[0126]理解_corrupted_rollback_segments
[20150126]理解_corrupted_rollback_segments.txt
--前幾天遇到一個恢復問題，異常掉電導致讀取redo檔案錯誤，我還第一次解決這種問題，加入引數後
--_allow_resetlogs_corruption=true後，報undo讀取有問題，按照網上的介紹，使用_corrupted_rollback_segments引數解決，
--最後open resetlogs開啟。
--今天有空，研究以及做一些模擬測試(注意僅僅用來測試,不要在生產系統使用)：
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
SYS@test> @ &r/hide _corrupted_rollback_segments
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------------- ---------------------------- ---------------------- ---------------------- ----------------------
_corrupted_rollback_segments corrupted undo segment list TRUE
--以scott使用者登陸:
create table t1 (id number,name varchar2(20));
insert into t1 values (1,'aaaa');
insert into t1 values (2,'bbbb');
commit ;
SCOTT@test> select rowid,t1.* from t1;
ROWID ID NAME
------------------ ------------ --------------------
AAAN4jAAEAAAAGfAAA 1 aaaa
AAAN4jAAEAAAAGfAAB 2 bbbb
SCOTT@test> @ &r/lookup_rowid AAAN4jAAEAAAAGfAAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
56867 4 415 0 4,415 alter system dump datafile 4 block 415 ;
$ cat xid.sql
select dbms_transaction.local_transaction_id() x from dual ;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction;
2.修改一條記錄不提交：
SCOTT@test> alter system archive log current ;
System altered.
SCOTT@test> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------ ------------ ------------ ------------ ------------ --- ---------------- ------------- -------------------
1 1 25 52428800 1 NO CURRENT 11995874197 2015-01-26 10:43:30
2 1 23 52428800 1 YES ACTIVE 11995865528 2015-01-26 07:01:36
3 1 24 52428800 1 YES ACTIVE 11995874194 2015-01-26 10:43:26
SCOTT@test> select * from v$logfile ;
GROUP# STATUS TYPE MEMBER IS_
------- ------- -------- ----------------------------- ---
3 ONLINE /mnt/ramdisk/test/redo03.log NO
2 ONLINE /mnt/ramdisk/test/redo02.log NO
1 ONLINE /mnt/ramdisk/test/redo01.log NO
--當前的redo group#=1,對應檔案是/mnt/ramdisk/test/redo01.log。
SCOTT@test> update t1 set name='AAAA' where id=1;
1 row updated.
--注意不提交。
SYS@test> @ &r/xid
X
------------------------------
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR
------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ----------------
10 34 4875 2 1605 28 2402 ACTIVE 1 1 0A0022000B130000 000000007A6FD698
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$
...
49 _SYSSMU49$
50 rows selected.
3.選擇異常關機。
--在abort前寫一次盤。
SYS@test> alter system checkpoint ;
System altered.
SYS@test> shutdown abort ;
ORACLE instance shut down.
$ dd if=/mnt/ramdisk/test/users01.dbf skip=415 bs=8192 count=1 | xxd -c 16
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 2.8764e-05 s, 285 MB/s
0000000: 06a2 0000 9f01 0001 b383 02cb 0200 0104 ...........?...
0000010: 0f39 0000 0100 0000 23de 0000 0035 ffca .9......#?..5??
0000020: 0200 0000 0200 3200 9901 0001 0a00 2500 ......2.......%.
...
0001fc0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001fd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001fe0: 0000 0000 0000 2c00 0202 c103 0462 6262 ......,...?.bbb
0001ff0: 622c 0102 02c1 0204 4141 4141 0106 b383 b,...?.AAAA....
--可以發現對應的資料塊已經修改,但是沒有提交.正常如果啟動資訊應該會返回'aaaa'.
4.使用_corrupted_rollback_segments引數,看看會出現什麼情況:
SYS@test> create pfile='/tmp/test0126.ora' from spfile ;
File created.
--在檔案/tmp/test0126.ora加入*._corrupted_rollback_segments='_SYSSMU10$'.使用這個引數啟動.
SYS@test> startup pfile=/tmp/test0126.ora mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> show parameter corrupt
NAME TYPE VALUE
----------------------------- ------- ----------
_corrupted_rollback_segments string _SYSSMU10$
SYS@test> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery
--不正常關閉,不能open read only開啟.
SYS@test> alter database open ;
Database altered.
SYS@test> select rowid,t1.* from scott.t1;
ROWID ID NAME
------------------ ------------ --------------------
AAAN4jAAEAAAAGfAAA 1 AAAA
AAAN4jAAEAAAAGfAAB 2 bbbb
--讀到了沒有提交前的資訊.'AAAA'.所以講這種修改會導致資料的一致性存在破壞.基本上像上面的修復,要執行一些匯出重建的工作.這
--樣比較穩妥一些.
--實際上設定這個引數就是跳過相應的undo段進行恢復工作.
5.繼續測試:
--重新啟動,使用原來的spfile檔案看看.以只讀開啟先:
SYS@test> alter database open read only ;
Database altered.
SYS@test> select rowid,t1.* from scott.t1;
ROWID ID NAME
------------------ ------------ --------------------
AAAN4jAAEAAAAGfAAA 1 AAAA
AAAN4jAAEAAAAGfAAB 2 bbbb
--保持提交前資訊.
SYS@test> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10$' XID 10 34 4875 ;
System altered.
********************************************************************************
Undo Segment: _SYSSMU10$ (10)
xid: 0x000a.022.0000130b
Low Blk : (0, 0)
High Blk : (4, 127)
Object Id : ALL
Layer : ALL
Opcode : ALL
Level : 2
********************************************************************************
UNDO BLK: Extent: 2 Block: 60 dba (file#, block#): 2,0x00000645
xid: 0x000a.022.0000130b seq: 0x962 cnt: 0x1c irb: 0x1c icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c 0x02 0x1edc 0x03 0x1e7c 0x04 0x1e30 0x05 0x1d38
0x06 0x1c48 0x07 0x1bac 0x08 0x1b40 0x09 0x1af4 0x0a 0x19d8
0x0b 0x193c 0x0c 0x18e0 0x0d 0x188c 0x0e 0x1820 0x0f 0x1750
0x10 0x16b4 0x11 0x1644 0x12 0x15f0 0x13 0x1584 0x14 0x13d4
0x15 0x136c 0x16 0x1300 0x17 0x12a4 0x18 0x1250 0x19 0x11e4
0x1a 0x1120 0x1b 0x1010 0x1c 0x0f5c
*-----------------------------
* Rec #0x1c slt: 0x22 objn: 56867(0x0000de23) objd: 56867 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800645.0962.1b ctl max scn: 0x0002.cb0280ee prv tx scn: 0x0002.cb0280f0
txn start scn: scn: 0x0002.cb02d4bd logon user: 57
prev brb: 8390210 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x000a.01b.00000ebd uba: 0x0080048b.073f.0b
flg: C--- lkc: 0 scn: 0x0000.002acb06
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100019f hdba: 0x0100019b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
col 1: [ 4] 61 61 61 61
+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++
*************************************
Total undo blocks scanned = 1
Total undo records scanned = 1
Total undo blocks dumped = 1
Total undo records dumped = 1
##Total warnings issued = 1
*************************************
SYS@test> alter system dump undo header "_SYSSMU10$";
System altered.
********************************************************************************
Undo Segment: _SYSSMU10$ (10)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 399
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800645 ext#: 2 blk#: 60 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080009a length: 7
0x00800579 length: 8
0x00800609 length: 128
0x00801609 length: 128
0x00800489 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1422230438
Extent Number:1 Commit Time: 1422230438
Extent Number:2 Commit Time: 1422230438
Extent Number:3 Commit Time: 1422230438
Extent Number:4 Commit Time: 1422230438
TRN CTL:: seq: 0x0962 chd: 0x0026 ctl: 0x0013 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
TRN CTL:: seq: 0x0962 chd: 0x0026 ctl: 0x0013 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800645.0962.1c scn: 0x0002.cb0280f0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0962.1b ext: 0x2 spc: 0xfc8
uba: 0x00000000.0962.02 ext: 0x2 spc: 0x1f06
uba: 0x00000000.0962.28 ext: 0x2 spc: 0xe9a
uba: 0x00000000.085c.1f ext: 0x8 spc: 0x1060
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x12f8 0x0019 0x0002.cb028108 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x01 9 0x00 0x130a 0x002e 0x0002.cb02d46c 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x02 9 0x00 0x130c 0x0025 0x0002.cb028357 0x00800644 0x0000.000.00000000 0x00000002 0x00000000 1422240037
0x03 9 0x00 0x1309 0x0023 0x0002.cb02811e 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x04 9 0x00 0x130f 0x0020 0x0002.cb0280fa 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x05 9 0x00 0x130a 0x0006 0x0002.cb028100 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x06 9 0x00 0x130e 0x000e 0x0002.cb028102 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x07 9 0x00 0x1308 0x000c 0x0002.cb0282de 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x08 9 0x00 0x1306 0x000d 0x0002.cb028118 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x09 9 0x00 0x1308 0x002a 0x0002.cb02d480 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x0a 9 0x00 0x130a 0x000b 0x0002.cb028126 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x0b 9 0x00 0x130b 0x001a 0x0002.cb028128 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x0c 9 0x00 0x1309 0x0024 0x0002.cb0282e0 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x0d 9 0x00 0x130e 0x0003 0x0002.cb02811a 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x0e 9 0x00 0x130a 0x0012 0x0002.cb028104 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x0f 9 0x00 0x1310 0x001b 0x0002.cb02d461 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1422241602
0x10 9 0x00 0x130b 0x0021 0x0002.cb028264 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422239436
0x11 9 0x00 0x130a 0x002c 0x0002.cb0282e6 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x12 9 0x00 0x130b 0x0000 0x0002.cb028106 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x13 9 0x00 0x130a 0xffff 0x0002.cb02d4bc 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241759
0x14 9 0x00 0x130c 0x000f 0x0002.cb02d44a 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x15 9 0x00 0x130b 0x0009 0x0002.cb02d47a 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x16 9 0x00 0x130c 0x001c 0x0002.cb0282d4 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x17 9 0x00 0x130a 0x0015 0x0002.cb02d477 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x18 9 0x00 0x1308 0x001d 0x0002.cb02810e 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x19 9 0x00 0x130b 0x0018 0x0002.cb02810c 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x1a 9 0x00 0x130c 0x002f 0x0002.cb02812c 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x1b 9 0x00 0x1309 0x001e 0x0002.cb02d464 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x1c 9 0x00 0x1309 0x0027 0x0002.cb0282d6 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x1d 9 0x00 0x12ff 0x0029 0x0002.cb028110 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x1e 9 0x00 0x130b 0x0001 0x0002.cb02d467 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x1f 9 0x00 0x1308 0x002b 0x0002.cb0280f4 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x20 9 0x00 0x1309 0x0005 0x0002.cb0280fc 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x21 9 0x00 0x1307 0x0016 0x0002.cb028267 0x00800643 0x0000.000.00000000 0x00000002 0x00000000 1422239439
0x22 10 0x80 0x130b 0x0002 0x0002.cb02d4bd 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 0
0x23 9 0x00 0x1309 0x002d 0x0002.cb028120 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x24 9 0x00 0x130a 0x0011 0x0002.cb0282e2 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x25 9 0x10 0x130a 0x0014 0x0002.cb02d448 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1422241602
0x26 9 0x00 0x1307 0x001f 0x0002.cb0280f2 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x27 9 0x00 0x130d 0x0007 0x0002.cb0282da 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x28 9 0x00 0x130c 0x000a 0x0002.cb028124 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x29 9 0x00 0x12f1 0x0008 0x0002.cb028112 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x2a 9 0x00 0x130d 0x0013 0x0002.cb02d486 0x00800645 0x0000.000.00000000 0x00000001 0x00000000 1422241602
0x2b 9 0x00 0x130a 0x0004 0x0002.cb0280f6 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x2c 9 0x00 0x1306 0x0002 0x0002.cb0282e8 0x00800643 0x0000.000.00000000 0x00000001 0x00000000 1422239739
0x2d 9 0x00 0x130b 0x0028 0x0002.cb028122 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
0x2e 9 0x00 0x130d 0x0017 0x0002.cb02d474 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1422241602
0x2f 9 0x00 0x130c 0x0010 0x0002.cb028132 0x00800642 0x0000.000.00000000 0x00000001 0x00000000 1422238539
--0x22 =34 的狀態是state=10,還是啟用狀態.
=====
6.繼續測試:
--重新啟動,使用原來的spfile檔案看看.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 230686808 bytes
Database Buffers 230686720 bytes
Redo Buffers 10498048 bytes
Database mounted.
Database opened.
$ dd if=/mnt/ramdisk/test/users01.dbf skip=415 bs=8192 count=1 | xxd -c 16
...
0001fd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001fe0: 0000 0000 0000 2c00 0202 c103 0462 6262 ......,...?.bbb
0001ff0: 622c 0002 02c1 0204 4141 4141 0106 6623 b,...?.AAAA..f#
--沒有變.
SYS@test> alter system checkpoint ;
System altered.
$ dd if=/mnt/ramdisk/test/users01.dbf skip=415 bs=8192 count=1 | xxd -c 16
...
0001fd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001fe0: 0000 0000 0000 2c00 0202 c103 0462 6262 ......,...?.bbb
0001ff0: 622c 0002 02c1 0204 6161 6161 0106 5325 b,...?.aaaa..S%
--修改了資訊.
SYS@test> select rowid,t1.* from scott.t1;
ROWID ID NAME
------------------ ------------ --------------------
AAAN4jAAEAAAAGfAAA 1 aaaa
AAAN4jAAEAAAAGfAAB 2 bbbb
--因為我的undo是正常的,取消引數後,恢復正常.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1415396/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 0126
- 理解 this
- 理解sizeof
- ThreadLocal理解thread
- YYCache理解
- 理解ContextContext
- 理解MeasureSpec
- 理解TypeScriptTypeScript
- BFC理解
- 理解BFC
- pm 理解
- LSTM理解
- Socket理解
- Socket 理解
- zookeeper理解
- Runtime理解
- 理解haslayout
- 理解 HTTPHTTP
- 理解CSSCSS
- 理解CAS
- MapReduce理解
- 理解SpingAOP
- Android Https 理解AndroidHTTP
- 如何理解Axis？
- 全面理解GitGit
- 理解 HandlerThread 原理thread
- React Diff理解React
- 理解 React HooksReactHook
- 理解Android BitmapAndroid
- 理解原型鏈原型
- 理解 AsyncTask 原理
- 理解 IntentService 原理Intent
- 如何理解nexus
- RAC的理解
- 理解RESTful概念REST
- Reactor模式理解React模式
- 理解索引（上）索引
- isNaN的理解NaN
- ssm框架理解SSM框架