[20150619]undo文件损坏或者丢失的恢复2.txt
--昨天别人问一些undo文件损坏或者丢失的恢复，如果不正常关机，undo文件丢失，恢复与正常关机存在不同。
--因为可能有事务在回滚段没有提交，这样启动时要进行instance恢复，在恢复时要读取回滚段，由于不存在导致一些错误，m
--必须修改一些参数略过实例恢复的回滚操作。
--通过例子来说明:
1.测试建立：
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> insert into t values (15,'eee');
1 row created.
--不提交。
SCOTT@test> @ &r/xid
X
------------------------------
10.21.11527
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
------ ------- ------ ------ ------ ------ ------ ------ --------- --------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
10 21 11527 2 5471 37 5281 ACTIVE 1 2 0A001500072D0000 000000007A702768 2015-06-19 09:00:42 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10$' XID 10 21 11527;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10$';
SCOTT@test> select us#,name from sys.undo$;
US# NAME
------------ --------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
21 _SYSSMU21$
22 _SYSSMU22$
23 _SYSSMU23$
24 _SYSSMU24$
25 _SYSSMU25$
26 _SYSSMU26$
27 _SYSSMU27$
28 _SYSSMU28$
29 _SYSSMU29$
30 _SYSSMU30$
31 _SYSSMU31$
32 _SYSSMU32$
33 _SYSSMU33$
34 _SYSSMU34$
35 _SYSSMU35$
36 _SYSSMU36$
37 _SYSSMU37$
38 _SYSSMU38$
39 _SYSSMU39$
40 _SYSSMU40$
41 _SYSSMU41$
42 _SYSSMU42$
43 _SYSSMU43$
44 _SYSSMU44$
45 _SYSSMU45$
46 _SYSSMU46$
47 _SYSSMU47$
48 _SYSSMU48$
49 _SYSSMU49$
50 rows selected.
SCOTT@test> show parameter undo
NAME TYPE VALUE
----------------- -------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@test> shutdown abort
ORACLE instance shut down.
2.做一个冷备份：
--步骤忽略
$ cd /mnt/ramdisk/test/
$ mv undotbs01.dbf undotbs01.dbf_org
3.开始测试：
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
MOUNTED
SYS@test> create pfile from spfile ;
File created.
--修改2行。
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
--再次启动:
SYS@test> startup pfile=//u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
SYS@test> alter database datafile 2 offline drop;
Database altered.
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
READ WRITE
--不过已经打开数据库。
--要建立新的undo表空间：
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/mnt/ramdisk/test/undotbs02.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
4.修改参数undo_tablespace=UNDOTBS2.
SYS@test> shutdown immediate ;
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
--主要由于有事务在回滚段表空间undotbs1，读取时错误。
--从alert*.log文件，可以确定这些undo segment 需要 needs recovery。
*** 2015-06-19 09:17:56.644
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
SYS@test> select SEGMENT_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME STATUS
-------------------- ----------------
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
_SYSSMU11$ OFFLINE
_SYSSMU12$ OFFLINE
_SYSSMU13$ OFFLINE
....
_SYSSMU59$ OFFLINE
60 rows selected.
SYS@test> alter system checkpoint;
System altered.
--没有办法只能abort关闭数据库。
SYS@test> shutdown abort
ORACLE instance shut down.
5.修改pfile文件：
*._corrupted_rollback_segments='_SYSSMU1$'
*._corrupted_rollback_segments='_SYSSMU2$'
*._corrupted_rollback_segments='_SYSSMU3$'
*._corrupted_rollback_segments='_SYSSMU4$'
*._corrupted_rollback_segments='_SYSSMU5$'
*._corrupted_rollback_segments='_SYSSMU6$'
*._corrupted_rollback_segments='_SYSSMU7$'
*._corrupted_rollback_segments='_SYSSMU8$'
*._corrupted_rollback_segments='_SYSSMU9$'
*._corrupted_rollback_segments='_SYSSMU10$'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
SYS@test> startup mount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test>
SYS@test> show parameter undo
NAME TYPE VALUE
----------------- -------- ---------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SYS@test> alter database open ;
Database altered.
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
READ WRITE
SYS@test> select * from scott.t where id=15;
ID NAME
------------ ----------------------------------------
15 eee
--看到了没有提交的数据这个是由于无法访问表空间untotbs1对应的回滚段，导致恢复仅仅前滚，没有做回滚操作(我的事务没有提交)，
--这种恢复会导致数据的"一致性"存在问题。
5.恢复收尾工作：
SYS@test> drop tablespace undotbs1;
Tablespace dropped.
SCOTT@test> insert into scott.t values (16,'fff');
1 row created.
SCOTT@test> commit ;
Commit complete.
