這篇文章中，我們將通過Undo, Redo, DataFile的dump來看看Oracle中一個(gè)transaction過程。

1：提交當(dāng)前session中的pending transaction,
SQL> show autocommit;
autocommit OFF
SQL> commit;

Commit complete.

2：確定transaction前的scn，
SQL> connect sys/coffee@testdb as sysdba;
Connected.
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
                            41683588

3：數(shù)據(jù)更新

SQL> connect system/coffee@testdb;
Connected.
SQL> update undo_test set id=456 where id=123;

1 row updated.

4：查看剛剛操作的transaction及回滾段信息
SQL> select xidusn, xidslot, xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10         27      20807          2       1375       1473         45

XIDUSN：回滾段Number
XIDSLOT：事務(wù)槽號
XIDSQN：事務(wù)序列號
 UBAFIL：UBA所在文件號
 UBABLK：UBA所在塊號
UBASQN：UBA序列號
UBAREC：UBA在Block中的位置

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

       USN NAME
---------- ------------------------------
        10 _SYSSMU10$

這里的usn即為transaction的xidusn

5：Dump回滾段的頭信息及塊信息
SQL> alter system dump undo header '_SYSSMU10$';

System altered.

回滾段的header中會(huì)紀(jì)錄事務(wù)槽信息，
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x1a    9    0x00  0x5147  0x0019  0x0000.027c09b1  0x0080055f  0x0000.000.00000000  0x00000001   0x00000000
   0x1b   10    0x80  0x5147  0x0002  0x0000.027c0b03  0x0080055f  0x0000.000.00000000  0x00000001   0x00000000
   0x1c    9    0x00  0x5146  0x001d  0x0000.02778562  0x0080055d  0x0000.000.00000000  0x00000001   0x00000000

注意：state：10為active, 9為inactive
            dba：為回滾塊的地址，根據(jù)這個(gè)地址，我們可以計(jì)算出file#及block#
                      0x0080055f  
                      0000 0000 10 00 0000 0000 0101 0101 1111
                      ----------------  ------------------------------------
                      file#2              block#1375
              當(dāng)然這個(gè)值可以不用計(jì)算，transaction的ubafil，ubablk分別為file#, block#。

SQL> alter system dump datafile 2 block 1375;

System altered.

這里我們可以看到數(shù)據(jù)的前鏡像值，
  UNDO BLK: 
 xid: 0x000a.01b.00005147  seq: 0x5c1 cnt: 0x2d  irb: 0x2d  icl: 0x0   flg: 0x0000
 
  Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
 ---------------------------------------------------------------------------
 0x01 0x1fb4     0x02 0x1f4c     0x03 0x1efc     0x04 0x1ec8     0x05 0x1e78    
 0x06 0x1e28     0x07 0x1df4     0x08 0x1da4     0x09 0x1d54     0x0a 0x1d20    
 0x0b 0x1cd0     0x0c 0x1c80     0x0d 0x1c4c     0x0e 0x1bfc     0x0f 0x1bac    
 0x10 0x1b78     0x11 0x1b28     0x12 0x1ad8     0x13 0x1aa4     0x14 0x1a54    
 0x15 0x1a04     0x16 0x19d0     0x17 0x1980     0x18 0x1930     0x19 0x18fc    
 0x1a 0x18ac     0x1b 0x185c     0x1c 0x1828     0x1d 0x17d8     0x1e 0x1788    
 0x1f 0x16d4     0x20 0x1648     0x21 0x15bc     0x22 0x1518     0x23 0x14c8    
 0x24 0x1460     0x25 0x13f8     0x26 0x1390     0x27 0x1328     0x28 0x12c0    
 0x29 0x1258     0x2a 0x11f0     0x2b 0x1188     0x2c 0x1120     0x2d 0x10b8 
塊頭反映了這個(gè)塊中有多少事務(wù)槽，我們要看的就是這個(gè)紅色的0x2d, (即transaction的UBAREC=45)
 
 *-----------------------------
 * Rec #0x2d  slt: 0x1b  objn: 30387(0x000076b3)  objd: 30387  tblspc: 0(0x00000000)<----30387是我們操作對象（undo_test）
 *       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: 0x0080055f.05c1.2c ctl max scn: 0x0000.0277854e prv tx scn: 0x0000.02778558
 KDO undo record:
 KTB Redo
 op: 0x03  ver: 0x01 
 op: Z
 KDO Op code: URP row dependencies Disabled
   xtype: XA  bdba: 0x0040c732  hdba: 0x0040c731
 itli: 2  ispac: 0  maxfr: 4863
 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
 ncol: 1 nnew: 1 size: 0
 col  0: [ 3]  c2 02 18<----前鏡像值（123）


6：Dump數(shù)據(jù)文件，數(shù)據(jù)文件有沒有內(nèi)容，決定于DBWr進(jìn)程有沒有將data buffer cache寫出

SQL> select file_id, block_id from dba_extents where segment_name='UNDO_TEST';<----即表名

   FILE_ID   BLOCK_ID
---------- ----------
         1      50993

SQL> alter system dump datafile 1 block  min 50993 block max 50995;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.011.00005147  0x0080055f.05c1.23  C---    0  scn 0x0000.027c094a
0x02   0x000a.01b.00005147  0x0080055f.05c1.2d  ----    1  fsc 0x0000.00000000
Itl：記錄了該數(shù)據(jù)塊上發(fā)生過的transaction。
Xid：xidusn.xidslot.xidsqn
Uba：uba.ubasqn.ubarec
Xid和Uba的內(nèi)容和V$transactio中的內(nèi)容一致。

data_block_dump,data header at 0x30a105c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x030a105c
bdba: 0x0040c732
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f99
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f99
block_row_dump:
tab 0, row 0, @0x1f99
tl: 7 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 3]  c2 05 39<----修改后的數(shù)據(jù)，即456
end_of_block_dump
buffer tsn: 0 rdba: 0x0040c733 (1/50995)
scn: 0x0000.00000000 seq: 0x01 flg: 0x05 tail: 0x00000001
frmt: 0x02 chkval: 0xc073 type: 0x00=unknown
Dump of memory from 0x030A1014 to 0x030A2FFC
30A1010          00000000 00000000 00000000      [............]
30A1020 00000000 00000000 00000000 00000000  [................]
        Repeat 508 times
30A2FF0 00000000 00000000 00000000           [............]   
End dump data blocks tsn: 0 file#: 1 minblk 50993 maxblk 50995

7：提交事務(wù)

SQL> commit;

Commit complete.

SQL> connect sys/coffee@testdb as sysdba;
Connected.

查看事務(wù)提交后的scn，我們將以此作為redo log dump的內(nèi)容。
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
------------------------------------
                            41684393

查看當(dāng)前活動(dòng)的redo log，
SQL> select log.group#, log.status, logfile.member
  2  from v$log log, v$logfile logfile
  3  where log.group#=logfile.group#;

    GROUP# STATUS           MEMBER
---------- ---------------- ----------------------------------------
         1 INACTIVE         D:\ORACLE\ORADATA\TESTDB\REDO01.LOG
         2 CURRENT          D:\ORACLE\ORADATA\TESTDB\REDO02.LOG
         3 INACTIVE         D:\ORACLE\ORADATA\TESTDB\REDO03.LOG

SQL> alter system dump logfile 'D:\ORACLE\ORADATA\TESTDB\REDO02.LOG' scn min 41683588 scn max 41684393;

System altered.

Redo中對于一個(gè)transaction的改變向量紀(jì)錄如下

REDO RECORD - Thread:1 RBA: 0x00008a.000004a2.0010 LEN: 0x0190 VLD: 0x01
SCN: 0x0000.027c0b03 SUBSCN:  1 06/11/2009 15:01:35
CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.027c09bb SEQ:  1 OP:5.2<----Update rollback segment header - KTURDH
ktudh redo: slt: 0x001b sqn: 0x00005147 flg: 0x0012 siz: 104 fbi: 0
            uba: 0x0080055f.05c1.2d    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:2 DBA:0x0080055f SCN:0x0000.027c09ba SEQ:  1 OP:5.1<----Undo block or undo segment header - KTURDB

ktudb redo: siz: 104 spc: 4278 flg: 0x0012 seq: 0x05c1 rec: 0x2d
            xid:  0x000a.01b.00005147 
ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 30387 objd: 30387 tsn: 0
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x0080055f.05c1.2c
prev ctl max cmt scn:  0x0000.0277854e  prev tx cmt scn:  0x0000.02778558
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA  bdba: 0x0040c732  hdba: 0x0040c731
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
col  0: [ 3]  c2 02 18
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040c732 SCN:0x0000.027c094a SEQ:  1 OP:11.5<----Update Row Piece
KTB Redo
op: 0x11  ver: 0x01 
op: F  xid:  0x000a.01b.00005147    uba: 0x0080055f.05c1.2d
Block cleanout record, scn:  0x0000.027c0b03 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.027c094a
KDO Op code: URP row dependencies Disabled
  xtype: XA  bdba: 0x0040c732  hdba: 0x0040c731
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
col  0: [ 3]  c2 05 39
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.20<----Transaction continue audit log record
session number   = 9
serial  number   = 10
transaction name =

......

CHANGE #1 TYP:0 CLS:35 AFN:2 DBA:0x00800099 SCN:0x0000.027c0b03 SEQ:  1 OP:5.4<----Commit transaction
ktucm redo: slt: 0x001b sqn: 0x00005147 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080055f.05c1.2d ext: 2 spc: 4172 fbi: 0 

    從上面的過程來看，
    Undo：分配事務(wù)槽及記錄數(shù)據(jù)前鏡像。
    DataFile：記錄事務(wù)信息及修改后的數(shù)據(jù)。
    Redo：記錄事務(wù)槽分配、回滾段信息、修改后數(shù)據(jù)信息、事務(wù)提交信息等。