备份与恢复（之undo恢复篇）
一、shutdown immediate关闭数据库，有事务的undo恢复!
|
解决思路 1、 2、 3、 4、 Shutdown immediate,startup看看数据库 |
二、shutdown abort
关闭数据库，有事务的undo恢复!
|
1、 努力尝试开启数据库 2、 丢失文件离线，尝试开启数据库 3、 使用隐含参数_allow_resetlogs_corruption=true与（_corrupted_rollback_segments或_offline_rollback_segments）开启数据库，然后建立新的undo表空间，使用新的删除老的 4、 |
三、总结：
|
1、 2、 3、 |
四、实验如下：
实验（一）
1、Doudou用户下的test表
SQL> select * from test;
I NAME
---------- ----------------------------------------
1 兜兜
delete删除没有commit；所以此事务记录到了undo表空间里。
SQL> delete test;
1 row deleted.
2、Sys查询，并确定undo表空间内的事务
SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
5 6 251
XIDUSN:就是事务的回滚段号
XIDSLOT:就是ITL(interesting
transaction list)列表中,slot的号
XIDSQN:就是表示这个slot被重复使用的次数
SQL> select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=5;
SEGMENT_NAME TABLESPACE_NAME FILE_ID
-------------------- -------------------- ----------
_SYSSMU5$ UNDOTBS1 2
3、rm undo文件，shutdow immediate
关闭数据库，开启数据库报错！
[ora@dg-pp test]$ rm -rf undotbs01.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 457179136 bytes
Fixed Size 1219976 bytes
Variable Size 142606968 bytes
Database Buffers 310378496 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/opt/oracle/oradata/test/undotbs01.dbf'
Alert日志：
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_2287.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/opt/oracle/oradata/test/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
显然，oracle发现了我们的undo文件不存在，下面进行恢复操作。启库为先，因为我们开启之后就可以再OPEN状态下，才重新建立新的undo表空间并使用、删除。
4、先mount状态下，对undo文件进行离线，启库
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
--------------
/opt/oracle/oradata/test/system01.dbf
SYSTEM
/opt/oracle/oradata/test/undotbs01.dbf
ONLINE
/opt/oracle/oradata/test/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
--------------
/opt/oracle/oradata/test/users01.dbf
ONLINE
SQL> alter database datafile '/opt/oracle/oradata/test/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
1、 开启库了，然后建立新的undo表空间，使用新的并删除老的。
SQL> create undo tablespace undo02 datafile '/opt/oracle/oradata/test/doudou.dbf' size 100m;
Tablespace created.
SQL> alter system set undo_tablespace=undo02 scope=both;
System altered.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
为什么我们的undotbs1中的事务，没有被记录呢，原因是shutdown immediate自动回滚事务。所以undo中没有记录。所以个人认为shutdown
abort下的undo更有挑战性一些。
实验（二）
1、 实验环境，直接就是有事务的undo，被删除。然后恢复。
Doudou用户
SQL> select * from test;
I NAME
---------- ----------------------------------------
1 兜兜
SQL> delete test;
1 row deleted.
SQL> select * from test;
no rows selected
sys用户，查看当前事务是哪个用户的事务
SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
18 14 7
SQL> select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=18;
SEGMENT_NAME TABLESPACE_NAME FILE_ID
-------------------- -------------------- ----------
_SYSSMU18$ UNDO02 5
SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
2 FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
3 WHERE R.NAME ='_SYSSMU18$'
4 AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN
5 ;
SID SERIAL# USERNAME ROLLBACK
------ ------- --------------- ---------------
151 1368 DOUDOU _SYSSMU18$
Shutdown abort关闭，rm undo
文件，开库报错
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 457179136 bytes
Fixed Size 1219976 bytes
Variable Size 150995576 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/test/doudou.dbf'
Alert日志：
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_7064.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/test/doudou.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
2、 执行恢复
离线被删除文件，尝试开启数据库
SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database open;
Database altered.
建立新的undo并使用，尝试删除老的
SQL> create undo tablespace undotbs1 datafile '/opt/oracle/oradata/test/undotbs1.dbf' size 100m;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.
很好，问题来了，有事务没有被回退，使用隐含参数回退事务
SQL> drop tablespace undo02 including contents and datafiles;
drop tablespace undo02 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11$' found, terminate dropping
Tablespace
SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU11$'
scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 457179136 bytes
Fixed Size 1219976 bytes
Variable Size 155189880 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> drop tablespace undo02 including contents and datafiles;
drop tablespace undo02 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU12$' found, terminate dropping
Tablespace
这段为错误操作，忘大家不要记录此段，给自己当个警界就好
**********************************************************************
写了一个SQL，把所有的执行好
SQL> select 'alter system set "_corrupted_rollback_segments"='''||segment_name||''' scope=spfile;' from dba_rollback_segs where tablespace_name='UNDO02';
'ALTERSYSTEMSET"_CORRUPTED_ROLLBACK_SEGMENTS"='''||SEGMENT_NAME||'''SCOPE=SPFILE
--------------------------------------------------------------------------------
alter system set "_corrupted_rollback_segments"='_SYSSMU13$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU14$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU15$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU16$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU17$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU18$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU19$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU20$' scope=spfile;
重新后，删除老的undo文件。我一次性把上面的都执行了，每次都告诉我能删除，看样这个参数每次只能识别一条命令
SQL> drop tablespace undo02 including contents and datafiles;
drop tablespace undo02 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU13$' found, terminate dropping
Tablespace
***************************************************************************
使用inittest.ora
文本编辑修改，然后重启数据库，删除成功！
_corrupted_rollback_segments=('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')
SQL> drop tablespace undo02 including contents and datafiles;
Tablespace dropped.
恢复成功！
但是我们使用的参数为_corrupted_rollback_segments，oracle假设这个事务已经回滚，而不会进行其他的检查！所以doudou用户执行的delete，会被认为已经成功！
SQL> conn doudou/doudou
Connected.
SQL> select * from tab;
TNAME TABTYPE
------------------------------------------------------------ --------------
CLUSTERID
----------
TEST TABLE
SQL> select * from test;
no rows selected
3、使用隐含参数后，有着不可预测的问题，所有我们还是把隐含参数去掉，然后shutdown immediate
，然后exp ，建新库，最后imp。备份重于一切！还有保护现场也很重要。
总结：
1、
恢复undo看似简单，但是盖老师说的太对，你要了解这个drop undo的风险，相对应金融行业真的是灾难！希望以后还可以研究出来更好的恢复方法。
2、使用_corrupted_rollback_segments，会被oracle认为已经回滚了事务，所以相当于应用上已经commit;
，使用的时候一定要注意。
3、使用_offline_rollback_segments，会延迟清除事务，所以想删除undo表空间必须执行drop
回滚段。
感谢
http://space.itpub.net/4227/viewspace-198238杨老师
http://www.eygle.com/archives/2011/06/recovery_4193_4194_2662.html盖老师
杨老师总结：
尝试使用_corrupted_rollback_segments打开数据库，使用_offline_rollback_segments参数时，Oracle还会尝试进行数据块的延迟清除，而对于_corrupted_rollback_segments来说，Oracle假设事务已经回滚，而不会进行其他的检查。一般UNDO表空间丢失或损害的时候可以尝试使用这个隐含参数
为了避免随后出现不可预测的问题，在重起数据库去掉隐藏参数后，应该导出数据，并建立新库，将源数据导入。
盖老师总结：
ORA-600的 4193
和 4194
错误，可以通过重建回滚表空间解决。