深入SUPPLEMENTAL LOGGING

早就想写一篇关于Oraclesupplemental logging的文章了,姗姗妈随她们单位出去旅游了,姗姗也不用我看,我终于有了写这篇文章的时间。

Oracle中的supplemental logging分为两种一种是全库级别的supplemental logging一种是表级别的supplemental logging。它们的基本定义和类型我这里不再详述,有兴趣的朋友可以去查Oracle的文档。

这篇文章里我主要试图回答如下问题:

1supplemental loggingredo log里到底会额外的写入什么内容?

2全库级别的supplemental logging和表级别的supplemental logging的各种组合的效果是什么?

3、为什么supplemental logging一开启就会禁掉IMUIn Memory Undo)?

4、为什么logminer10g中使用时必须要开启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内容中我们可以看到:

1supplemental log未开启的情况下,即在IMU的情况下redo中依然会有描述undoredo

2因为开启了IMU,所以描述redoredo和描述undoredo在从private redo log bufferin memory undo pool写入public redo log buffer时存在一个简单合并的过程,这就可以解释为什么在开启了IMU的情况下,描述undoredoredo log中的位置会在描述redoredo的位置的后面,而在通常情况下描述undoredoredo log中的位置会在描述redoredo的位置的前面。

 

我们再用UltraEdit看一下上述描述undoredo记录:

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 ; …………….

 

上述描述undoredo记录可以和上述trace记录中描述undodump后的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 3100001c20h

 

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现搜不到。

这里以aaaaaa所对应的61 61 61 61 61 61UltraEdit中搜索,发现只能搜索到两处,分别为如下所示:

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未开启且目标表有主键的情况下Oracleredo中不会记录主键,并且只会记录被修改的列的当前值和前镜像。

 

 

现在我们把表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 loggingminimum开启后(此时IMU会被禁掉)且目标表t有主键的情况下,上述update操作所产生的redo dump中并没有记录主键列的值。

但上述dump内容中ktudb redo size的值已经从之前的136变为了184

同样的update操作,为啥描述undoredoktudb redosize变大了?

 

只有一种可能,就是redo dump虽然看不到supplemental loggging的内容,但Oracle实际上已经在ktudb redo中写入了supplemental logsupplemental loggging实际上是额外的记录在描述undoredo所在的change vector

 

我们再用UltraEdit看一下上述描述undoredo记录:

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

 

上述描述undoredo记录可以和上述trace记录中描述undodump后的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 3100002350h

aaaaaa11所对应的61 61 61 61 61 61 31 31000023f0h

 

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现搜不到。

这里以aaaaaa所对应的61 61 61 61 61 61UltraEdit中搜索,发现只能搜索到两处,分别为如下所示:

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模式在全库开启且目标表有主键的情况下Oracleredo中不会记录主键列的值,并且只会记录被修改的列的当前值和前镜像。

 

 

现在我们把表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,这意味着Oracleredo log中记录的supplemental log中额外的写入了东西。

 

我们再用UltraEdit看一下上述描述undoredo记录:

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 ; …………….

 

上述描述undoredo记录可以和上述trace记录中描述undodump后的redo对上,比对的过程已经详细描述过两次,这里不再赘述。

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现能搜到一处,为如下所示:

00002180h: 05 00 80 00 C4 0D 23 39 4F 00 01 06 0B 05 01 00 ; ..€.?#9O…….

 

这里以aaaaaa所对应的61 61 61 61 61 61UltraEdit中搜索,发现只能搜索到两处,分别为如下所示:

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在全库以主键模式开启且目标表有主键的情况下Oracleredo中只会额外的记录主键列的值且主键的记录位置supplemental logging的记录位置)是在前镜像61 61 61 61 61 61 31和当前值61 61 61 61 61 61 31 31之间,这也符合我之前提到的:supplemental loggging实际上是额外的记录在描述undoredo所在的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看一下上述描述undoredo记录:

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 ; …………….

上述描述undoredo记录可以和上述trace记录中描述undodump后的redo对上,比对的过程已经详细描述过两次,这里不再赘述。

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现能搜到一处,为如下所示:

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 61UltraEdit中搜索,发现能搜索到五处,分别为如下所示:

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在全库以主键模式开启且目标表没有主键的情况下Oracleredo中记录目标表所有的列,也就是说此时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。这意味着Oracleredo log中记录的supplemental log中减少了写入的内容。

 

 

我们再用UltraEdit看一下上述描述undoredo记录:

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 ; …………….

上述描述undoredo记录可以和上述trace记录中描述undodump后的redo对上,比对的过程已经详细描述过两次,这里不再赘述。

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现能搜到一处,为如下所示:

00002380h: 05 00 00 00 C4 0D 23 39 4F 00 00 00 0B 05 01 00 ; ….?#9O…….

 

这里以aaaaaa所对应的61 61 61 61 61 61UltraEdit中搜索,发现只能搜索到两处,分别为如下所示:

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的情况下Oracleredo中只会额外的记录主键列的值且主键的记录位置supplemental logging的记录位置)是在前镜像61 61 61 61 61 61 31和当前值61 61 61 61 61 61 31 31之间,这也符合我之前提到的:supplemental loggging实际上是额外的记录在描述undoredo所在的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看一下上述描述undoredo记录:

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 ; …………….

 

上述描述undoredo记录可以和上述trace记录中描述undodump后的redo对上,比对的过程已经详细描述过两次,这里不再赘述。

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现能搜到一处,为如下所示:

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 61UltraEdit中搜索,发现能搜索到五处,分别为如下所示:

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 loggingminimal模式开启且目标表没有主键且目标表单独开启主键模式的supplemental logging的情况下Oracleredo中会额外的记录目标表所有的列,也就是说此时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,且描述undoredo的位置又跑到描述redoredo的位置下面去了,这显然说明supplemental logging在这种情况下是没有生效的。

 

我们再用UltraEdit看一下上述描述undoredo记录:

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 ; …………….

 

上述描述undoredo记录可以和上述trace记录中描述undodump后的redo对上,比对的过程已经详细描述过两次,这里不再赘述。

这里以主键12345678所对应的c4 0d 23 39 4fUltraEdit中搜索,发现搜不到。

 

这里以aaaaaa所对应的61 61 61 61 61 61UltraEdit中搜索,发现只能搜索到两处,分别为如下所示:

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的情况下Oracleredo中不会额外的记录主键列和其他列的值相当于supplemental logging在这种情况下是没有生效的。

 

 

Eygle提到说虽然supplemental logging默认情况下在redo dump中是看不到的,但通过设置13541348事件后再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后设置13541348事件后再次尝试同样的操作(表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实际上是额外的记录在描述undoredo所在的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分析的结论一致。

 

好了,我们所有的准备工作都做好了,现在我们来回答我在本文开头提的那几个问题:

1supplemental loggingredo log里到底会额外的写入什么内容?

答:supplemental logging会在redo log中描述undochange vector中额外的写入主键列或者所有列或者你指定列的值。什么情况下会写什么我已经在问题2中详细总结了。

 

2、全库级别的supplemental logging和表级别的supplemental logging的各种组合的效果是什么?

答:各种组合的效果总结如下:

1)、当全库supplemental loggingminimal模式开启时(此时IMU会被禁掉):

   (a) 如果不对目标表单独开启supplemental logging,则Oracleredo中并不会额外的记录主键列和其他列的值;

   (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 loggingOracleredo中都并不会额外的记录主键列和其他列的值。

 

3、为什么supplemental logging一开启就会禁掉IMUIn Memory Undo)?

答:supplemental loggingOracle数据库中出现的比IMU早,IMU10g后才引入。由刚才的分析我们知道如果开启了IMU,则描述redoredo和描述undoredo在从private redo log bufferin memory undo pool写入public redo log buffer时存在一个简单合并的过程,这就可以解释为什么在开启了IMU的情况下,描述undoredoredo log中的位置会在描述redoredo的位置的后面,而在通常情况下描述undoredoredo log中的位置会在描述redoredo的位置的前面。如果比IMU更早出现在Oracle数据库中的supplemental logging的实现代码中是固定了要先在redo中写入描述undoredo,再随后写入描述redoredo,这就和IMU的简单的合并机制所导致的先写描述redoredo再写描述undoredo矛盾了,所以supplemental logging一开启就会禁掉IMU这个我不确定,是猜的。

 

4、为什么logminer10g中使用时必须要开启supplemental logging而在9i中则不需要?

答:9i中不存在IMU,所以开不开启supplemental logging并不会影响redo log中写入描述undoredo和写入描述redoredo的先后顺序,而10g中的IMU是会影响这个写入顺序的。如果logminer并没有对这种写入顺序的颠倒做特殊处理,还是在10g中维持和9i一样的解析redo logchange vector顺序的代码,那么当然在10g中不开启supplemental logging(意味着IMU会被开启)就不能正确的解析SQL了。这个我不确定,是猜的。

 

5、为什么GoldenGate在同步数据时会要求不仅要在全库级别开启supplemental logging,还会额外的要求要在待同步的目标表上也要开启表级别的supplemental logging

答:因为从之前的分析我们知道,如果仅仅在全库级别开启minimal模式的supplemental logging而不对待同步的目标表单独开启主键模式的supplemental logging的话Oracle并不会在redo log中额外的记录主键列的值,这样当然GoldenGate就不能正确的同步了。

 


3 Comments on “深入SUPPLEMENTAL LOGGING”

  1. Cui Hua says:

    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.

  2. wxjzqymtl says:

    看了崔大师文章真的是受益匪浅,十分感谢。就你文中提到的第四个问题我在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.”


Leave a Reply

Your email address will not be published. Required fields are marked *