[20150619]undo檔案損壞或者丟失的恢復2
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150619]undo檔案損壞或者丟失的恢復1
- [20150619]undo檔案損壞或者丟失的恢復3
- REDO檔案丟失或者損壞的恢復
- INDEX表空間檔案丟失或者損壞的恢復Index
- RMAN_部分資料檔案丟失或者損壞的恢復
- undo檔案丟失或損壞
- 資料檔案丟失損壞的恢復--
- Oracle不同檔案丟失/損壞的恢復方法Oracle
- rman 恢復---歸檔丟失and資料檔案損壞
- 磁碟損壞導致資料檔案丟失的恢復
- coreldraw檔案丟失(損壞)的恢復處理辦法
- UNDO 表空間檔案損壞的恢復
- Recovery from missing or corrupted datafile(多個資料檔案丟失或者損壞的恢復)
- 備份與恢復--資料檔案損壞或丟失
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- TEMP表空間的檔案丟失或損壞後的恢復
- RMAN_資料庫的絕大部分資料檔案丟失或者損壞的恢復資料庫
- 資料檔案損壞、丟失
- 2.7.10 恢復丟失或損壞的伺服器引數檔案(SPFILE)伺服器
- undo 檔案損壞
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- win10安裝檔案丟失或者損壞怎麼解決Win10
- 恢復丟失的控制檔案
- 損壞控制檔案的恢復方法
- undo表空間檔案丟失恢復（1）--有備份
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 備份＆恢復之五：歸檔模式下丟失或損壞一個資料檔案模式
- 資料檔案或者tablespace損壞基於rman恢復測試
- 【恢復】Redo日誌檔案丟失的恢復
- 控制檔案損壞，丟失其中一個
- 單個控制檔案損壞的恢復
- 資料檔案丟失的恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- Oracle Password檔案丟失的恢復Oracle
- 控制檔案丟失的RMAN恢復