A-A+

浅析undo

2014年08月01日 BasicKnowledge 暂无评论 阅读 1,166 次

该文档从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
标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: