通过dump功能获取回滚段信息

回滚段中只包含修改数据列的前像，而不是记录/数据块的前像。
本文通过DUMP回滚段信息对此进行验证。

--版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

--1,测试表
drop table test;
create table test(id int,name varchar2(10));

insert into test values(1,'abc1');
insert into test values(2,'abc2');
insert into test values(3,'abc3');
commit;

select user_id from dba_users where username='MH';
148

select object_id
  from dba_objects
 where wner = 'MH'
   and object_name = 'TEST';
157690

select   id,rowid,
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
from test;

 ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAAmf6AAEAAAAqQAAA          4       2704          0
         2 AAAmf6AAEAAAAqQAAB          4       2704          1
         3 AAAmf6AAEAAAAqQAAC          4       2704          2

 

--2,dump data block
alter system checkpoint;
alter system dump datafile 4 block 2704;

 在数据块尾部：
E2723C0 44012803 00014F01 0306013C 045009C2  [.(.D.O..<.....p.>E2723D0 4B1C02C3 09807807 2C031317 C1020201  [...K.x.....,....]
E2723E0 62610404 012C3363 03C10202 63626104  [..abc3,......abc]
E2723F0 02012C32 0402C102 31636261 2B330601  [2,......abc1..3+]

0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8d
0x14:pri[1] offs=0x1f82
0x16:pri[2] offs=0x1f77
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  61 62 63 31
tab 0, row 1, @0x1f82
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 4]  61 62 63 32
tab 0, row 2, @0x1f77
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 4]  61 62 63 33

--3，更新数据块
update test set name='aaa' where id=2;

SQL> select sid from v$mystat where rownum<2;

       SID
----------
       644

--4，在新会话中dump数据块
alter system checkpoint;
alter system dump datafile 4 block 2704;

--数据块最后几行
E91B3C0 44012803 00014F01 0306013C 045009C2  [.(.D.O..<.....p.>E91B3D0 02022CC3 0303C102 2C616161 C1020200  [.,......aaa,....]
E91B3E0 62610404 002C3363 03C10202 63626104  [..abc3,......abc]
E91B3F0 02002C32 0402C102 31636261 2D290601  [2,......abc1..)-]

Block header dump:  0x01000a90
 Object id on Block? Y
 seg/obj: 0x267fa  csc: 0x570.8742d29  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000a89 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000d.029.00005d0c  0x00807380.1070.20  C---    0  scn 0x0570.08742b33
0x02   0x0004.011.0000f0c7  0x00806d71.3714.31  ----    1  fsc 0x0001.00000000

==>Xid：0x0004.011.0000f0c7

data_block_dump,data header at 0xe919464
...
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8d
0x14:pri[1] offs=0x1f6d
0x16:pri[2] offs=0x1f77
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  61 62 63 31
tab 0, row 1, @0x1f6d  --〉地址已变更
tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  61 61 61  --〉数据已变更为aaa
tab 0, row 2, @0x1f77  --〉地址未变更
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 4]  61 62 63 33

--5，dump undo block
--通过v$transaction查找
select xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec
  from v$transaction
 where addr = (select taddr from v$session where sid = 644);

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         4         17      61639      28017          2         49

--或者可通过数据块ITL上的XID查找
select to_number('4','xxxxx') und
,to_number('011','xxxxx') 
,to_number('f0c7','xxxxx') 
from dual;
4                       17                     61639

--5.1 dump undo segment header by usn:
SQL> select name from v$rollname where usn=4;

NAME
------------------------------
_SYSSMU4$

alter system dump undo header "_SYSSMU4$";

--跟踪文件：
********************************************************************************
Undo Segment:  _SYSSMU4$ (4)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 11     #blocks: 1167 
                  last map  0x00000000  #maps: 0      offset: 4080 
      Highwater::  0x00806d71  ext#: 10     blk#: 104    ext size: 128  
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 0    
  mapblk  0x00000000  offset: 10   
                   Unlocked
     Map Header:: next  0x00000000  #extents: 11   obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080003a  length: 7    
   0x00800041  length: 8    
   0x00800509  length: 128  
   0x00801709  length: 128  
   0x00801889  length: 128  
   0x00808d89  length: 128  
   0x0080b289  length: 128  
   0x00803309  length: 128  
   0x0080d989  length: 128  
   0x00804089  length: 128  
   0x00806d09  length: 128  
 
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1853763185
 Extent Number:1  Commit Time: 1853766072
 Extent Number:2  Commit Time: 1853808843
 Extent Number:3  Commit Time: 1853861321
 Extent Number:4  Commit Time: 1853921137
 Extent Number:5  Commit Time: 1853967488
 Extent Number:6  Commit Time: 1854006058
 Extent Number:7  Commit Time: 1854039622
 Extent Number:8  Commit Time: 1854068457
 Extent Number:9  Commit Time: 1854129219
 Extent Number:10  Commit Time: 1853728857
 
  TRN CTL:: seq: 0x3714 chd: 0x0024 ctl: 0x0019 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00806d71.3714.31 scn: 0x0570.08742066
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.3714.30 ext: 0xa  spc: 0x252  
    uba: 0x00000000.3714.15 ext: 0xa  spc: 0x1224 
    uba: 0x00000000.3714.03 ext: 0xa  spc: 0x1e18 
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0xf0c9  0x001d  0x0570.08742957  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156345
   0x01    9    0x00  0xf0c8  0x002d  0x0570.08742b6f  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156704
   0x02    9    0x03  0xf0c8  0x001f  0x0570.08742534  0x00806d6b  0x0000.000.00000000  0x00000001   0x00806d6b  1854155575
...
   0x0e    9    0x00  0xf0cb  0x000d  0x0570.08742a2a  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156444
   0x0f    9    0x00  0xf0c8  0x0023  0x0570.08742868  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156227
   0x10    9    0x00  0xf0c9  0x0017  0x0570.087429d2  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156378
*  0x11   10    0x80  0xf0c7  0x000a  0x0570.08742d29  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  0
   0x12    9    0x00  0xf0c6  0x002e  0x0570.087425be  0x00806d6d  0x0000.000.00000000  0x00000001   0x00000000  1854155591
   0x13    9    0x00  0xf0c7  0x0016  0x0570.08742678  0x00806d6e  0x0000.000.00000000  0x00000001   0x00000000  1854155748
   0x14    9    0x00  0xf0a6  0x000a  0x0570.0874240a  0x00806d6b  0x0000.000.00000000  0x00000001   0x00000000  1854155429
...
   0x2d    9    0x00  0xf0c9  0x0019  0x0570.08742bb4  0x00806d71  0x0000.000.00000000  0x00000001   0x00000000  1854156769
   0x2e    9    0x00  0xf0c7  0x001a  0x0570.087425cd  0x00806d6d  0x0000.000.00000000  0x00000001   0x00000000  1854155603
   0x2f    9    0x00  0xf0c9  0x000c  0x0570.08742476  0x00806d6b  0x0000.000.00000000  0x00000001   0x00000000  1854155495

--&gtslot=17=0x11:state=10,cmt=0,dba=0x00806d71
select dbms_utility.data_block_address_file(to_number('806d71', 'xxxxxxxx')),
       dbms_utility.data_block_address_block(to_number('806d71', 'xxxxxxxx'))
  from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('806D71','XXXXXXXX'))
--------------------------------------------------------------------
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('806D71','XXXXXXXX'))
---------------------------------------------------------------------
                                                                   2
                                                                28017
==>v$transaction.UBABLK=28017  UBAFIL=2 UBAREC=49

5.2 dump undo block by datafile+block:
alter system dump datafile 2 block 28017;

--跟踪文件,UBAREC=49=0x31
 
********************************************************************************
UNDO BLK: 
xid: 0x0004.011.0000f0c7  seq: 0x3714 cnt: 0x31  irb: 0x31  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f4c     0x02 0x1edc     0x03 0x1e40     0x04 0x1de0     0x05 0x1d80    
0x06 0x1c98     0x07 0x1bf0     0x08 0x1b48     0x09 0x1aa0     0x0a 0x19b8    
0x0b 0x1910     0x0c 0x1868     0x0d 0x17c0     0x0e 0x1718     0x0f 0x163c    
0x10 0x1560     0x11 0x1478     0x12 0x13d0     0x13 0x12f4     0x14 0x1218    
0x15 0x117c     0x16 0x1108     0x17 0x1058     0x18 0x0fa0     0x19 0x0f04    
0x1a 0x0e78     0x1b 0x0e2c     0x1c 0x0dc0     0x1d 0x0d10     0x1e 0x0c84    
0x1f 0x0c30     0x20 0x0b3c     0x21 0x0a84     0x22 0x09e8     0x23 0x096c    
0x24 0x08bc     0x25 0x083c     0x26 0x07bc     0x27 0x073c     0x28 0x06bc    
0x29 0x063c     0x2a 0x05bc     0x2b 0x053c     0x2c 0x04bc     0x2d 0x043c    
0x2e 0x03bc     0x2f 0x0338     0x30 0x02c4     0x31 0x0228    
 
...
*-----------------------------
* Rec #0x31  slt: 0x11  objn: 157690(0x000267fa)  objd: 157690  tblspc: 4(0x00000004)
*       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: 0x00806d71.3714.24 ctl max scn: 0x0570.08742043 prv tx scn: 0x0570.08742066
txn start scn: scn: 0x0570.08742d29 logon user: 148
 prev brb: 8416618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000a90  hdba: 0x01000a8b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 55
ncol: 2 nnew: 1 size: 1
col  1: [ 4]  61 62 63 32  --&gt仅修改列的前像

5.3 dump undo block by xid:
语法：ALTER SYSTEM DUMP UNDO BLOCK ’segment_name’ XID xidusn xidslot xidsqn;

alter system dump undo block "_SYSSMU4$" xid 4 17 61639;
--跟踪文件如下：
********************************************************************************
Undo Segment:  _SYSSMU4$ (4)
xid: 0x0004.011.0000f0c7
Low Blk   :   (0, 0)
High Blk  :   (10, 127)
Object Id :   ALL
Layer     :   ALL
Opcode    :   ALL
Level     :   2
 
********************************************************************************
UNDO BLK:  Extent: 10   Block: 104   dba (file#, block#): 2,0x00006d71
xid: 0x0004.011.0000f0c7  seq: 0x3714 cnt: 0x31  irb: 0x31  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f4c     0x02 0x1edc     0x03 0x1e40     0x04 0x1de0     0x05 0x1d80    
0x06 0x1c98     0x07 0x1bf0     0x08 0x1b48     0x09 0x1aa0     0x0a 0x19b8    
0x0b 0x1910     0x0c 0x1868     0x0d 0x17c0     0x0e 0x1718     0x0f 0x163c    
0x10 0x1560     0x11 0x1478     0x12 0x13d0     0x13 0x12f4     0x14 0x1218    
0x15 0x117c     0x16 0x1108     0x17 0x1058     0x18 0x0fa0     0x19 0x0f04    
0x1a 0x0e78     0x1b 0x0e2c     0x1c 0x0dc0     0x1d 0x0d10     0x1e 0x0c84    
0x1f 0x0c30     0x20 0x0b3c     0x21 0x0a84     0x22 0x09e8     0x23 0x096c    
0x24 0x08bc     0x25 0x083c     0x26 0x07bc     0x27 0x073c     0x28 0x06bc    
0x29 0x063c     0x2a 0x05bc     0x2b 0x053c     0x2c 0x04bc     0x2d 0x043c    
0x2e 0x03bc     0x2f 0x0338     0x30 0x02c4     0x31 0x0228    
 
*-----------------------------
* Rec #0x31  slt: 0x11  objn: 157690(0x000267fa)  objd: 157690  tblspc: 4(0x00000004)
*       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: 0x00806d71.3714.24 ctl max scn: 0x0570.08742043 prv tx scn: 0x0570.08742066
txn start scn: scn: 0x0570.08742d29 logon user: 148
 prev brb: 8416618 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000a90  hdba: 0x01000a8b
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 55
ncol: 2 nnew: 1 size: 1
col  1: [ 4]  61 62 63 32
 
 
+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++
 
 
*************************************
Total undo blocks scanned  = 1
Total undo records scanned = 1
Total undo blocks dumped   = 1
Total undo records dumped  = 1
 
##Total warnings issued = 1
*************************************

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

转载于:http://blog.itpub.net/18922393/viewspace-756173/