[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是正常的,取消参数后,恢复正常.
