oracle dump log的内容分析

执行下面的SQL，

SQL> insert into t1 values(6,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> alter system switch logfile;

trace文件为：oratest_ora_6535.trc

然后再alter system dump log file '/opt/oracle/oradata/oratest/arch/126.dbf'，得到的trace文件的信息：

 

REDO RECORD - Thread:1 RBA: 0x00007c.00000004.0010 LEN: 0x0100 VLD: 0x01
SCN: 0x0000.001b409d SUBSCN:  1 10/26/2010 18:45:20
CHANGE #1 TYP:0 CLS:20 AFN:2 DBA:0x00802d91 SCN:0x0000.001b409c SEQ:  2 OP:5.1
ktudb redo: siz: 84 spc: 392 flg: 0x0022 seq: 0x00b9 rec: 0x34
            xid:  0x0002.016.0000031d 
ktubu redo: slt: 22 rci: 51 opc: 11.1 objn: 6748 objd: 6748 tsn: 3
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x00802d91.00b9.32
KDO Op code: DRP row dependencies Disabled
  xtype: XA  bdba: 0x00c0002a  hdba: 0x00c00029
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 4(0x4)
CHANGE #2 TYP:0 CLS: 1 AFN:3 DBA:0x00c0002a SCN:0x0000.001b409c SEQ:  1 OP:11.2
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x00802d91.00b9.34
KDO Op code: IRP row dependencies Disabled
  xtype: XA  bdba: 0x00c0002a  hdba: 0x00c00029
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 4(0x4) size/delt: 14
fb: --H-FL-- lb: 0x2  cc: 2
null: --
col  0: [ 2]  c1 02
col  1: [ 7]  78 6e 0a 1a 13 2e 14
 
REDO RECORD - Thread:1 RBA: 0x00007c.00000004.0110 LEN: 0x009c VLD: 0x01
SCN: 0x0000.001b409d SUBSCN:  1 10/26/2010 18:45:20
CHANGE #1 TYP:0 CLS: 1 AFN:3 DBA:0x00c0002a SCN:0x0000.001b409d SEQ:  1 OP:11.3
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x00802d91.00b9.32
KDO Op code: DRP row dependencies Disabled
  xtype: XR  bdba: 0x00c0002a  hdba: 0x00c00029
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 4(0x4)
CHANGE #2 TYP:0 CLS:20 AFN:2 DBA:0x00802d91 SCN:0x0000.001b409d SEQ:  1 OP:5.6
ktubu redo: slt: 22 rci: 51 opc: 11.1 objn: 6748 objd: 6748 tsn: 3
Undo type:  Regular undo       Undo type:  User undo done   Last buffer split:  No
Tablespace Undo:  No
             0x00000000
ktuxvoff: 0x01ac  ktuxvflg: 0x0002

 

可以看到oracle 的redo log是按“REDO RECORD”组织的，在trace 文件中每个redo record之间有一个空行，而每个redo record是由redo record的头部和每个change vector组成的，trace文件中分别双“CHANGE #1”，“CHANGE #2”，“CHANGE #3”表示。

 

redo record头说明：

REDO RECORD - Thread:1 RBA: 0x00007c.00000004.0010 LEN: 0x0100 VLD: 0x01
SCN: 0x0000.001b409d SUBSCN:  1 10/26/2010 18:45:20

Thread: 1，表示这是由log thread 1产生的log，非RAC的数据库只有一个thread。

 

RBA: 0x00007c.00000004.0010，0x00007c是log的sequence号，0x7c就是124， 00000004表示第4个块，0010表示在块中的偏移量为0x0010。

 

LEN: 0x0100 表示这个record的长度。

 

VLD: 0x01 ，可能是一种状态，不确定。

 

SCN: 0x0000.001b409d ，是scn号，共6个字节，前两个字节的高位+4个字节的低位，如果后4个字节满了，就向前两个字节进位。

 

SUBSCN:  1， 在做某些操作时，SCN号不变，SUBSCN+1。

 

10/26/2010 18:45:20 : REDO RECORD的产生时间。

 

change vector说明：

CHANGE #1 TYP:0 CLS:20 AFN:2 DBA:0x00802d91 SCN:0x0000.001b409c SEQ:  2 OP:5.1
ktudb redo: siz: 84 spc: 392 flg: 0x0022 seq: 0x00b9 rec: 0x34
            xid:  0x0002.016.0000031d 
ktubu redo: slt: 22 rci: 51 opc: 11.1 objn: 6748 objd: 6748 tsn: 3
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x00802d91.00b9.32
KDO Op code: DRP row dependencies Disabled
  xtype: XA  bdba: 0x00c0002a  hdba: 0x00c00029
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 4(0x4)

 

TYP:0 为块的类型，

 

AFN:2 absolute file number，

 

DBA:0x00802d91，data block address，

扩展的rowid的结构说明如下：

+-----------+-----------+-----------+-----------+ 

|  OOOOOO   |    FFF    |   BBBBBB  |   RRR     | 

|数据对象编号 |相对文件编号|   块编号   |   行编号   |

+-----------+-----------+-----------+-----------+

|  32bit    |   10bit   |   22bit   |   16bit   |

+-----------+-----------+-----------+-----------+

总共80bit，10节字

DBA就是前10bit文件编号+22bit的块编号组合成的32bit的一个数字。

 

SEQ:2，    A sequence number incremented for each change to a block at the same SCN
                 A new SCN is allocated if the sequence number wraps. 

                 同一个SCN影响这个block中的行数大于 254 行就会为这个事务分配一个新的SCN

                 如下面的操作就可能引起同一个SCN但影响的同一个block 中的行超过254行
                 "delete from table_name"  
                 影响的行数(最大254) 是用从 0x01 到 0xfe 表示的

 

OP: 5.1，操作码，这个很重要，表示这个操作的类型。5.1表示是对undo的操作操作。

 

下面这些内容都是undo中的一些结构

ktudb redo: siz: 84 spc: 392 flg: 0x0022 seq: 0x00b9 rec: 0x34
            xid:  0x0002.016.0000031d 
ktubu redo: slt: 22 rci: 51 opc: 11.1 objn: 6748 objd: 6748 tsn: 3
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x00802d91.00b9.32
KDO Op code: DRP row dependencies Disabled
  xtype: XA  bdba: 0x00c0002a  hdba: 0x00c00029
itli: 2  ispac: 0  maxfr: 4863

 

objn: 6748 为object id，

objd: 6748 为data_object_id

 

 

--- fb:   1 byte   K = Cluster Key (Flags may change meaning if this is set to show HASH cluster)
---                C = Cluster table member
---                H = Head piece of row
---                D = Deleted row
---                F = First data piece
---                L = Last data piece
---                P = First column continues from previous piece
---                N = Last column continues in next piece

 

 

--- xid:     8 bytes    值可以用select XIDUSN, XIDSLOT,XIDSQN from v$transaction;查到
---                     This is comprised of the rollback segment number (2 bytes), the slot number
---                     in the transaction table of that rollback segment (2 bytes), and the number
---                     of times use of that transaction table has wrapped (4 bytes). 
--- Uba:     8 bytes    The location of the undo for the most recent change to this block by this
---                     transaction. This is comprised of the DBA of the rollback segment block (4
---                     bytes), the sequence number (2 bytes), and the record number for the change
---                     in that undo block (1 byte), plus 1 unused byte. 

 

 

另外一个redo log分析的文章：http://www.oraclefans.cn/forum/showtopic.jsp?rootid=6451&CPages=1

REDO RECORD - Thread:1 RBA: 0x0000a1.000040ce.0010 LEN: 0x0054 VLD: 0x01  ---change vectors copied in
SCN: 0x0000.00f7a9c9 SUBSCN:  1 03/12/2008 09:37:49
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800111 SCN:0x0000.00f7a9c7 SEQ:  1 OP:5.4   ----Commit transaction (transaction table update)
                                                                                 - no undo record dba(2/273)---SYS_SYSSMU3$
ktucm redo: slt: 0x0007 sqn: 0x0000682b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00806146.0706.16 ext: 2 spc: 2274 fbi: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040cf.0010 LEN: 0x0058 VLD: 0x02  ----A commit SCN was allocated and is stored
SCN: 0x0000.00f7a9cb SUBSCN:  1 03/12/2008 09:37:57
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1   ----write behind logging of blocks - KCOCOLWR 
                                                                      ----Dummy block written callback - KCBBLWR
 Block Written - afn: 2 rdba: 0x008095f1(2,38385)                     ----undo segment
                   scn: 0x0000.00f79cf1 seq: 0x02 flg:0x04
 Block Written - afn: 2 rdba: 0x0080726b(2,29291)                    ------undo segment
                   scn: 0x0000.00f7a1e1 seq: 0x02 flg:0x04           ------undo segment
 Block Written - afn: 2 rdba: 0x00806e2e(2,28206)                    ------undo segment
                   scn: 0x0000.00f79d50 seq: 0x02 flg:0x04
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.0010 LEN: 0x00b8 VLD: 0x01  ----change vectors copied in
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS: 4 AFN:5 DBA:0x01401a63 SCN:0x0000.00f7a965 SEQ:  3 OP:13.28 
Redo on Level1 Bitmap Block
Redo to add range
bdba: Length: 16
CHANGE #2 TYP:0 CLS: 8 AFN:5 DBA:0x01401a61 SCN:0x0000.00f7a965 SEQ:  2 OP:13.22  ----dba (5/6753)-- scott.t4的segment header，设置高水位
Redo on Level1 Bitmap Block
Redo to set hwm
Opcode: 32      Highwater::  0x01401a71  ext#: 0      blk#: 16     ext size: 16   
  #blocks in seg. hdr's freelists: 0    
  #blocks below: 13   
  mapblk  0x00000000  offset: 0    
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.00c8 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a64 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  --dba(5/6756) --scott.t4，格式化BLOCK
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.0104 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a65 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.0140 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a66 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.017c LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a67 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.01b8 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a68 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21     ----格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d0.01f4 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a69 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21   ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.0040 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6a SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21   ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.007c LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6b SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21   ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.00b8 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6c SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.00f4 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6d SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.0130 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6e SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.016c LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a6f SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21   ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.01a8 LEN: 0x003c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x01401a70 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.21  ---格式化block
ktspbfredo - Format Pagetable Datablock
Parent(l1) DBA: typ: 1 objd: 32027 itls: 2 fmt_flag: 0 poff: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d1.01e4 LEN: 0x0044 VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS: 8 AFN:5 DBA:0x01401a61 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.22  --对于l1 bmb的redo,state change
Redo on Level1 Bitmap Block
Redo for state change
Len: 13 Offset: 3 newstate: 5
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d2.0038 LEN: 0x0078 VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS: 4 AFN:5 DBA:0x01401a63 SCN:0x0000.00f7a9cd SEQ:  1 OP:13.28  ----对于L1 bmb的redo，delete range
Redo on Level1 Bitmap Block
Redo to delete range
 Length: 0
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d2.00b0 LEN: 0x015c VLD: 0x01
SCN: 0x0000.00f7a9cd SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800071 SCN:0x0000.00f7a38b SEQ:  1 OP:5.2    ----Update rollback segment header - KTURDH SYS_SYSSMU2$
ktudh redo: slt: 0x000f sqn: 0x00004947 flg: 0x0012 siz: 80 fbi: 0
            uba: 0x008090cb.0550.13    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:2 DBA:0x008090cb SCN:0x0000.00f7a38a SEQ:  3 OP:5.1   ---Undo block or undo segment header - KTURDB  SYS_SYSSMU4$
ktudb redo: siz: 80 spc: 2746 flg: 0x0012 seq: 0x0550 rec: 0x13
            xid:  0x0004.00f.00004947 
ktubl redo: slt: 15 rci: 0 opc: 11.1 objn: 32027 objd: 32027 tsn: 5              ----Interpret Undo Record (Undo) ，针对scott.t4表
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x008090cb.0550.10
prev ctl max cmt scn:  0x0000.00f78d7e  prev tx cmt scn:  0x0000.00f78f06
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01 
op: Z                                                                          ------Undo of first (ever) change to the ITL
KDO Op code: DRP row dependencies Disabled                                     ------Delete Row Piece
  xtype: XA  bdba: 0x01401a65  hdba: 0x01401a63                                  -----ROWID
itli: 1  ispac: 0  maxfr: 2401
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS: 1 AFN:5 DBA:0x01401a65 SCN:0x0000.00f7a9cd SEQ:  2 OP:11.2   ---Insert Row Piece
KTB Redo
op: 0x01  ver: 0x01 
op: F  xid:  0x0004.00f.00004947    uba: 0x008090cb.0550.13                       ---First change to ITL by this TX. Copy redo to ITL
KDO Op code: IRP row dependencies Disabled                                        ---Single Insert Row Piece
  xtype: XA  bdba: 0x01401a65  hdba: 0x01401a63                                   ---scott.t4
itli: 1  ispac: 0  maxfr: 2401
tabn: 0 slot: 0(0x0) size/delt: 6
fb: --H-FL-- lb: 0x1  cc: 1
null: -
col  0: [ 2]  c1 02                                                                                         ----十进制1，就是我们插入的数据
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.20              ----Transaction continue audit log record，记录SESSION信息
session number   = 11
serial  number   = 115
transaction name =
 
REDO RECORD - Thread:1 RBA: 0x0000a1.000040d4.0010 LEN: 0x0054 VLD: 0x01        ----
SCN: 0x0000.00f7a9cf SUBSCN:  1 03/12/2008 09:38:03
CHANGE #1 TYP:0 CLS:23 AFN:2 DBA:0x00800071 SCN:0x0000.00f7a9cd SEQ:  1 OP:5.4  ----Commit transaction (transaction table update)
ktucm redo: slt: 0x000f sqn: 0x00004947 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008090cb.0550.13 ext: 2 spc: 2664 fbi: 0
END OF REDO DUMP