根据ITL找到前镜像

SQL> select * from emp where sal=800;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 12/17/1980     800.00               20
1 rows selected
SQL> select dbms_rowid.rowid_relative_fno(rowid)file_id,dbms_rowid.rowid_block_number(rowid)block_id from emp  where sal=800;

   FILE_ID   BLOCK_ID
---------- ----------
         5        204 
SQL> update emp set sal=1000 where sal=800;
1 row updated
SQL> alter system dump datafile 5 block 204;
系统已更改。
SQL> select  d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  2              from
  3              ( select p.spid
  4              from sys.v$mystat m,sys.v$session s,sys.v$process p
  5              where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  6              ( select t.instance from sys.v$thread  t,sys.v$parameter  v
  7              where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
  8              ( select value from sys.v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
C:/ORACLE/PRODUCT/10.2.0/ADMIN/ROBINSON/UDUMP/robinson_ora_8056.trc
找到ITL记录，这里我们看0x03,应为update语句会lock一行，这里只有0x03的loc为1.
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.027.00000402  0x008000cd.0246.09  C---    0  scn 0x0000.001a027c
0x02   0x0003.021.000003e9  0x008002f5.021f.16  C---    0  scn 0x0000.001a41a8
0x03   0x0005.016.000003f9  0x008006de.0190.12  ----    1  fsc 0x0000.00000000
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         5         22       1017          2       1758        400         18
从DSI中可以知道ITL中的XID=usn#.slot#.wrap#
我们找到0X03中的XID=0x0005.016.000003f9
SQL> select to_number('0005','XXXXX') from dual;
TO_NUMBER('0005','XXXXX')
-------------------------
                        5
SQL> select to_number('016','XXXXX')from dual;
TO_NUMBER('016','XXXXX')
------------------------
                      22
SQL> select to_number('000003f9','XXXXXXXXX')from dual;
TO_NUMBER('000003F9','XXXXXXXX
------------------------------
                          1017
在V$TRANSACTION中记录的XIDUSN,XIDSLOT,XIDSQN与ITL中的xid相吻合，下面验证uba
从DSI中可以知道UBA=DBA.seq#.rec#
现在找到0X03中的uba=0x008006de.0190.12,0x008006de表示的是DBA，那么我们可以用dbms_utility.data_block_address_file，
dbms_utility.data_block_address_block这两个函数来解析DBA
SQL> select dbms_utility.data_block_address_file(to_number('008006de','xxxxxxxxxxxx'))file_id,dbms_utility.data_block_address_block(
  2  to_number('008006de','xxxxxxxxxxxx'))block_id from dual;

   FILE_ID   BLOCK_ID
---------- ----------
         2       1758
SQL> select to_number('0190','xxxx') from dual;
TO_NUMBER('0190','XXXX')
------------------------
                     400
SQL> select to_number('12','xxxx') from dual;

TO_NUMBER('12','XXXX')
----------------------
                    18
到此，ITL中的记录完全吻合v$transaction的记录，现在dump undo回滚段，找到更改的前镜像
SQL> alter system dump datafile 2 block 1758;
系统已更改。
SQL> select  d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  2              from
  3              ( select p.spid
  4              from sys.v$mystat m,sys.v$session s,sys.v$process p
  5              where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  6              ( select t.instance from sys.v$thread  t,sys.v$parameter  v
  7              where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
  8              ( select value from sys.v$parameter where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
C:/ORACLE/PRODUCT/10.2.0/ADMIN/ROBINSON/UDUMP/robinson_ora_6588.trc
找到dump出来的文件,undo block中也必须记录xid 0x0005.016.000003f9这个信息，因此我们可以在undo的dump文件中查找该ITL是否存在，已经找到该信息
UNDO BLK:  
xid: 0x0005.016.000003f9  seq: 0x190 cnt: 0x12  irb: 0x12  icl: 0x0   flg: 0x0000
注意仔细查看XID与之前的XID 相同，然后我们根据cnt，也就是UBA=DBA.seq#.rec#中的rec#找到该记录rec#=x12也就是十进制的18也就是undo中的Rec #0x12,看这里的slt=0x16与ITL中记录xid 016吻合，到此ORACLE就能从这些信息找到前镜像了，下面我们还原c2 09,看看c2 09是否等于800
* Rec #0x12  slt: 0x16  objn: 52556(0x0000cd4c)  objd: 52556  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: 0x008006de.0190.0f ctl max scn: 0x0000.001a54ba prv tx scn: 0x0000.001a550b
txn start scn: scn: 0x0000.001a6167 logon user: 55
 prev brb: 8390282 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
op: L  itl: xid:  0x0006.00d.0000040c uba: 0x00800616.017a.26
                      flg: C---    lkc:  0     scn: 0x0000.0019ffe9
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x014000cc  hdba: 0x014000cb
itli: 3  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 0
col  5: [ 2]  c2 09
SQL> declare n number;
  2       begin
  3       dbms_stats.convert_raw_value('c209',n);
  4       dbms_output.put_line(n);
  5       end;
  6  /
800
PL/SQL procedure successfully completed
果然 c209等于800，到此，大家应该知道ORACLE是怎么通过ITL找到前纪录了吧。