前几天有朋友问我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的、有约束的,不一而足