深入SUPPLEMENTAL LOGGING
Posted: September 2, 2012 | Author: Cui Hua | Filed under: Oracle | 4 Comments »早就想写一篇关于Oracle中supplemental logging的文章了，姗姗妈随她们单位出去旅游了，姗姗也不用我看，我终于有了写这篇文章的时间。
Oracle中的supplemental logging分为两种，一种是全库级别的supplemental logging，一种是表级别的supplemental logging。它们的基本定义和类型我这里不再详述，有兴趣的朋友可以去查Oracle的文档。
这篇文章里我主要试图回答如下问题：
1、supplemental logging在redo log里到底会额外的写入什么内容？
2、全库级别的supplemental logging和表级别的supplemental logging的各种组合的效果是什么？
3、为什么supplemental logging一开启就会禁掉IMU（In Memory Undo）？
4、为什么logminer在10g中使用时必须要开启supplemental logging而在9i中则不需要？
5、为什么GoldenGate在同步数据时会要求不仅要在全库级别开启supplemental logging，还会额外的要求要在待同步的目标表上也要开启表级别的supplemental logging？
我们还是从一个demo开始：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create table t(id number,c1 varchar2(10),c2 varchar2(10),c3 varchar2(10),c4 varchar2(10));
Table created
SQL> alter table t add constraint pk_t primary key (id);
Table altered
SQL> insert into t values(12345678,’aaaaaa1′,’aaaaaa2′,’aaaaaa3′,’aaaaaa4′);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
NO NO NO NO NO
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
3
SQL> select member from v$logfile where group#=3;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518013947
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518013970
SQL> select object_id,data_object_id from dba_objects where object_name=’T’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
82294 82294
SQL> select dump(12345678,16) from dual;
DUMP(12345678,16)
————————–
Typ=2 Len=5: c4,d,23,39,4f
SQL> select dump(‘aaaaaa1’,16) from dual;
DUMP(‘AAAAAA1’,16)
———————————-
Typ=96 Len=7: 61,61,61,61,61,61,31
SQL> select dump(‘aaaaaa11’,16) from dual;
DUMP(‘AAAAAA11’,16)
————————————-
Typ=96 Len=8: 61,61,61,61,61,61,31,31
SQL> select dump(‘aaaaaa2’,16) from dual;
DUMP(‘AAAAAA2’,16)
———————————-
Typ=96 Len=7: 61,61,61,61,61,61,32
SQL> select dump(‘aaaaaa3’,16) from dual;
DUMP(‘AAAAAA3’,16)
———————————-
Typ=96 Len=7: 61,61,61,61,61,61,33
SQL> select dump(‘aaaaaa4’,16) from dual;
DUMP(‘AAAAAA4’,16)
———————————-
Typ=96 Len=7: 61,61,61,61,61,61,34
SQL> oradebug setmypid
已处理的语句
SQL> oradebug unlimit
已处理的语句
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG’ scn min 7518013947 scn max 7518013970;
系统已更改。
SQL> oradebug tracefile_name
c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001e0.0000000d.0010 LEN: 0x020c VLD: 0x0d
SCN: 0x0001.c01bca04 SUBSCN: 1 09/01/2012 20:33:01
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01bc85b SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0007.020.0000074b uba: 0x00c006ef.02fd.23
Block cleanout record, scn: 0x0001.c01bca02 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01bc85b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 46
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #2 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0001.c01bc99f SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0020 sqn: 0x0000074b flg: 0x0012 siz: 136 fbi: 0
uba: 0x00c006ef.02fd.23 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0001.c01bca04 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0020 sqn: 0x0000074b srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c006ef.02fd.23 ext: 3 spc: 1856 fbi: 0
CHANGE #4 TYP:0 CLS:30 AFN:3 DBA:0x00c006ef OBJ:4294967295 SCN:0x0001.c01bc99f SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 1994 flg: 0x0012 seq: 0x02fd rec: 0x23
xid: 0x0007.020.0000074b
ktubl redo: slt: 32 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c006ef.02fd.20
prev ctl max cmt scn: 0x0001.c01bc540 prev tx cmt scn: 0x0001.c01bc55b
txn start scn: 0x0001.c01bc9de logon user: 84 prev brb: 12584686 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 46
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
从上述trace内容中我们可以看到：
1、在supplemental log未开启的情况下，即在IMU的情况下redo中依然会有描述undo的redo；
2、因为开启了IMU，所以描述redo的redo和描述undo的redo在从private redo log buffer和in memory undo pool写入public redo log buffer时存在一个简单合并的过程，这就可以解释为什么在开启了IMU的情况下，描述undo的redo在redo log中的位置会在描述redo的redo的位置的后面，而在通常情况下描述undo的redo在redo log中的位置会在描述redo的redo的位置的前面。
我们再用UltraEdit看一下上述描述undo的redo记录：
00001b60h: 7C 00 42 50 05 01 1E 00 03 00 FF FF EF 06 C0 00 ; |.BP……??
00001b70h: 9F C9 1B C0 01 00 AF 33 03 00 FF FF 0E 00 14 00 ; 熒.?.?..….
00001b80h: 4C 00 02 00 1D 00 02 00 07 00 00 00 88 00 CA 07 ; L………..??
00001b90h: 12 00 C0 00 07 00 20 00 4B 07 00 00 FD 02 23 00 ; ..?.. .K…?#.
00001ba0h: 76 41 01 00 76 41 01 00 04 00 00 00 00 00 00 00 ; vA..vA……….
00001bb0h: 0B 01 20 00 08 0C 01 00 00 00 00 00 EF 06 C0 00 ; .. ………??
00001bc0h: FD 02 20 00 40 C5 1B C0 01 00 1B 00 5B C5 1B C0 ; ? .@??…[??
00001bd0h: 01 00 00 00 00 00 00 00 DE C9 1B C0 01 00 11 00 ; ……..奚.?…
00001be0h: EE 06 C0 00 00 00 00 00 54 00 00 00 03 05 1B C0 ; ??….T……?
00001bf0h: F7 54 01 01 F2 54 01 01 FA 12 05 01 02 00 00 00 ; 鱐..騎..?……
00001c00h: 01 22 00 00 0E 00 00 00 E0 01 00 00 2C 80 6A 3C ; .”……?..,€j<
00001c10h: 2C 00 2E 00 00 00 05 01 FF FF 00 00 00 00 00 00 ; ,…….……
00001c20h: 01 00 00 00 61 61 61 61 61 61 31 00 00 00 00 00 ; ….aaaaaa1…..
00001c30h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
00001c40h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
00001c50h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
00001c60h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
00001c70h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，例如上述change #4中有如下记录：
CHANGE #4 TYP:0 CLS:30 AFN:3 DBA:0x00c006ef OBJ:4294967295 SCN:0x0001.c01bc99f SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 1994 flg: 0x0012 seq: 0x02fd rec: 0x23
xid: 0x0007.020.0000074b
这里的DBA:0x00c006ef就是00001b60h中的EF 06 C0 00
这里的SCN:0x0001.c01bc99f就是00001b70h中的9F C9 1B C0 01 00
这里的ktudb redo: siz: 136就是00001b80h中的88
这里的spc: 1994就是00001b80h中的CA 07
这里的xid: 0x0007.020.0000074b就是00001b90h中的07 00 20 00 4B 07 00 00
aaaaaa1所对应的61 61 61 61 61 61 31在00001c20h中
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现搜不到。
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现只能搜索到两处，分别为如下所示：
00001ad0h: 00 02 00 00 01 00 3C 00 61 61 61 61 61 61 31 31 ; ……<.aaaaaa11
00001c20h: 01 00 00 00 61 61 61 61 61 61 31 00 00 00 00 00 ; ….aaaaaa1…..
所以这里可以确认当supplemental logging未开启且目标表有主键的情况下Oracle在redo中不会记录主键，并且只会记录被修改的列的当前值和前镜像。
现在我们把表t中的那条记录恢复成原来的样子并以minimal模式开启supplemental logging后再次尝试同样的操作:
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter database add supplemental log data;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
2
SQL> select member from v$logfile where group#=2;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518043648
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518043662
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG’ scn min 7518043648 scn max 7518043662;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001e2.00000011.0010 LEN: 0x0228 VLD: 0x05
SCN: 0x0001.c01c3e06 SUBSCN: 1 09/01/2012 21:35:56
CHANGE #1 TYP:0 CLS:25 AFN:3 DBA:0x00c000c0 OBJ:4294967295 SCN:0x0001.c01c3dd4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000f sqn: 0x00000a71 flg: 0x0012 siz: 184 fbi: 0
uba: 0x00c0098a.0323.0b pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:26 AFN:3 DBA:0x00c0098a OBJ:4294967295 SCN:0x0001.c01c3dd3 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 184 spc: 6806 flg: 0x0012 seq: 0x0323 rec: 0x0b
xid: 0x0005.00f.00000a71
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0098a.0323.0a
prev ctl max cmt scn: 0x0001.c01c399e prev tx cmt scn: 0x0001.c01c39a0
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12585350 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0007.020.0000074b uba: 0x00c006ef.02fd.23
flg: C— lkc: 0 scn: 0x0001.c01bca04
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c3d9c SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0005.00f.00000a71 uba: 0x00c0098a.0323.0b
Block cleanout record, scn: 0x0001.c01c3e06 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c3d9c
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 21
serial number = 6
transaction name =
version 186646784
audit sessionid 665625
Client Id =
从上述trace内容中我们可以看到，当把supplemental logging以minimum开启后（此时IMU会被禁掉）且目标表t有主键的情况下，上述update操作所产生的redo dump中并没有记录主键列的值。
但上述dump内容中ktudb redo size的值已经从之前的136变为了184。
同样的update操作，为啥描述undo的redo的ktudb redo的size变大了？
只有一种可能，就是redo dump虽然看不到supplemental loggging的内容，但Oracle实际上已经在ktudb redo中写入了supplemental log。即supplemental loggging实际上是额外的记录在描述undo的redo所在的change vector中。
我们再用UltraEdit看一下上述描述undo的redo记录：
00002290h: 05 01 1A 00 03 00 FF FF 8A 09 C0 00 D3 3D 1C C0 ; ……???.?
000022a0h: 01 00 C1 36 01 00 FF FF 10 00 14 00 4C 00 1C 00 ; ..?..….L…
000022b0h: 1D 00 02 00 07 00 14 00 B8 00 96 1A 12 00 00 00 ; ……..??….
000022c0h: 05 00 0F 00 71 0A 00 00 23 03 0B 00 76 41 01 00 ; ….q…#…vA..
000022d0h: 76 41 01 00 04 00 00 00 00 00 00 00 0B 01 0F 00 ; vA…………..
000022e0h: 08 0C 01 00 00 00 00 00 8A 09 C0 00 23 03 0A 00 ; ……..??#…
000022f0h: 9E 39 1C C0 01 00 00 00 A0 39 1C C0 01 00 00 00 ; ?.?…?.?…
00002300h: 00 00 00 00 FF FF FF FF FF FF 00 00 86 09 C0 00 ; …...??
00002310h: 00 00 00 00 54 00 00 00 04 05 14 00 07 00 20 00 ; ….T……… .
00002320h: 4B 07 00 00 EF 06 C0 00 FD 02 23 00 00 80 01 00 ; K…???#..€..
00002330h: 04 CA 1B C0 F7 54 01 01 F2 54 01 01 FA 12 25 01 ; .?厉T..騎..?%.
00002340h: 02 00 EB 36 2C 00 00 00 00 00 05 01 FF FF 00 00 ; ..?,……...
00002350h: 00 00 13 00 01 00 00 00 61 61 61 61 61 61 31 00 ; ……..aaaaaa1.
00002360h: 01 1C 00 00 01 00 02 00 02 00 00 00 00 10 00 00 ; …………….
00002370h: 00 00 00 00 0B 05 01 00 04 00 01 00 F7 54 01 01 ; …………鱐..
00002380h: 9C 3D 1C C0 01 00 C1 36 02 02 76 41 0A 00 3C 00 ; ?.?.?..vA..<.
00002390h: 1D 00 02 00 08 00 C1 36 11 05 00 00 05 00 0F 00 ; ……?……..
000023a0h: 71 0A 00 00 8A 09 C0 00 23 03 0B 00 34 F1 2C 36 ; q…??#…4?6
000023b0h: 0A 00 80 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ..€………….
000023c0h: 02 01 01 00 06 3E 1C C0 01 00 00 00 01 02 01 00 ; …..>.?…….
000023d0h: 9C 3D 1C C0 F7 54 01 01 F2 54 01 01 FA 12 05 01 ; ?.厉T..騎..?..
000023e0h: 02 00 EB 36 2C 02 00 00 00 00 05 01 01 00 00 00 ; ..?,………..
000023f0h: 00 00 00 00 01 00 00 00 61 61 61 61 61 61 31 31 ; ……..aaaaaa11
00002400h: 01 22 00 00 12 00 00 00 E2 01 00 00 48 80 6E A5 ; .”……?..H€n
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，
例如上述change #2中有如下记录：
CHANGE #2 TYP:0 CLS:26 AFN:3 DBA:0x00c0098a OBJ:4294967295 SCN:0x0001.c01c3dd3 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 184 spc: 6806 flg: 0x0012 seq: 0x0323 rec: 0x0b
xid: 0x0005.00f.00000a71
这里的DBA:0x00c0098a就是00002290h中的8A 09 C0 00
这里的SCN:0x0001.c01c3dd3就是00002290h中的D3 3D 1C C0和和000022a0h中的01 00
这里的ktudb redo: siz: 184就是000022b0h中的B8
这里的spc: 6806就是000022b0h中的96 1A
这里的xid: xid: 0x0005.00f.00000a71就是000022c0h中的05 00 0F 00 71 0A 00 00
aaaaaa1所对应的61 61 61 61 61 61 31在00002350h中
aaaaaa11所对应的61 61 61 61 61 61 31 31在000023f0h中
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现搜不到。
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现只能搜索到两处，分别为如下所示：
00002350h: 00 00 13 00 01 00 00 00 61 61 61 61 61 61 31 00 ; ……..aaaaaa1.
000023f0h: 00 00 00 00 01 00 00 00 61 61 61 61 61 61 31 31 ; ……..aaaaaa11
所以这里可以确认当supplemental logging仅仅以minimal模式在全库开启且目标表有主键的情况下Oracle在redo中不会记录主键列的值，并且只会记录被修改的列的当前值和前镜像。
现在我们把表t中的那条记录恢复成原来的样子并以主键模式在全库开启supplemental logging后再次尝试同样的操作:
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter database drop supplemental log data;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
NO NO NO NO NO
SQL> alter database add supplemental log data (primary key) columns;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
IMPLICIT YES NO NO NO
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
1
SQL> select member from v$logfile where group#=1;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO01.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518046047
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518046067
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO01.LOG’ scn min 7518046047 scn max 7518046067;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001e4.00000010.0010 LEN: 0x0240 VLD: 0x05
SCN: 0x0001.c01c476c SUBSCN: 1 09/01/2012 22:08:41
CHANGE #1 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0001.c01c4743 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0000 sqn: 0x000009a4 flg: 0x0012 siz: 204 fbi: 0
uba: 0x00c00329.0480.1a pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:3 DBA:0x00c00329 OBJ:4294967295 SCN:0x0001.c01c4742 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 204 spc: 2848 flg: 0x0012 seq: 0x0480 rec: 0x1a
xid: 0x0003.000.000009a4
ktubl redo: slt: 0 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00329.0480.17
prev ctl max cmt scn: 0x0001.c01c44d1 prev tx cmt scn: 0x0001.c01c44e7
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12583720 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0005.00f.00000a71 uba: 0x00c0098a.0323.0b
flg: C— lkc: 0 scn: 0x0001.c01c3e08
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c4432 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0003.000.000009a4 uba: 0x00c00329.0480.1a
Block cleanout record, scn: 0x0001.c01c476c ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c4432
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 21
serial number = 6
transaction name =
version 186646784
audit sessionid 665625
Client Id =
从上述trace内容中我们可以看到，当把supplemental logging在全库以主键模式开启后（此时IMU会被禁掉）且目标表t有主键的情况下，上述update操作所产生的redo dump中并没有记录主键列的值。
但上述dump内容中ktudb redo size的值已经从之前的184变为了204，这意味着Oracle在redo log中记录的supplemental log中额外的写入了东西。
我们再用UltraEdit看一下上述描述undo的redo记录：
00002090h: 05 01 16 00 03 00 FF FF 29 03 C0 00 42 47 1C C0 ; ……).?BG.?
000020a0h: 01 00 1C 00 03 00 FF FF 16 00 14 00 4C 00 1C 00 ; ……….L…
000020b0h: 1D 00 02 00 07 00 14 00 02 00 02 00 05 00 00 00 ; …………….
000020c0h: CC 00 20 0B 12 00 00 00 03 00 00 00 A4 09 00 00 ; ? ………?..
000020d0h: 80 04 1A 00 76 41 01 00 76 41 01 00 04 00 00 00 ; €…vA..vA……
000020e0h: 00 00 00 00 0B 01 00 00 08 0C 01 00 00 00 00 00 ; …………….
000020f0h: 29 03 C0 00 80 04 17 00 D1 44 1C C0 01 00 00 00 ; ).?€…袲.?…
00002100h: E7 44 1C C0 01 00 00 00 00 00 00 00 FF FF FF FF ; 鏒.?…….
00002110h: FF FF 00 00 28 03 C0 00 00 00 00 00 54 00 00 00 ; ..(.?….T…
00002120h: 04 05 04 00 05 00 0F 00 71 0A 00 00 8A 09 C0 00 ; ……..q…??
00002130h: 23 03 0B 00 00 80 01 00 08 3E 1C C0 F7 54 01 01 ; #….€…>.厉T..
00002140h: F2 54 01 01 FA 12 25 01 02 00 EB 36 2C 00 00 00 ; 騎..?%…?,…
00002150h: 00 00 05 01 FF FF 00 00 00 00 00 00 01 00 00 00 ; ….……….
00002160h: 61 61 61 61 61 61 31 00 01 1C 01 00 01 00 02 00 ; aaaaaa1………
00002170h: 02 00 00 00 00 10 00 00 00 00 00 00 01 00 00 00 ; …………….
00002180h: 05 00 80 00 C4 0D 23 39 4F 00 01 06 0B 05 01 00 ; ..€.?#9O…….
00002190h: 04 00 01 00 F7 54 01 01 32 44 1C C0 01 00 C1 36 ; ….鱐..2D.?.?
000021a0h: 01 02 76 41 0A 00 3C 00 1D 00 02 00 08 00 C1 36 ; ..vA..<…….?
000021b0h: 11 05 00 00 03 00 00 00 A4 09 00 00 29 03 C0 00 ; ……..?..).?
000021c0h: 80 04 1A 00 C4 B0 2C 36 0A 00 80 00 00 00 00 00 ; €…陌,6..€…..
000021d0h: 00 00 00 00 00 00 00 00 02 01 01 00 6C 47 1C C0 ; …………lG.?
000021e0h: 01 00 00 00 01 02 01 00 32 44 1C C0 F7 54 01 01 ; ……..2D.厉T..
000021f0h: F2 54 01 01 FA 12 05 01 02 00 EB 36 2C 02 00 00 ; 騎..?….?,…
00002200h: 01 22 00 00 11 00 00 00 E4 01 00 00 60 80 2E 3D ; .”……?..`€.=
00002210h: 00 00 05 01 01 00 00 00 00 3F 1C C0 01 00 C0 00 ; ………?.?.?
00002220h: 61 61 61 61 61 61 31 31 05 14 00 00 00 00 00 00 ; aaaaaa11……..
00002230h: 00 00 00 00 00 00 00 00 00 00 00 00 00 06 00 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，比对的过程已经详细描述过两次，这里不再赘述。
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现能搜到一处，为如下所示：
00002180h: 05 00 80 00 C4 0D 23 39 4F 00 01 06 0B 05 01 00 ; ..€.?#9O…….
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现只能搜索到两处，分别为如下所示：
00002160h: 61 61 61 61 61 61 31 00 01 1C 01 00 01 00 02 00 ; aaaaaa1………
00002220h: 61 61 61 61 61 61 31 31 05 14 00 00 00 00 00 00 ; aaaaaa11……..
所以这里可以确认当supplemental logging在全库以主键模式开启且目标表有主键的情况下Oracle在redo中只会额外的记录主键列的值，且主键的记录位置（即supplemental logging的记录位置）是在前镜像61 61 61 61 61 61 31和当前值61 61 61 61 61 61 31 31之间，这也符合我之前提到的：supplemental loggging实际上是额外的记录在描述undo的redo所在的change vector中。
现在我们把表t中的那条记录恢复成原来的样子并保持全库主键模式的supplemental logging，然后drop掉表T的主键后再次尝试同样的操作:
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter table t drop constraint pk_t;
Table altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
IMPLICIT YES NO NO NO
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
3
SQL> select member from v$logfile where group#=3;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518047247
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518047260
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG’ scn min 7518047247 scn max 7518047260;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001e6.00000019.0010 LEN: 0x0264 VLD: 0x05
SCN: 0x0001.c01c4c16 SUBSCN: 1 09/01/2012 22:34:56
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0001.c01c4b69 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0003 sqn: 0x00000738 flg: 0x0012 siz: 244 fbi: 0
uba: 0x00c00c65.0323.13 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c00c65 OBJ:4294967295 SCN:0x0001.c01c4b68 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 244 spc: 4990 flg: 0x0012 seq: 0x0323 rec: 0x13
xid: 0x0001.003.00000738
ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00c65.0323.10
prev ctl max cmt scn: 0x0001.c01c45ea prev tx cmt scn: 0x0001.c01c460a
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12586082 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0003.000.000009a4 uba: 0x00c00329.0480.1a
flg: C— lkc: 0 scn: 0x0001.c01c476e
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c4b95 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0001.003.00000738 uba: 0x00c00c65.0323.13
Block cleanout record, scn: 0x0001.c01c4c16 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c4b95
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 21
serial number = 6
transaction name =
version 186646784
audit sessionid 665625
Client Id =
从上述trace内容中我们可以看到，当把supplemental logging以主键模式开启后（此时IMU会被禁掉）且目标表t没有主键的情况下，上述update操作所产生的redo dump中并没有记录主键列和其他列的值。
但上述dump内容中ktudb redo size的值已经从之前的204变为了244。这意味着Oracle又在redo log中记录的supplemental log中额外的写入了东西。
我们再用UltraEdit看一下上述描述undo的redo记录：
00003290h: 05 01 12 00 03 00 FF FF 65 0C C0 00 68 4B 1C C0 ; ……e.?hK.?
000032a0h: 01 00 00 00 03 00 FF FF 1C 00 14 00 4C 00 1C 00 ; ……….L…
000032b0h: 1D 00 02 00 07 00 14 00 08 00 08 00 05 00 07 00 ; …………….
000032c0h: 07 00 07 00 F4 00 7E 13 12 00 C4 36 01 00 03 00 ; ….?~…?….
000032d0h: 38 07 00 00 23 03 13 36 76 41 01 00 76 41 01 00 ; 8…#..6vA..vA..
000032e0h: 04 00 00 00 00 00 00 00 0B 01 03 00 08 0C 01 00 ; …………….
000032f0h: 00 00 00 00 65 0C C0 00 23 03 10 00 EA 45 1C C0 ; ….e.?#…闑.?
00003300h: 01 00 00 00 0A 46 1C C0 01 00 00 00 00 00 00 00 ; …..F.?…….
00003310h: FF FF FF FF FF FF 00 00 62 0C C0 00 00 00 00 00 ; ..b.?….
00003320h: 54 00 00 00 04 05 02 00 03 00 00 00 A4 09 00 00 ; T………..?..
00003330h: 29 03 C0 00 80 04 1A 00 00 80 01 00 6E 47 1C C0 ; ).?€….€..nG.?
00003340h: F7 54 01 01 F2 54 01 01 FA 12 25 01 02 00 00 00 ; 鱐..騎..?%…..
00003350h: 2C 00 00 00 00 00 05 01 FF FF 00 00 00 54 53 5F ; ,…….…TS_
00003360h: 01 00 52 4B 61 61 61 61 61 61 31 00 01 1C 04 00 ; ..RKaaaaaa1…..
00003370h: 01 00 02 00 02 00 00 00 00 10 00 00 00 00 00 00 ; …………….
00003380h: 01 00 03 00 04 00 05 00 05 00 07 00 07 00 07 00 ; …………….
00003390h: C4 0D 23 39 4F 05 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
000033a0h: 61 61 61 61 61 61 33 00 61 61 61 61 61 61 34 62 ; aaaaaa3.aaaaaa4b
000033b0h: 0B 05 01 00 04 00 01 00 F7 54 01 01 95 4B 1C C0 ; ……..鱐..昁.?
000033c0h: 01 00 00 00 01 02 76 41 0A 00 3C 00 1D 00 02 00 ; ……vA..<…..
000033d0h: 08 00 00 00 11 05 00 00 01 00 03 00 38 07 00 00 ; …………8…
000033e0h: 65 0C C0 00 23 03 13 00 00 00 00 00 00 00 00 00 ; e.?#………..
000033f0h: 00 00 00 00 00 00 00 00 00 00 00 00 02 01 01 00 ; …………….
00003400h: 01 22 00 00 1A 00 00 00 E6 01 00 00 84 80 55 C8 ; .”……?..剙U?
00003410h: 16 4C 1C C0 01 00 00 00 01 02 01 00 95 4B 1C C0 ; .L.?…….昁.?
00003420h: F7 54 01 01 F2 54 01 01 FA 12 05 01 02 00 00 00 ; 鱐..騎..?……
00003430h: 2C 02 00 00 00 00 05 01 01 00 00 00 00 41 47 45 ; ,…………AGE
00003440h: 01 00 18 00 61 61 61 61 61 61 31 31 05 14 00 00 ; ….aaaaaa11….
00003450h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 02 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，比对的过程已经详细描述过两次，这里不再赘述。
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现能搜到一处，为如下所示：
00003390h: C4 0D 23 39 4F 05 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现能搜索到五处，分别为如下所示：
00003360h: 01 00 52 4B 61 61 61 61 61 61 31 00 01 1C 04 00 ; ..RKaaaaaa1…..
00003390h: C4 0D 23 39 4F 05 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
000033a0h: 61 61 61 61 61 61 33 00 61 61 61 61 61 61 34 62 ; aaaaaa3.aaaaaa4b
00003440h: 01 00 18 00 61 61 61 61 61 61 31 31 05 14 00 00 ; ….aaaaaa11….
所以这里可以确认当supplemental logging在全库以主键模式开启且目标表没有主键的情况下Oracle会在redo中记录目标表所有的列，也就是说此时redo log中的supplemental log中会额外的记录目标表所有列的值。
现在我把全库改成minimal方式的supplemental logging并对表T单独开启主键模式的supplemental logging，然后对表T加上主键后再次尝试同样的操作：
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter table t add constraint pk_t primary key (id);
Table altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
IMPLICIT YES NO NO NO
SQL> alter database drop supplemental log data (primary key) columns;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
NO NO NO NO NO
SQL> alter database add supplemental log data;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> alter table t add supplemental log data (primary key) columns;
Table altered
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SCOTT SYS_C0017190 T PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
2
SQL> select member from v$logfile where group#=2;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518049783
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518049854
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG’ scn min 7518049783 scn max 7518049854;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001e8.00000011.0010 LEN: 0x0240 VLD: 0x05
SCN: 0x0001.c01c5607 SUBSCN: 1 09/01/2012 23:14:28
CHANGE #1 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0001.c01c55c4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x001c sqn: 0x0000098f flg: 0x0012 siz: 204 fbi: 0
uba: 0x00c01253.027b.3e pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:20 AFN:3 DBA:0x00c01253 OBJ:4294967295 SCN:0x0001.c01c55c3 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 204 spc: 2360 flg: 0x0012 seq: 0x027b rec: 0x3e
xid: 0x0002.01c.0000098f
ktubl redo: slt: 28 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c01253.027b.3b
prev ctl max cmt scn: 0x0001.c01c5243 prev tx cmt scn: 0x0001.c01c525b
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12587605 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0001.003.00000738 uba: 0x00c00c65.0323.13
flg: C— lkc: 0 scn: 0x0001.c01c4c17
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c5052 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0002.01c.0000098f uba: 0x00c01253.027b.3e
Block cleanout record, scn: 0x0001.c01c5607 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c5052
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 21
serial number = 6
transaction name =
version 186646784
audit sessionid 665625
Client Id =
从上述trace内容中我们可以看到，当把supplemental logging在全库以minimal模式开启后（此时IMU会被禁掉）且目标表t有主键，且在目标表单独开启主键模式的supplemental logging的情况下，上述update操作所产生的redo dump中并没有额外记录主键列的值。
但上述dump内容中ktudb redo size的值已经从之前的244变回了204。这意味着Oracle在redo log中记录的supplemental log中减少了写入的内容。
我们再用UltraEdit看一下上述描述undo的redo记录：
00002290h: 05 01 14 00 03 00 FF FF 53 12 C0 00 C3 55 1C C0 ; ……S.?肬.?
000022a0h: 01 00 00 00 03 00 FF FF 16 00 14 00 4C 00 1C 00 ; ……….L…
000022b0h: 1D 00 02 00 07 00 14 00 02 00 02 00 05 00 6B 12 ; …………..k.
000022c0h: CC 00 38 09 12 00 00 00 02 00 1C 00 8F 09 00 00 ; ?8………?..
000022d0h: 7B 02 3E 00 76 41 01 00 76 41 01 00 04 00 00 00 ; {.>.vA..vA……
000022e0h: 00 00 00 00 0B 01 1C 00 08 0C 01 00 00 00 00 00 ; …………….
000022f0h: 53 12 C0 00 7B 02 3B 00 43 52 1C C0 01 00 00 00 ; S.?{.;.CR.?…
00002300h: 5B 52 1C C0 01 00 00 00 00 00 00 00 FF FF FF FF ; [R.?…….
00002310h: FF FF 00 00 55 12 C0 00 00 00 00 00 54 00 00 00 ; ..U.?….T…
00002320h: 04 05 1C 00 01 00 03 00 38 07 00 00 65 0C C0 00 ; ……..8…e.?
00002330h: 23 03 13 00 00 80 01 00 17 4C 1C C0 F7 54 01 01 ; #….€…L.厉T..
00002340h: F2 54 01 01 FA 12 25 01 02 00 EB 36 2C 00 00 00 ; 騎..?%…?,…
00002350h: 00 00 05 01 FF FF 00 00 00 00 00 00 01 00 00 00 ; ….……….
00002360h: 61 61 61 61 61 61 31 00 01 1C 01 00 01 00 02 00 ; aaaaaa1………
00002370h: 02 00 00 00 00 10 00 00 00 00 00 00 01 00 00 00 ; …………….
00002380h: 05 00 00 00 C4 0D 23 39 4F 00 00 00 0B 05 01 00 ; ….?#9O…….
00002390h: 04 00 01 00 F7 54 01 01 52 50 1C C0 01 00 00 00 ; ….鱐..RP.?…
000023a0h: 01 02 76 41 0A 00 3C 00 1D 00 02 00 08 00 00 00 ; ..vA..<………
000023b0h: 11 05 00 00 02 00 1C 00 8F 09 00 00 53 12 C0 00 ; ……..?..S.?
000023c0h: 7B 02 3E 00 00 00 00 00 00 00 00 00 00 00 00 00 ; {.>………….
000023d0h: 00 00 00 00 00 00 00 00 02 01 01 00 07 56 1C C0 ; ………….V.?
000023e0h: 01 00 00 00 01 02 01 00 52 50 1C C0 F7 54 01 01 ; ……..RP.厉T..
000023f0h: F2 54 01 01 FA 12 05 01 02 00 EB 36 2C 02 00 00 ; 騎..?….?,…
00002400h: 01 22 00 00 12 00 00 00 E8 01 00 00 60 80 67 A7 ; .”……?..`€g?
00002410h: 00 00 05 01 01 00 00 00 00 4D 1C C0 01 00 00 00 ; ………M.?…
00002420h: 61 61 61 61 61 61 31 31 05 14 00 00 00 00 00 00 ; aaaaaa11……..
00002430h: 00 00 00 00 00 00 00 00 00 00 00 00 00 06 00 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，比对的过程已经详细描述过两次，这里不再赘述。
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现能搜到一处，为如下所示：
00002380h: 05 00 00 00 C4 0D 23 39 4F 00 00 00 0B 05 01 00 ; ….?#9O…….
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现只能搜索到两处，分别为如下所示：
00002360h: 61 61 61 61 61 61 31 00 01 1C 01 00 01 00 02 00 ; aaaaaa1………
00002420h: 61 61 61 61 61 61 31 31 05 14 00 00 00 00 00 00 ; aaaaaa11……..
所以这里可以确认当supplemental logging在全库以minimal模式开启且目标表有主键且目标表单独开启主键模式的supplemental logging的情况下Oracle在redo中只会额外的记录主键列的值，且主键的记录位置（即supplemental logging的记录位置）是在前镜像61 61 61 61 61 61 31和当前值61 61 61 61 61 61 31 31之间，这也符合我之前提到的：supplemental loggging实际上是额外的记录在描述undo的redo所在的change vector中。这里supplemetal log所在的ktudb redo的大小从之前的244变回了204，这当然是因为Oracle这里只需要额外的记录表T的主键，而不需要像刚才那样需要额外的记录表T的所有列。
现在我们把表t中的那条记录恢复成原来的样子并保持全库minimal模式的supplemental logging和目标表T主键模式的supplemental logging，然后drop掉表T的主键后再次尝试同样的操作:
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter table t drop constraint pk_t;
Table altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SCOTT SYS_C0017190 T PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
1
SQL> select member from v$logfile where group#=1;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO01.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518050938
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518050952
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO01.LOG’ scn min 7518050938 scn max 7518050952;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001ea.0000001b.0010 LEN: 0x0264 VLD: 0x05
SCN: 0x0001.c01c5a81 SUBSCN: 1 09/01/2012 23:35:16
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0001.c01c5a40 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x001f sqn: 0x00000973 flg: 0x0012 siz: 244 fbi: 0
uba: 0x00c00144.04d7.06 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:3 DBA:0x00c00144 OBJ:4294967295 SCN:0x0001.c01c5a3f SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 244 spc: 7396 flg: 0x0012 seq: 0x04d7 rec: 0x06
xid: 0x0009.01f.00000973
ktubl redo: slt: 31 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00144.04d7.05
prev ctl max cmt scn: 0x0001.c01c5569 prev tx cmt scn: 0x0001.c01c55c0
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12583232 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0002.01c.0000098f uba: 0x00c01253.027b.3e
flg: C— lkc: 0 scn: 0x0001.c01c5608
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c5a01 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0009.01f.00000973 uba: 0x00c00144.04d7.06
Block cleanout record, scn: 0x0001.c01c5a81 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c5a01
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 21
serial number = 6
transaction name =
version 186646784
audit sessionid 665625
Client Id =
从上述trace内容中我们可以看到，当把supplemental logging在全库以minimal模式开启后（此时IMU会被禁掉）且目标表t没有主键，且目标表单独开启主键模式的supplemental logging的情况下，上述update操作所产生的redo dump中并没有额外记录主键列的值。
但上述dump内容中ktudb redo size的值已经从之前的204变成了244，这显然是Oracle又在supplemental log中额外的记录了表T的所有列。
我们再用UltraEdit看一下上述描述undo的redo记录：
00003650h: B4 8F 42 2F 05 02 21 00 03 00 FF FF 00 01 C0 00 ; 磸B/..!…..?
00003660h: 40 5A 1C C0 01 00 00 00 01 00 FF FF 04 00 20 00 ; @Z.?….... .
00003670h: 1F 00 00 00 73 09 00 00 44 01 C0 00 D7 04 06 00 ; ….s…D.??..
00003680h: 12 00 F4 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ..?…………
00003690h: 05 01 22 00 03 00 FF FF 44 01 C0 00 3F 5A 1C C0 ; ..”…D.??Z.?
000036a0h: 01 00 18 00 01 00 FF FF 1C 00 14 00 4C 00 1C 00 ; ……….L…
000036b0h: 1D 00 02 00 07 00 14 00 08 00 08 00 05 00 07 00 ; …………….
000036c0h: 07 00 07 00 F4 00 E4 1C 12 00 C4 36 09 00 1F 00 ; ….??..?….
000036d0h: 73 09 00 00 D7 04 06 36 76 41 01 00 76 41 01 00 ; s…?.6vA..vA..
000036e0h: 04 00 00 00 00 00 00 00 0B 01 1F 00 08 0C 01 00 ; …………….
000036f0h: 00 00 00 00 44 01 C0 00 D7 04 05 00 69 55 1C C0 ; ….D.??..iU.?
00003700h: 01 00 00 00 C0 55 1C C0 01 00 00 00 00 00 00 00 ; ….繳.?…….
00003710h: FF FF FF FF FF FF 00 00 40 01 C0 00 00 00 00 00 ; ..@.?….
00003720h: 54 00 00 00 04 05 02 00 02 00 1C 00 8F 09 00 00 ; T………..?..
00003730h: 53 12 C0 00 7B 02 3E 00 00 80 01 00 08 56 1C C0 ; S.?{.>..€…V.?
00003740h: F7 54 01 01 F2 54 01 01 FA 12 25 01 02 00 00 00 ; 鱐..騎..?%…..
00003750h: 2C 00 00 00 00 00 05 01 FF FF 00 00 00 00 00 00 ; ,…….……
00003760h: 01 00 40 00 61 61 61 61 61 61 31 36 01 1C 04 00 ; ..@.aaaaaa16….
00003770h: 01 00 02 00 02 00 00 00 00 10 00 00 00 00 00 00 ; …………….
00003780h: 01 00 03 00 04 00 05 00 05 00 07 00 07 00 07 00 ; …………….
00003790h: C4 0D 23 39 4F 00 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
000037a0h: 61 61 61 61 61 61 33 C0 61 61 61 61 61 61 34 00 ; aaaaaa3繿aaaaa4.
000037b0h: 0B 05 01 00 04 00 01 00 F7 54 01 01 01 5A 1C C0 ; ……..鱐…Z.?
000037c0h: 01 00 00 00 01 02 76 41 0A 00 3C 00 1D 00 02 00 ; ……vA..<…..
000037d0h: 08 00 00 00 11 05 00 00 09 00 1F 00 73 09 00 00 ; …………s…
000037e0h: 44 01 C0 00 D7 04 06 00 00 00 00 00 00 00 00 00 ; D.??……….
000037f0h: 00 00 00 00 00 00 00 00 00 00 00 00 02 01 01 00 ; …………….
00003800h: 01 22 00 00 1C 00 00 00 EA 01 00 00 84 80 1C 74 ; .”……?..剙.t
00003810h: 81 5A 1C C0 01 00 00 00 01 02 01 00 01 5A 1C C0 ; 乑.?……..Z.?
00003820h: F7 54 01 01 F2 54 01 01 FA 12 05 01 02 00 00 00 ; 鱐..騎..?……
00003830h: 2C 02 00 00 00 00 05 01 01 00 00 00 00 35 34 34 ; ,…………544
00003840h: 01 00 41 43 61 61 61 61 61 61 31 31 05 14 00 00 ; ..ACaaaaaa11….
00003850h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 02 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，比对的过程已经详细描述过两次，这里不再赘述。
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现能搜到一处，为如下所示：
00003790h: C4 0D 23 39 4F 00 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现能搜索到五处，分别为如下所示：
00003760h: 01 00 40 00 61 61 61 61 61 61 31 36 01 1C 04 00 ; ..@.aaaaaa16….
00003790h: C4 0D 23 39 4F 00 00 00 61 61 61 61 61 61 32 00 ; ?#9O…aaaaaa2.
000037a0h: 61 61 61 61 61 61 33 C0 61 61 61 61 61 61 34 00 ; aaaaaa3繿aaaaa4.
00003840h: 01 00 41 43 61 61 61 61 61 61 31 31 05 14 00 00 ; ..ACaaaaaa11….
所以这里可以确认当全库supplemental logging以minimal模式开启且目标表没有主键，且目标表单独开启主键模式的supplemental logging的情况下Oracle在redo中会额外的记录目标表所有的列，也就是说此时redo log中的supplemental log中会额外的记录目标表所有列的值。
最后我们测试一下当全库supplemental logging关闭但单表的supplemental logging以主键模式开启的情况：
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SCOTT SYS_C0017190 T PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SQL> alter database drop supplemental log data;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
NO NO NO NO NO
这里我主要想测一下在全库supplemental logging关闭的情况下，对单表的supplemental logging是否可以开启：
SQL> alter table t drop supplemental log data (primary key) columns;
Table altered
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SQL> alter table t add supplemental log data (primary key) columns;
Table altered
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SCOTT SYS_C0017191 T PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> alter table t add constraint pk_t primary key (id);
Table altered
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
3
SQL> select member from v$logfile where group#=3;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518054189
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518054200
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG’ scn min 7518054189 scn max 7518054200;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_5260.trc中关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001ec.00000016.0010 LEN: 0x0224 VLD: 0x0d
SCN: 0x0001.c01c6731 SUBSCN: 1 09/02/2012 00:48:47
CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c66cb SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0009.019.00000976 uba: 0x00c00705.04d8.0c
Block cleanout record, scn: 0x0001.c01c672f ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c66cb
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 48
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #2 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0001.c01c6701 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0019 sqn: 0x00000976 flg: 0x0012 siz: 160 fbi: 0
uba: 0x00c00705.04d8.0c pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0001.c01c6731 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0019 sqn: 0x00000976 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00705.04d8.0c ext: 2 spc: 6622 fbi: 0
CHANGE #4 TYP:0 CLS:34 AFN:3 DBA:0x00c00705 OBJ:4294967295 SCN:0x0001.c01c6701 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 160 spc: 6784 flg: 0x0012 seq: 0x04d8 rec: 0x0c
xid: 0x0009.019.00000976
ktubl redo: slt: 25 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00705.04d8.09
prev ctl max cmt scn: 0x0001.c01c6210 prev tx cmt scn: 0x0001.c01c6228
txn start scn: 0x0001.c01c6713 logon user: 84 prev brb: 12584707 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0009.01f.00000973 uba: 0x00c00144.04d7.06
flg: C— lkc: 0 scn: 0x0001.c01c5a82
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 48
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
从上述trace内容中我们可以看到，当把全库supplemental loggging禁掉后（此时IMU会被开启）且目标表t有主键，且目标表单独开启主键模式的supplemental logging的情况下，上述update操作所产生的redo dump中并没有额外记录主键列的值。上述dump内容中ktudb redo size的值已经从之前的244变成了160，且描述undo的redo的位置又跑到描述redo的redo的位置下面去了，这显然说明supplemental logging在这种情况下是没有生效的。
我们再用UltraEdit看一下上述描述undo的redo记录：
00002d60h: 6E 3C 42 50 05 01 22 00 03 00 FF FF 05 07 C0 00 ; n<BP..”…..?
00002d70h: 01 67 1C C0 01 00 AF 33 03 00 FF FF 0E 00 14 00 ; .g.?.?..….
00002d80h: 4C 00 1C 00 1D 00 02 00 07 00 00 00 A0 00 80 1A ; L………..?€.
00002d90h: 12 00 C0 00 09 00 19 00 76 09 00 00 D8 04 0C 00 ; ..?….v…?..
00002da0h: 76 41 01 00 76 41 01 00 04 00 00 00 00 00 00 00 ; vA..vA……….
00002db0h: 0B 01 19 00 08 0C 01 00 00 00 00 00 05 07 C0 00 ; …………..?
00002dc0h: D8 04 09 00 10 62 1C C0 01 00 11 00 28 62 1C C0 ; ?…b.?…(b.?
00002dd0h: 01 00 00 00 00 00 00 00 13 67 1C C0 01 00 0D 00 ; ………g.?…
00002de0h: 03 07 C0 00 00 00 00 00 54 00 00 00 04 05 C0 00 ; ..?….T…..?
00002df0h: 09 00 1F 00 73 09 00 00 44 01 C0 00 D7 04 06 00 ; ….s…D.??..
00002e00h: 01 22 00 00 17 00 00 00 EC 01 00 00 44 80 74 3B ; .”……?..D€t;
00002e10h: 00 80 01 00 82 5A 1C C0 F7 54 01 01 F2 54 01 01 ; .€..俍.厉T..騎..
00002e20h: FA 12 05 01 02 00 00 00 2C 00 30 00 00 00 05 01 ; ?……,.0…..
00002e30h: FF FF 00 00 00 00 1A 00 01 00 00 00 61 61 61 61 ; ……….aaaa
00002e40h: 61 61 31 0E 00 00 00 00 00 00 00 00 00 00 00 00 ; aa1………….
00002e50h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; …………….
上述描述undo的redo记录可以和上述trace记录中描述undo的dump后的redo对上，比对的过程已经详细描述过两次，这里不再赘述。
这里以主键12345678所对应的c4 0d 23 39 4f去UltraEdit中搜索，发现搜不到。
这里以aaaaaa所对应的61 61 61 61 61 61去UltraEdit中搜索，发现只能搜索到两处，分别为如下所示：
00002cd0h: 00 01 C8 05 01 00 00 00 61 61 61 61 61 61 31 31 ; ..?….aaaaaa11
00002e30h: FF FF 00 00 00 00 1A 00 01 00 00 00 61 61 61 61 ; ……….aaaa
00002e40h: 61 61 31 0E 00 00 00 00 00 00 00 00 00 00 00 00 ; aa1………….
所以这里可以确认当把全库supplemental log禁掉后（此时IMU会被开启）且目标表t有主键，且目标表单独开启主键模式的supplemental logging的情况下，Oracle在redo中不会额外的记录主键列和其他列的值，相当于supplemental logging在这种情况下是没有生效的。
Eygle提到说虽然supplemental logging默认情况下在redo dump中是看不到的，但通过设置1354和1348事件后再dump就可以看到了，我一查还真是这样的，Oracle里太多后门了：
For 10g+ with SUPPLEMENTAL DATA logged:
By default supplementally column data is not dumped
by ALTER SYSTEM DUMP LOGFILE. To enable dumping of
supplemental information in the redo set the following
events BEFORE issuing the DUMP LOGFILE command:
alter session set events = ‘1354 trace name context forever, level 32768’;
alter session set events = ‘1348 trace name context forever, level 1032’ ;
现在我仅仅开启全库minimal方式的supplemental logging后设置1354和1348事件后再次尝试同样的操作（表T现在有主键）：
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
NO NO NO NO NO
SQL> alter table t drop supplemental log data (primary key) columns;
Table altered
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SQL> alter database add supplemental log data;
Database altered
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
2
SQL> select member from v$logfile where group#=2;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518063438
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518063454
SQL> oradebug setmypid
已处理的语句
SQL> oradebug unlimit
已处理的语句
SQL> alter session set events = ‘1354 trace name context forever, level 32768’;
会话已更改。
SQL> alter session set events = ‘1348 trace name context forever, level 1032’;
会话已更改。
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO02.LOG’ scn min 7518063438 scn max 7518063454;
系统已更改。
SQL> oradebug tracefile_name
c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2524.trc
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2524.trc关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001ee.000006bc.0010 LEN: 0x0228 VLD: 0x05
SCN: 0x0001.c01c8b52 SUBSCN: 1 09/02/2012 11:02:19
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0001.c01c8b39 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0018 sqn: 0x00000980 flg: 0x0012 siz: 184 fbi: 0
uba: 0x00c0051f.04db.03 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:3 DBA:0x00c0051f OBJ:4294967295 SCN:0x0001.c01c8b37 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 184 spc: 7950 flg: 0x0012 seq: 0x04db rec: 0x03
xid: 0x0009.018.00000980
ktubl redo: slt: 24 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0051d.04db.01
prev ctl max cmt scn: 0x0001.c01c86c2 prev tx cmt scn: 0x0001.c01c86cf
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12584800 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0009.019.00000976 uba: 0x00c00705.04d8.0c
flg: C— lkc: 0 scn: 0x0001.c01c6731
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
LOGMINER DATA 10i:
opcode: UPDATE
Number of columns supplementally logged: 0 Flag: SE kdogspare1: 0x1000 [ NSRCI] Objv#: 1
segcol# in Undo starting from 2
segcol# in Redo starting from 2
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c89b1 SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0009.018.00000980 uba: 0x00c0051f.04db.03
Block cleanout record, scn: 0x0001.c01c8b52 ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c89b1
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 22
serial number = 15
transaction name =
version 186646784
audit sessionid 665951
Client Id =
从上面trace内容确实可以看到在仅仅开启minimal模式的全库supplemental logging的情况下，Oracle并没有在redo中额外的记录主键和其他列的值：
LOGMINER DATA 10i:
opcode: UPDATE
Number of columns supplementally logged: 0 Flag: SE kdogspare1: 0x1000 [ NSRCI] Objv#: 1
segcol# in Undo starting from 2
segcol# in Redo starting from 2
而且这里我们可以更进一步的证实我之前关于supplemental log记录的位置的结论：supplemental loggging实际上是额外的记录在描述undo的redo所在的change vector中。
现在我保持全库minimal方式的supplemental logging并对表T单独开启主键模式的supplemental logging并再次尝试同样的操作（表T现在有主键）：
SQL> update t set c1=’aaaaaa1′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa1 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,
supplemental_log_data_fk,supplemental_log_data_all from v$database;
SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
————————- ———————— ———————— ———————— ————————-
YES NO NO NO NO
SQL> alter table t add supplemental log data (primary key) columns;
Table altered
SQL> select * from dba_log_groups;
OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED
—————————— —————————— —————————— ——————- ———– ————–
SYS ENC$_LOG_GRP ENC$ USER LOG GROUP ALWAYS USER NAME
SCOTT SYS_C0017197 T PRIMARY KEY LOGGING ALWAYS GENERATED NAME
SQL> alter system switch logfile;
System altered
SQL> select group# from v$log where status=’CURRENT’;
GROUP#
———-
3
SQL> select member from v$logfile where group#=3;
MEMBER
——————————————————————————–
C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518064825
SQL> update t set c1=’aaaaaa11′ where id=12345678;
1 row updated
SQL> commit;
Commit complete
SQL> select * from t;
ID C1 C2 C3 C4
———- ———- ———- ———- ———-
12345678 aaaaaa11 aaaaaa2 aaaaaa3 aaaaaa4
SQL> select dbms_flashback.get_system_change_number() from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHAN
——————————
7518064833
SQL> alter system dump logfile ‘C:\APP\CUIHUA\ORADATA\CUIHUA112\REDO03.LOG’ scn min 7518064825 scn max 7518064833;
系统已更改。
如下是c:\app\cuihua\diag\rdbms\cuihua112\cuihua112\trace\cuihua112_ora_2524.trc关于上述update操作的redo记录：
REDO RECORD – Thread:1 RBA: 0x0001ef.00000008.0010 LEN: 0x0240 VLD: 0x05
SCN: 0x0001.c01c90bb SUBSCN: 1 09/02/2012 11:14:44
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0001.c01c908a SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0003 sqn: 0x00000743 flg: 0x0012 siz: 204 fbi: 0
uba: 0x00c01f1d.0324.19 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c01f1d OBJ:4294967295 SCN:0x0001.c01c9089 SEQ:9 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 204 spc: 4530 flg: 0x0012 seq: 0x0324 rec: 0x19
xid: 0x0001.003.00000743
ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 82294 objd: 82294 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c01f1c.0324.2d
prev ctl max cmt scn: 0x0001.c01c8c92 prev tx cmt scn: 0x0001.c01c8cbe
txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12590872 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0009.018.00000980 uba: 0x00c0051f.04db.03
flg: C— lkc: 0 scn: 0x0001.c01c8b53
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 5 nnew: 1 size: -1
col 1: [ 7] 61 61 61 61 61 61 31
LOGMINER DATA 10i:
opcode: UPDATE
Number of columns supplementally logged: 1 Flag: SE kdogspare1: 0x1000 [ NSRCI] Objv#: 1
segcol# in Undo starting from 2
segcol# in Redo starting from 2
Supplemental logging:
col 0: segcol#: 1 len [ 5] c4 0d 23 39 4f
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x010154f7 OBJ:82294 SCN:0x0001.c01c9022 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0001.003.00000743 uba: 0x00c01f1d.0324.19
Block cleanout record, scn: 0x0001.c01c90bb ver: 0x01 opt: 0x02, entries follow…
itli: 1 flg: 2 scn: 0x0001.c01c9022
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010154f7 hdba: 0x010154f2
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 5 nnew: 1 size: 1
col 1: [ 8] 61 61 61 61 61 61 31 31
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 22
serial number = 15
transaction name =
version 186646784
audit sessionid 665951
Client Id =
从上面的trace文件中确实可以看到主键12345678（对应的是c4 0d 23 39 4f）被额外的记录在supplemental log中了，如下所示：
LOGMINER DATA 10i:
opcode: UPDATE
Number of columns supplementally logged: 1 Flag: SE kdogspare1: 0x1000 [ NSRCI] Objv#: 1
segcol# in Undo starting from 2
segcol# in Redo starting from 2
Supplemental logging:
col 0: segcol#: 1 len [ 5] c4 0d 23 39 4f
这和我们之前用UltraEdit分析的结论一致。
好了，我们所有的准备工作都做好了，现在我们来回答我在本文开头提的那几个问题：
1、supplemental logging在redo log里到底会额外的写入什么内容？
答：supplemental logging会在redo log中描述undo的change vector中额外的写入主键列或者所有列或者你指定列的值。什么情况下会写什么我已经在问题2中详细总结了。
2、全库级别的supplemental logging和表级别的supplemental logging的各种组合的效果是什么？
答：各种组合的效果总结如下：
（1）、当全库supplemental logging以minimal模式开启时（此时IMU会被禁掉）：
(a) 如果不对目标表单独开启supplemental logging，则Oracle在redo中并不会额外的记录主键列和其他列的值；
(b) 如果对目标表单独开启主键模式的supplemental logging,则又细分为如下两种情况：
(b1)如果目标表有主键，则Oracle会在redo中额外的记录主键列的值；
(b2)如果目标表没有主键，则Oracle会在redo中额外的记录所有列的值；
（2）、当全库supplemental logging以主键模式开启（此时IMU会被禁掉）且目标表并没有单独开启任何模式的supplemental logging时：
(a) 如果目标表有主键，则Oracle会在redo中额外的记录主键列的值；
(b) 如果目标表没有主键，则Oracle会在redo中额外的记录所有列的值；
（3）、当把全库supplemental log禁掉后（此时IMU会被开启）,不管目标表是否单独开启主键模式的supplemental logging，Oracle在redo中都并不会额外的记录主键列和其他列的值。
3、为什么supplemental logging一开启就会禁掉IMU（In Memory Undo）？
答：supplemental logging在Oracle数据库中出现的比IMU早，IMU是10g后才引入。由刚才的分析我们知道如果开启了IMU，则描述redo的redo和描述undo的redo在从private redo log buffer和in memory undo pool写入public redo log buffer时存在一个简单合并的过程，这就可以解释为什么在开启了IMU的情况下，描述undo的redo在redo log中的位置会在描述redo的redo的位置的后面，而在通常情况下描述undo的redo在redo log中的位置会在描述redo的redo的位置的前面。如果比IMU更早出现在Oracle数据库中的supplemental logging的实现代码中是固定了要先在redo中写入描述undo的redo，再随后写入描述redo的redo，这就和IMU的简单的合并机制所导致的先写描述redo的redo再写描述undo的redo矛盾了，所以supplemental logging一开启就会禁掉IMU。这个我不确定，是猜的。
4、为什么logminer在10g中使用时必须要开启supplemental logging而在9i中则不需要？
答：9i中不存在IMU，所以开不开启supplemental logging并不会影响redo log中写入描述undo的redo和写入描述redo的redo的先后顺序，而10g中的IMU是会影响这个写入顺序的。如果logminer并没有对这种写入顺序的颠倒做特殊处理，还是在10g中维持和9i一样的解析redo log中change vector顺序的代码，那么当然在10g中不开启supplemental logging（意味着IMU会被开启）就不能正确的解析SQL了。这个我不确定，是猜的。
5、为什么GoldenGate在同步数据时会要求不仅要在全库级别开启supplemental logging，还会额外的要求要在待同步的目标表上也要开启表级别的supplemental logging？
答：因为从之前的分析我们知道，如果仅仅在全库级别开启minimal模式的supplemental logging而不对待同步的目标表单独开启主键模式的supplemental logging的话Oracle并不会在redo log中额外的记录主键列的值，这样当然GoldenGate就不能正确的同步了。
好文。
这里有关于IMU的详细描述：
http://www.patentstorm.us/patents/6981004/description.html
Minimal supplemental logging ensures that Logminer (and any products building on Logminer technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables.
看了崔大师文章真的是受益匪浅，十分感谢。就你文中提到的第四个问题我在oracle官方文档中看到如下一段话，不知道是不是这个原因“In Oracle Database release 9.0.1, minimal supplemental logging was the default behavior in LogMiner. In release 9.2 and later, the default is no supplemental logging. Supplemental logging must be specifically enabled.”
感谢，文章解惑。