技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置：首页 --> Oracle --> undo异常事务回滚规则分析

undo异常事务回滚规则分析

浏览:3541次  出处信息

   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和上面计算出来的一致，看出来事务未提交标记和上述一致.

建议继续学习：

  1. undo异常总结和恢复思路    (阅读：3496)
  2. Oracle In-memory Undo运作原理    (阅读：2578)
  3. 大事务回滚导致系统故障案例一则    (阅读：1816)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：关于blockrecover 解决坏块相关测试与总结
后一篇：Oracle DBA的学习进阶成长树-从初出茅庐到高瞻远瞩 >>
文章信息
© 2009 - 2024 by blogread.cn 微博：@IT技术博客大学习

京ICP备15002552号-1