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