A-A+
浅析undo
该文档从insert，update，delete所产生的dump文件进行分析并做简要说明
31号会话插入一条记录（未提交）
sys@ora11gr2> select sid from v$mystat where rownum<2;
SID
----------
31
1 row selected.
sys@ora11gr2> select * from yallonking.redoundo;
ID NAME
---------- --------------------
1 redoundo1
2 redoundo2
2 rows selected.
sys@ora11gr2> insert into yallonking.redoundo values(3,'redoundo3');
1 row created.
sys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2;
ROWID ID NAME
------------------ ---------- --------------------
AAARgrAAFAAAAATAAA 1 redoundo1
AAARgrAAFAAAAATAAB 2 redoundo2
AAARgrAAFAAAAATAAC 3 redoundo3
3 rows selected.
32号会话删除一条记录（未提交）
sys@ora11gr2> select sid from v$mystat where rownum<2;
SID
----------
32
1 row selected.
sys@ora11gr2> delete from yallonking.redoundo where id=1;
1 row deleted.
sys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2;
ROWID ID NAME
------------------ ---------- --------------------
AAARgrAAFAAAAATAAB 2 redoundo2
1 row selected.
下边在新的会话中进行分析
sys@ora11gr2> select sid from v$mystat where rownum<2;
SID
----------
1
1 row selected.
查看活动事务信息
sys@ora11gr2> select ADDR,sid,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,a.STATUS,START_SCN
2 from v$transaction a,v$session b
3 where a.SES_ADDR=b.SADDR;
ADDR SID XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_SCN
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
0000000095A27420 31 7 9 801 3 800 184 1 ACTIVE 1330231 //插入操作开始的scn
0000000095A367A8 32 4 15 792 3 182 248 18 ACTIVE 1330299 //删除操作开始的scn
2 rows selected.
sys@ora11gr2> select * from V$ROLLNAME where USN in (7,4);
USN NAME
---------- ------------------------------
4 _SYSSMU4_3451911709$
7 _SYSSMU7_774603598$
2 rows selected.
对于插入操作
插入操作的事务信息如下：
sys@ora11gr2> alter system dump undo header '_SYSSMU7_774603598$'; System altered.
dump文件部分如下：
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x09 10 0x80 0x0321 0x0002 0x0000.00144c37 0x00c00320 0x0000.000.00000000 0x00000001 0x00000000 0
查看插入操作的undo信息
dump出对应的块信息部分如下：
******************************************************************************** UNDO BLK: xid: 0x0007.009.00000321 seq: 0xb8 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f78 *----------------------------- * Rec #0x1 slt: 0x09 objn: 71723(0x0001182b) objd: 71723 tblspc: 5(0x00000005) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c0031c.00b8.2c ctl max scn: 0x0000.0014413e prv tx scn: 0x0000.00144173 txn start scn: scn: 0x0000.00144bbe logon user: 0 prev brb: 12583711 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z KDO Op code: DRP row dependencies Disabled //说明回滚时进行的是delete操作 xtype: XA flags: 0x00000000 bdba: 0x01400013 hdba: 0x01400012 itli: 2 ispac: 0 maxfr: 4858 tabn: 0 slot: 2(0x2) End dump data blocks tsn: 2 file#: 3 minblk 800 maxblk 800
从插入操作的undo中可以找到回滚时需要删除的行的rowid信息，如下：
/////////////////////////////////////////////////////////////////
说明： rowid由data_object_id# rfile# block# row#组成 占用10个bytes的空间， 32bit的 data_object_id#,10 bit 的 rfile#,22bit 的 block#,16 bit 的 row#. 1.objn: 71723(0x0001182b) //data_object_id 2.bdba: 0x01400013 //relative_fno 和 block_number 3.slot: 2(0x2) //row_number
/////////////////////////////////////////////////////////////////
sys@ora11gr2> select dbms_utility.data_block_address_file(to_number('01400013','xxxxxxxx')) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('01400013','XXXXXXXX'))
----------------------------------------------------------------------
5
1 row selected.
sys@ora11gr2> select dbms_utility.data_block_address_block(to_number('01400013','xxxxxxxx')) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('01400013','XXXXXXXX'))
-----------------------------------------------------------------------
19
1 row selected.
sys@ora11gr2> select dbms_rowid.rowid_create(1,71723,5,19,2) from dual;
DBMS_ROWID.ROWID_C
------------------
AAARgrAAFAAAAATAAC
1 row selected.
在第一个插入但未提交的会话中查看：
sys@ora11gr2> select rowid, 2 dbms_rowid.rowid_object(rowid) dobj, 3 dbms_rowid.rowid_relative_fno(rowid) fno, 4 dbms_rowid.rowid_block_number(rowid) block_number, 5 dbms_rowid.rowid_row_number(rowid) row_number, 6 t.* from yallonking.redoundo t; ROWID DOBJ FNO BLOCK_NUMBER ROW_NUMBER ID NAME ------------------ ---------- ---------- ------------ ---------- ---------- -------------------- AAARgrAAFAAAAATAAA 71723 5 19 0 1 redoundo1 AAARgrAAFAAAAATAAB 71723 5 19 1 2 redoundo2_p2 AAARgrAAFAAAAATAAC 71723 5 19 2 3 redoundo3 3 rows selected.
对于删除操作
删除操作的事务信息如下：
sys@ora11gr2> alter system dump undo header '_SYSSMU4_3451911709$'; System altered.
dump文件部分如下：
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x0f 10 0x80 0x0318 0x0000 0x0000.00144c7b 0x00c000b6 0x0000.000.00000000 0x00000001 0x00000000 0
查看删除操作的undo信息
dump出对应的块信息部分如下：
*-----------------------------
* Rec #0x12 slt: 0x0f objn: 71723(0x0001182b) objd: 71723 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c000b6.00f8.0f ctl max scn: 0x0000.0014413f prv tx scn: 0x0000.001441b9
txn start scn: scn: 0x0000.00144c72 logon user: 0
prev brb: 12583093 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0005.004.00000326 uba: 0x00c00173.00ef.2a
flg: C--- lkc: 0 scn: 0x0000.00144c37
KDO Op code: IRP row dependencies Disabled //说明回滚时进行的是insert操作
xtype: XA flags: 0x00000000 bdba: 0x01400013 hdba: 0x01400012
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 16
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02 //删除前的值1
col 1: [ 9] 72 65 64 6f 75 6e 64 6f 31 //删除前的值redoundo1
End dump data blocks tsn: 2 file#: 3 minblk 182 maxblk 182
查看具体值
sys@ora11gr2> select dump(1,16) from dual;
DUMP(1,16)
-----------------
Typ=2 Len=2: c1,2
sys@ora11gr2> @cast_to_varchar2 7265646f756e646f31
UTL_RAW.CAST_TO_VARCHAR2('7265646F756E646F31')
----------------------------------------------------------------------------------------------------
redoundo1
下边单独考虑update的操作
27号会话修改2次（未提交）并记录每次的savepoint
sys@ora11gr2> select sid from v$mystat where rownum<2;
SID
----------
27
1 row selected.
sys@ora11gr2> select t.rowid,t.* from yallonking.redoundo t order by 2;
ROWID ID NAME
------------------ ---------- --------------------
AAARgrAAFAAAAATAAB 2 redoundo2_p2
AAARgrAAFAAAAATAAC 3 redoundo3
2 rows selected.
sys@ora11gr2> savepoint oraking;
Savepoint created.
sys@ora11gr2> update yallonking.redoundo set NAME='oraking' where id=2;
1 row updated.
sys@ora11gr2> savepoint yallonking;
Savepoint created.
sys@ora11gr2> update yallonking.redoundo set NAME='yallonking' where id=2;
1 row updated.
在新的会话中查看当前进行修改的活动事务信息
sys@ora11gr2> select sid from v$mystat where rownum<2;
SID
----------
32
1 row selected.
sys@ora11gr2> set line 400
sys@ora11gr2> select ADDR,sid,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,a.STATUS,START_SCN
2 from v$transaction a,v$session b
3 where a.SES_ADDR=b.SADDR;
ADDR SID XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_SCN
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
0000000095A28840 27 9 1 814 3 2235 335 23 ACTIVE 1336130
1 row selected.
查看修改操作的undo信息
dump出对应的块信息部分如下：
*-----------------------------
* Rec #0x16 slt: 0x01 objn: 71723(0x0001182b) objd: 71723 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 rci 0x00 //终止于此
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c008bb.014f.15 ctl max scn: 0x0000.00145a16 prv tx scn: 0x0000.00145a2a
txn start scn: scn: 0x0000.00146342 logon user: 0
prev brb: 12585143 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0009.010.0000032b uba: 0x00c00888.014f.1a
flg: C--- lkc: 0 scn: 0x0000.001452e9
KDO Op code: URP row dependencies Disabled //update操作
xtype: XA flags: 0x00000000 bdba: 0x01400013 hdba: 0x01400012
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 9
ncol: 2 nnew: 1 size: 5
col 1: [12] 72 65 64 6f 75 6e 64 6f 32 5f 70 32 //redoundo2_p2
*-----------------------------
* Rec #0x17 slt: 0x01 objn: 71723(0x0001182b) objd: 71723 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 rci 0x16 //继续进行undo
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c008bb.014f.16
KDO Op code: URP row dependencies Disabled //update操作
xtype: XA flags: 0x00000000 bdba: 0x01400013 hdba: 0x01400012
itli: 3 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 3 ckix: 9
ncol: 2 nnew: 1 size: -3
col 1: [ 7] 6f 72 61 6b 69 6e 67 //oraking
End dump data blocks tsn: 2 file#: 3 minblk 2235 maxblk 2235
sys@ora11gr2> @cast_to_varchar2 7265646f756e646f325f7032
UTL_RAW.CAST_TO_VARCHAR2('7265646F756E646F325F7032')
----------------------------------------------------------------------------------------------------
redoundo2_p2
sys@ora11gr2> @cast_to_varchar2 6f72616b696e67
UTL_RAW.CAST_TO_VARCHAR2('6F72616B696E67')
----------------------------------------------------------------------------------------------------
oraking
