[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.