通过undo找到前镜像

SQL> show user
USER 为 "TEST"
SQL> create table t(id int ,name varchar2(10));
表已创建。

SQL> insert into t values(1,'a');
已创建 1 行。
SQL> insert into t values(2,'b');
已创建 1 行。
SQL> commit;
提交完成。

SQL> select * from t;
        ID NAME
---------- ----------
         1 a
         2 b


SQL> update t set name='c' where id=1;  --没有提交
已更新 1 行。
SQL> select t.rowid,id,name from t;
ROWID                      ID NAME
------------------ ---------- ----------
AAAMkAAAFAAAAAmAAA          1 c
AAAMkAAAFAAAAAmAAB          2 b
会话二:
SQL> select * from t;


        ID NAME
---------- ----------
         1 a
         2 b
 查到的是提交的数据

会话一:
查看t表中的数据属于哪个数据文件哪个块
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   5                                   38
                                   5                                   38

sys用户下dump数据块
====================================================================================
seg/obj: 0xc900  csc: 0x00.a835a  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400021 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.007.0000015c  0x00800477.00cc.1c  C---    0  scn 0x0000.000a832e
0x02   0x0005.027.00000143  0x008000ef.0121.3e  ----    1  fsc 0x0000.00000000
 
data_block_dump,data header at 0x7ee2264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x07ee2264
bdba: 0x01400026
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f90
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  63
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 38 maxblk 38
====================================================================================
Uba:DBA.seq#.rec#

当会话二查询的时候会到uba:008000ef这个地址去查找数据
SQL> select to_number('008000ef','xxxxxxxx') from dual;
TO_NUMBER('008000EF','XXXXXXXX')
--------------------------------
                         8388847
SQL> select dbms_utility.data_block_address_file(8388847) fno,dbms_utility.data_block_address_block(8388847) bno from dual;

       FNO        BNO
---------- ----------
         2        239
SQL> select file_name,file_id,tablespace_name from dba_data_files where file_id=2;  --正好是undo表空间


FILE_NAME                                                       FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ---------- ---------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                   2 UNDOTBS1

--dump undo块
SQL> alter system dump datafile 2 block 239;

系统已更改。
UNDO BLK:  
xid: 0x0005.027.00000143  seq: 0x121 cnt: 0x3e  irb: 0x3e  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f40     0x02 0x1ec8     0x03 0x1e60     0x04 0x1df8     0x05 0x1d78     
0x06 0x1d20     0x07 0x1ca0     0x08 0x1c48     0x09 0x1ba0     0x0a 0x1b28     
0x0b 0x1ac0     0x0c 0x1a58     0x0d 0x19f0     0x0e 0x1970     0x0f 0x1918     
0x10 0x1870     0x11 0x17c8     0x12 0x1760     0x13 0x16f8     0x14 0x1690     
0x15 0x15e8     0x16 0x1568     0x17 0x1508     0x18 0x14c8     0x19 0x1478     
0x1a 0x1400     0x1b 0x1398     0x1c 0x1330     0x1d 0x12b0     0x1e 0x1250     
0x1f 0x1210     0x20 0x11c0     0x21 0x1140     0x22 0x10e0     0x23 0x10a0     
0x24 0x1050     0x25 0x0fa8     0x26 0x0f00     0x27 0x0e58     0x28 0x0de0     
0x29 0x0d38     0x2a 0x0c90     0x2b 0x0c28     0x2c 0x0b80     0x2d 0x0b08     
0x2e 0x0aa0     0x2f 0x09f8     0x30 0x0980     0x31 0x08d8     0x32 0x0860     
0x33 0x07b8     0x34 0x0740     0x35 0x0698     0x36 0x0620     0x37 0x0578     
0x38 0x0500     0x39 0x0458     0x3a 0x03b0     0x3b 0x0348     0x3c 0x02a0     
0x3d 0x0238     0x3e 0x01a0     


这里cnt:0x3e对应uba中的rec#,根据cnt:0x3e查找
SQL> select xidusn,xidslot,xidsqn from v$transaction;  --xid  xid: 0x0005.027.00000143 

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         5         39        323


SQL> select to_number('5','xx') from dual;
TO_NUMBER('5','XX')
-------------------
                  5
SQL> select to_number('27','xx') from dual;
TO_NUMBER('27','XX')
--------------------
                  39
SQL> select to_number('143','xxx') from dual;

TO_NUMBER('143','XXX')
----------------------
                   323


*-----------------------------
* Rec #0x3e  slt: 0x27  objn: 51456(0x0000c900)  objd: 51456  tblspc: 6(0x00000006)
*       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: 0x008000ef.0121.3c ctl max scn: 0x0000.000a7bc7 prv tx scn: 0x0000.000a7be6
txn start scn: scn: 0x0000.000a82d9 logon user: 55
 prev brb: 0 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: 9
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01400026  hdba: 0x01400023
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  61
 
找到了前镜像


SQL> select data_object_id from dba_objects where object_name='T'; --对象id
DATA_OBJECT_ID
--------------
         51456
SQL> select usn,latch,xacts from v$rollstat;   --第5个段有活动事务
       USN      LATCH      XACTS
---------- ---------- ----------
         0          0          0
         1          0          0
         2          0          0
         3          0          0
         4          0          0
         5          0          1
         6          0          0
         7          0          0
         8          0          0
         9          0          0
        10          0          0


已选择11行。
==================会话二再更新
SQL> update t set name='d' where id=1;  --没有提交
已更新 1 行。

dump数据块
lock header dump:  0x01400026
 Object id on Block? Y
 seg/obj: 0xc900  csc: 0x00.a835a  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400021 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.007.0000015c  0x00800477.00cc.1c  C---    0  scn 0x0000.000a832e
0x02   0x0005.027.00000143  0x008000ef.0121.3f  ----    1  fsc 0x0000.00000000
 
data_block_dump,data header at 0x7ee2264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x07ee2264
bdba: 0x01400026
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f88
avsp=0x1f70
tosp=0x1f70
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f90
0x14:pri[1] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  64
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62
end_of_block_dump


dump undo块
-----------------------------
* Rec #0x3e  slt: 0x27  objn: 51456(0x0000c900)  objd: 51456  tblspc: 6(0x00000006)
*       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: 0x008000ef.0121.3c ctl max scn: 0x0000.000a7bc7 prv tx scn: 0x0000.000a7be6
txn start scn: scn: 0x0000.000a82d9 logon user: 55
 prev brb: 0 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: 9
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01400026  hdba: 0x01400023
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  61  
 
*-----------------------------
* Rec #0x3f  slt: 0x27  objn: 51456(0x0000c900)  objd: 51456  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x3e   
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  
op: C  uba: 0x008000ef.0121.3e
Array Update of 1 rows: 
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01400026  hdba: 0x01400023
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  63  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26937943/viewspace-1198166/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26937943/viewspace-1198166/