undo事务具体是如何回滚,这里提供了大概的异常undo事务回滚的一个过程(更加准确的说，这个过程是在以下几种情况中发生的过程：1.数据库非正常关闭后启动,2.事务未提交会话终止)，数据库先扫描所有回滚段，然后发现有事务未提交回滚段,然后根据这个回滚段定位到undo block,然后定位到data block，当一个undo block回滚完成之后，利用undo的链表规则完成下一个undo block的回滚操作，依次类此，从而实现数据库的回滚操作；回滚的过程是先回滚后操作的块(先进后出原则)
创建测试表
SQL> create table chf.t_xifenfei(a varchar2(4000));
Table created.
SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'a'));
1 row created.
SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
SQL> update chf.t_xifenfei set a=lpad('www.xifenfei.com',4000,'F');
2 rows updated.
SQL> select
2 dbms_rowid.rowid_relative_fno(rowid) rel_fno,
3 dbms_rowid.rowid_block_number(rowid) block_no
4 from chf.t_xifenfei;
REL_FNO BLOCK_NO
---------- ----------
9 421
9 422
确保表中有两条记录,存储在两个block中
查询测试表相关信息
SQL> select object_id,data_object_id from dba_objects where owner='CHF' AND OBJECT_NAME='T_XIFENFEI';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
53917 53917
SQL> SELECT TO_CHAR(53917,'XXXXX') FROM DUAL;
TO_CHA
------
D29D
SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$ transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 28 513 2 3627 253 1
SQL> select to_char(28,'xxx') from dual;
TO_C
----
1c
SQL> select to_char(513,'xxx') from dual;
TO_C
----
201
------------------------
--xid=0002.01c.00000201
------------------------
SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(2,3627);
PL/SQL procedure successfully completed.
SQL> print dba
DBA
--------------------------------
8392235
SQL> select to_char(253,'xxx') from dual;
TO_C
----
fd
---------------------
--uda=800e2a.00fd.01
---------------------
通过这些查询可以得知:
1)chf.t_xifenfei的object_id/data_object_id的值为53917/D29D
2)xid=0002.01c.00000201(Xid=usn.slot.wrap)
3)uda=800e2a.00fd.01(Uba=undo block dba.undo record number.undo block sequence number)
dump rollback header
SQL> select * from v$ rollname where usn=2;
USN NAME
---------- ------------------------------
2 _SYSSMU2$
SQL> alter system dump undo header "_SYSSMU2$ ";
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0201 0x0012 0x0b2c.c02c9b85 0x00800e27 0x0000.000.00000000 0x00000001 0x00000000 1358780575
………………
0x1b 9 0x00 0x0201 0x0028 0x0b2c.c02c9bf4 0x00800e27 0x0000.000.00000000 0x00000001 0x00000000 1358780575
0x1c 10 0x80 0x0201 0x0002 0x0b2c.c02ca2a8 0x00800e2b 0x0000.000.00000000 0x00000002 0x00000000 0
0x1d 9 0x00 0x0200 0x0004 0x0b2c.c02c9a8b 0x00800e26 0x0000.000.00000000 0x00000001 0x00000000 1358780444
………………
0x2f 9 0x00 0x0201 0x001a 0x0b2c.c02ca1da 0x00800e29 0x0000.000.00000000 0x00000001 0x00000000 1358784176
------------------------------------------------------------------------------
==> Transaction Table
==> state 0 = IDLE
1 = Collecting 2 = Prepared 3 = Committed
4 = Forced Abort 5 = Forced Commit 6 = Forced Mixed
7 = try again later
9 = No TX (Committed) 10= 'a' = Active local TX
==> cflags 1 = TX has started storing collecting information
2 = TX has forced the collecting information
4 = Prepared TX needs distributed recovery
10= Rollback failed on this TX - mark SMON for recover
20= TX has rolled back its updates
==> wrap# is incremented TX slot reuse.
==> uel ?
==> scn SCN for the TX prepare / commit
==> dba is DBA of HEAD of the REDO - Ie: The MOST RECENT CHANG
------------------------------------------------------------------------------
--通过上面的提示可以知道index为0x1c为未提交事务
SQL> select to_number('800e2b','xxxxxxx') from dual;
TO_NUMBER('800E2B','XXXXXXX')
-----------------------------
8392235
SQL> select dbms_utility.data_block_address_block(8392235) "block",
2 dbms_utility.data_block_address_file(8392235) "file" from dual;
block file
---------- ----------
3627 2
--v$ transaction表中查询出来的UBAFIL与UBABLK一致
dump undo block
SQL> alter system dump datafile 2 block 3627;
System altered.
UNDO BLK:
xid: 0x0002.01c.00000201 seq: 0xfd cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x0fdc
*-----------------------------
* Rec #0x1 slt: 0x1c objn: 53917(0x0000d29d) objd: 53917 tblspc: 9(0x00000009)
* Layer: 11 (Row) opc: 1 rci 0x00 <--- 表明指向下一个block
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00800e2a <--- 为下一个undo block rdba
*-----------------------------
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x024001a6 hdba: 0x024001a3
itli: 2 ispac: 0 maxfr: 4858
--bdba表示undo对应的data block,这里对应的是datafile 9 block 422
--hdba表示chf.t_xifenfei的extent 0的 first block(first mapbit)
SQL> select to_number('800e2a','xxxxxxx') from dual;
TO_NUMBER('800E2A','XXXXXXX')
-----------------------------
8392234
SQL> select dbms_utility.data_block_address_block(8392234) "block",
2 dbms_utility.data_block_address_file(8392234) "file" from dual;
block file
---------- ----------
3626 2
SQL> alter system dump datafile 2 block 3626;
System altered.
UNDO BLK:
xid: 0x0002.01c.00000201 seq: 0xfd cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x0fac
*-----------------------------
* Rec #0x1 slt: 0x1c objn: 53917(0x0000d29d) objd: 53917 tblspc: 9(0x00000009)
* Layer: 11 (Row) opc: 1 rci 0x00 <--- 表明指向下一个block
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000 <--- 下一个block为 0x00000000,表示事务到此为止
*-----------------------------
uba: 0x00800e29.00fd.24 ctl max scn: 0x0b2c.c02c99cc prv tx scn: 0x0b2c.c02c9a27
txn start scn: scn: 0x0b2c.c02ca0b3 logon user: 0
prev brb: 8392230 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x024001a5 hdba: 0x024001a3
itli: 2 ispac: 0 maxfr: 4858
--bdba表示undo对应的data block,这里对应的是datafile 9 block 421
XID: this is the transaction ID, which matches the value made up previously from the query on V$ TRANSACTION.
CNT: this is the number of undo records in this block.
IRB: this is the index if the first record to be considered in case of a rollback.
OBJN: this is the object number being altered by the undo record. This matches the value previously queried from DBA_OBJECTS.
RCI: this is the next undo record in the chain to be examined. When rolling back transactions, these undo records are applied one by one on the data blocks to undo the changes.
1. undo链表的意思为:从IRB表示undo 回滚的开始，RCI表示未IRB对应的下一条记录，依次类推。当RCI为0,而且rdba不为0的时候，表示undo 记录关联到下一个block；当RCI为0，而且rdba也为0表示undo结束
2. undo事务回滚是倒序的,这里可以看到事务先读取block 422，再读取block 421
dump data block
SQL> alter system dump datafile 9 block 421;
System altered.
Block header dump: 0x024001a5
Object id on Block? Y
seg/obj: 0xd29d csc: 0xb2c.c02ca2bc itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24001a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000232 0x0083dbe0.02be.22 C--- 0 scn 0x0b2c.c02ca2a8
0x02 0x0002.01c.00000201 0x00800e2a.00fd.01 ---- 1 fsc 0x0000.00000000
SQL> alter system dump datafile 9 block 422;
System altered.
Block header dump: 0x024001a6
Object id on Block? Y
seg/obj: 0xd29d csc: 0xb2c.c02ca2bd itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24001a1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01d.00000232 0x0083dbe0.02be.23 C--- 0 scn 0x0b2c.c02ca2a8
0x02 0x0002.01c.00000201 0x00800e2b.00fd.01 ---- 1 fsc 0x0000.00000000
这里可以看到Xid,Uba和上面计算出来的一致，看出来事务未提交标记和上述一致.
转载请注明：爱开源 » undo异常事务回滚规则分析