[20150619]undo檔案損壞或者丟失的恢復2

lfree發表於2015-06-19

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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1704902/,如需轉載,請註明出處,否則將追究法律責任。

相關文章