通过dump分析undo镜像内容

发表于 2012 年 4 月 7 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：通过dump分析undo镜像内容

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.模拟表并插入数据

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t_xff(id number,name varchar2(10));

Table created.

SQL> insert into t_xff values(1,'a');

1 row created.

SQL> insert into t_xff values(2,'b');

1 row created.

SQL> insert into t_xff values(3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL>  select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xff;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASfUAAEAAAACvAAA          4        175          0
AAASfUAAEAAAACvAAB          4        175          1
AAASfUAAEAAAACvAAC          4        175          2

二.dump当前表数据

SQL> alter system dump datafile 4 block 175;

System altered.

--表中数据
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62     <---注意原始值(b)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63

三.修改表中数据(新建会话并不提交)

SQL> select * from t_xff;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> update t_xff set name='F' where id=2;

1 row updated.

SQL> select * from t_xff;

        ID NAME
---------- --------------------
         1 a
         2 F
         3 c

四.dump修改后数据块

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.


block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  46     <--数据内容已经修改(由b改为了F)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63
end_of_block_dump

五.找出本次更新操作对应undo块
1.通过v$transaction视图找出


SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2         31        750       8155          3          6

2.通过更新块的XID信息找出

Block header dump:  0x010000af
 Object id on Block? Y
 seg/obj: 0x127d4  csc: 0x00.11216d  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10000a8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.000001ff  0x00c01748.009f.10  C---    0  scn 0x0000.00112130
0x02   0x0002.01f.000002ee  0x00c01fdb.00f5.06  ----    1  fsc 0x0000.00000000
bdba: 0x010000af
data_block_dump,data header at 0xb6ce9664
--这里可以看出Itl=0x02为锁信息

SQL> select name from v$rollname where usn=2;

NAME
------------------------------------------------------------
_SYSSMU2_4228238222$

SQL> alter system dump undo header "_SYSSMU2_4228238222$";

System altered.

 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x02ee  0x0019  0x0000.0010cc90  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x01    9    0x00  0x02ee  0x0018  0x0000.0010cf00  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662985
   0x02    9    0x00  0x02ee  0x0000  0x0000.0010cc84  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x03    9    0x00  0x02ee  0x0011  0x0000.00112094  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670810
   0x04    9    0x00  0x02ee  0x0012  0x0000.0010ccc1  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x05    9    0x00  0x02ee  0x0017  0x0000.0010cd13  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x06    9    0x00  0x02ee  0x0004  0x0000.0010ccb9  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x07    9    0x00  0x02ee  0xffff  0x0000.00112119  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x08    9    0x00  0x02ee  0x0006  0x0000.0010ccab  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x09    9    0x00  0x02ee  0x000a  0x0000.0010ccf4  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0a    9    0x00  0x02ee  0x0014  0x0000.0010ccf8  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0b    9    0x00  0x02ee  0x001a  0x0000.0010d061  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x0c    9    0x00  0x02ee  0x0009  0x0000.0010ccdc  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0d    9    0x00  0x02ee  0x0001  0x0000.0010ce1f  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662386
   0x0e    9    0x00  0x02ee  0x001d  0x0000.00112113  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x0f    9    0x00  0x02ed  0x0002  0x0000.0010cc79  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x10    9    0x00  0x02ee  0x001e  0x0000.00112017  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1333670781
   0x11    9    0x00  0x02ed  0x000e  0x0000.001120dd  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670813
   0x12    9    0x00  0x02ee  0x000c  0x0000.0010ccd3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x13    9    0x00  0x02ee  0x0016  0x0000.0010cd2e  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x14    9    0x00  0x02ee  0x0005  0x0000.0010cd0b  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x15    9    0x00  0x02ed  0x0020  0x0000.0010cc9d  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x16    9    0x00  0x02ee  0x000d  0x0000.0010cd33  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x17    9    0x00  0x02ee  0x0013  0x0000.0010cd20  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x18    9    0x00  0x02ee  0x000b  0x0000.0010d051  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x19    9    0x00  0x02ed  0x0015  0x0000.0010cc96  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1a    9    0x00  0x02ed  0x001b  0x0000.0010d102  0x00c01fda  0x0000.000.00000000  0x00000002   0x00000000  1333664305
   0x1b    9    0x00  0x02ee  0x0010  0x0000.0010d13e  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333664453
   0x1c    9    0x00  0x02c5  0x000f  0x0000.0010cc72  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1d    9    0x00  0x02ee  0x0007  0x0000.00112115  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x1e    9    0x00  0x02ee  0x0021  0x0000.00112035  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670797
   0x1f   10    0x80  0x02ee  0x0003  0x0000.00112157  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  0
   0x20    9    0x00  0x02ed  0x0008  0x0000.0010cca3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x21    9    0x00  0x02ec  0x0003  0x0000.00112052  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670803

SQL> select to_number('00c01fdb','xxxxxxxxxxx') from dual;

TO_NUMBER('00C01FDB','XXXXXXXXXXX')
-----------------------------------
                           12591067

SQL> select dbms_utility.data_block_address_file(12591067) file#,
  2  dbms_utility.data_block_address_block(12591067) block  from dual;

     FILE#      BLOCK
---------- ----------
         3       8155

3.通过更新块的Uba信息找出

00c01fdb 对应的2进制为:
0000 0000  11 | 00 0000 0001 1111 1101 1011
2+1=3           4096+2048+1024+512+256+128+64+16+8+2+1=8155

六.dump 对应undo数据块

SQL> alter system dump datafile 3 block 8155;

System altered.


uba: 0x00c01fdb.00f5.04 ctl max scn: 0x0000.0010cc60 prv tx scn: 0x0000.0010cc6e
txn start scn: scn: 0x0000.00112028 logon user: 84
 prev brb: 12591059 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x010000af  hdba: 0x010000aa
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62   <---以前的值(b)

试验说明:数据库的undo只是保存修改值的前镜像，而非修改数据块或者行记录的镜像

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《通过dump分析undo镜像内容》有 3 条评论

  2. 梁 说：
    2012 年 4 月 20 日11:29

    index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
    0x1f 10 0×80 0x02ee 0×0003 0×0000.00112157 0x00c01fdb 0×0000.000.00000000 0×00000001 0×00000000 0
    难道是通过stat=10这个标志位找到的dba吗？

  3. 梁 说：
    2012 年 4 月 20 日11:21

    SQL> select to_number(’00c01fdb’,’xxxxxxxxxxx’) from dual;
    你是如何确定这个dba就是你需要的dba