一个REDO LOG DUMP的例子

 

前几天有朋友问我DML操作会生成哪些REDO，其实这个问题十分复杂，各种场景都有所不同。按照Oracle的官方说法，针对所有产生的数据块的变更（除了临时表空间的变更外）都需要写REDO，包括因此产生的UNDO。

这里我设计了一个最简单的场景：

scott:

create table t4 (a integer);

sys：

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

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

然后对下面的SQL进行分析：

SCOTT:

insert into t4 values (1);

commit;

 

SYS:

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

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

针对这2个SQL，生成的REDO，通过DUMP

SQL> alter system dump logfile 'd:/oracle/oradata/ora92/redo01.log' scn min 16230857 scn max 16230863;

系统已更改。

 

下面是TRACE文件，----后面是我的注释：

Dump file d:/oracle/admin/ora92/udump/ora92_ora_1996.trc
Wed Mar 12 09:39:21 2008
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.1 Service Pack 2, CPU type 586
Instance name: ora92

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 1996, image: ORACLE.EXE

*** 2008-03-12 09:39:21.734
*** SESSION ID:(12.16) 2008-03-12 09:39:21.687
 
DUMP OF REDO FROM FILE 'd:/oracle/oradata/ora92/redo01.log'
 Opcodes *.*
 DBA's: (file # 0, block # 0) thru (file # 65534, block # 4194303)
 RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCN's scn: 0x0000.00f7a9c9 thru scn: 0x0000.00f7a9cf
 Times: creation thru eternity
 FILE HEADER:
 Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
 Db ID=1953145418=0x746aa24a, Db Name='ORA92'
 Activation ID=1953179004=0x746b257c
 Control Seq=1645=0x66d, File size=204800=0x32000
 File Number=1, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000161, SCN 0x000000f74a49-0xffffffffffff"
 thread: 1 nab: 0xffffffff seq: 0x000000a1 hws: 0x5 eot: 1 dis: 0
 reset logs count: 0x263732da scn: 0x0000.0002fd60
 Low scn: 0x0000.00f74a49 03/11/2008 08:17:16
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 Enabled scn: 0x0000.0002fd60 12/12/2007 17:41:46
 Thread closed scn: 0x0000.00f765fa 03/11/2008 08:53:49
 Log format vsn: 0x8000000 Disk cksum: 0xabeb Calc cksum: 0xabeb
 Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 0 blocks
 End-of-redo stream : No
Unprotected mode
 Miscellaneous flags: 0x0
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:17.3
Crash Recovery at scn:  0x0000.00f74a48
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:17.3
Crash Recovery at scn:  0x0000.00f765fa
 
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
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 8392Kb in 1.14s => 7.02 Mb/sec
Longest record: 2Kb, moves: 0/30942 (0%)
Change moves: 8044/71490 (11%), moved: 0Mb
----------------------------------------------

从TRACE可以看出，下列部分可能产生REDO：

1、事务控制方面的

2、UNDO信息

3、BMB的变更

4、ITL的变更

5、SEGMENT HEADER的变更

6、数据块的变更（DML操作的修改）

7、COMMIT命令

8、SESSION的信息

。。。。

这个是最简单的情况，对于有索引的，有触发器的，有AUDIT的、有约束的，不一而足

 

 

 

 

from:http://www.oraclefans.cn/forum/showtopic.jsp?rootid=6451&CPages=1