A-A+
表级日志模式异同对比
表级日志模式对比
该文档测试数据库开启表级日志模式和没有开启时，在对表update操作时产生的redo日志记录的不同
背景：由于一个同事在之前配置ogg时没有开启源端表级日志模式，导致从源端传过来的和update相关操作的记录源端和对端不一致。
下边是OGG对数据库级日志模式和表级日志模式的说明
首先是数据库级的说明：
When replicating from Oracle databases, you need to enable database-level supplemental logging on the source database. Remember that if you’re using bidirectional replication, you need to enable supplemental logging on both the source and the target databases. The supplemental logging is required to ensure that Oracle adds additional information to the redo logs that is required by GoldenGate. For Oracle 9i, SUPPLEMENTAL_LOG_DATA_MIN must be YES. For Oracle 10g and later, SUPPLEMENTAL_LOG_DATA must be YES or IMPLICIT. Now that you’ve verified that database-level supplemental logging isn’t currently enabled, you can enter the commands to make it enabled.
下边是关于表级日志模式的说明：
Normally, databases such as Oracle, DB2, and SQL Server don’t log enough data in the transaction logs for GoldenGate to be able to successfully replicate the change to the target database. GoldenGate requires the key values in addition to the changed data in order for the GoldenGate Replicat to apply the changed data to the target database. Adding supplemental logging to the source database tables ensures that the database logs the key values that GoldenGate needs in order to properly apply the updates on the target database. You can use the GoldenGate ADD TRANDATA command to force the database to log primary-key columns for all updates on your source database. You don’t need to enable supplemental logging on the target database for one-way replication.
参考文档：
临危不惧：oracle 11g 数据库恢复技术 包光磊
Export Oracle GoldenGate
下边是详细的比对过程：
首先是没有开启表级日志记录的情况：
SQL> sho user
USER is "SYS"
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_PL,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,LOG_MODE from v$database;
SUPPLEME SUP SUP SUP SUP SUP LOG_MODE
-------- --- --- --- --- --- ------------
NO NO NO NO NO NO ARCHIVELOG
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> col member for a50
SQL> select a.GROUP#,THREAD#,SEQUENCE#,a.STATUS,b.MEMBER from v$log a,v$logfile b where a.GROUP# =b.GROUP# ;
GROUP# THREAD# SEQUENCE# STATUS MEMBER
---------- ---------- ---------- ---------------- --------------------------------------------------
1 1 97 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo01.log
2 1 98 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo02.log
3 1 99 CURRENT /u01/app/oracle/oradata/ora11gr2/redo03.log
SQL> sho user
USER is "YALLONKING"
SQL> select rowid,t.* from yallonking t;
ROWID ID NAME MY_DATE AGE
------------------ ---------- -------------------- --------- ----------
AAARciAAFAAAACHAAA 1 yallonking 22-JUL-13 26
AAARciAAFAAAACHAAB 2 oraking 22-JUL-13 26
AAARciAAFAAAACHAAC 3 xiaolong 22-JUL-13 26
SQL> update yallonking set age=25 where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> sho user
USER is "SYS"
SQL> select a.GROUP#,THREAD#,SEQUENCE#,a.STATUS,b.MEMBER from v$log a,v$logfile b where a.GROUP# =b.GROUP# ;
GROUP# THREAD# SEQUENCE# STATUS MEMBER
---------- ---------- ---------- ---------------- --------------------------------------------------
1 1 97 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo01.log
2 1 98 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo02.log
3 1 99 CURRENT /u01/app/oracle/oradata/ora11gr2/redo03.log
SQL> select get_rowid('AAARciAAFAAAACHAAA') from dual;
GET_ROWID('AAARCIAAFAAAACHAAA')
--------------------------------------------------------------------------------
Row_id type is :1
Object# is :71458
Relative_fno is :5
Block number is :135
Row number is :0
SQL> alter session set tracefile_identifier='wyl';
Session altered.
SQL> alter system dump logfile '/u01/app/oracle/oradata/ora11gr2/redo03.log' dba min 5 135 dba max 5 300;
System altered.
以下为完全的trace文件
[oracle@rhl6 trace]$ pwd;cat ora11gr2_ora_2114_wyl.trc
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace
Trace file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2114_wyl.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rhl6.0x64
Release: 2.6.32-71.el6.x86_64
Version: #1 SMP Wed Sep 1 01:33:01 EDT 2010
Machine: x86_64
VM name: VMWare Version: 6
Instance name: ora11gr2
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 2114, image: oracle@rhl6.0x64 (TNS V1-V3)
*** 2013-08-28 13:42:11.532
*** SESSION ID:(41.25) 2013-08-28 13:42:11.532
*** CLIENT ID:() 2013-08-28 13:42:11.532
*** SERVICE NAME:(SYS$USERS) 2013-08-28 13:42:11.532
*** MODULE NAME:(sqlplus@rhl6.0x64 (TNS V1-V3)) 2013-08-28 13:42:11.532
*** ACTION NAME:() 2013-08-28 13:42:11.532
*** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2114.trc ***
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/ora11gr2/redo03.log'
Opcodes *.*
DBAs: (file # 5, block # 135) thru (file # 5, block # 300)
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=119524391=0x71fcc27, Db Name='ORA11GR2'
Activation ID=119541799=0x7201027
Control Seq=992=0x3e0, File size=102400=0x19000
File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000099, SCN 0x0000000c03e2-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000063 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x2fdc16a7 scn: 0x0000.00000001 (1)
prev resetlogs count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.000c03e2 (787426) 08/28/2013 13:29:28
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00000001 (1) 12/25/2012 10:28:49
Thread closed scn: 0x0000.000c03e2 (787426) 08/28/2013 13:29:28
Disk cksum: 0x85f2 Calc cksum: 0x85f2
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 6ba8b4cb653aab92bc9bcc4bcff4c7
redo log key flag is 5
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x000063.00000ac8.0010 LEN: 0x0278 VLD: 0x0d
SCN: 0x0000.000c06de SUBSCN: 1 08/28/2013 13:37:01
(LWN RBA: 0x000063.00000ac8.0010 LEN: 0002 NST: 0001 SCN: 0x0000.000c06dc)
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x01400087 OBJ:71458 SCN:0x0000.000c03a8 SEQ:4 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0006.010.00000367 uba: 0x00c000df.00b0.1f
Block cleanout record, scn: 0x0000.000c06dd ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0000.000c03a8
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 1 ckix: 9 //第一行[slot: 0(0x0)]
ncol: 4 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
vect = 22
col 3: [ 2] c1 1a //修改后值:第4列的值，长度为2：c11a(十进制：25)
CHANGE #2 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.000c06c0 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0010 sqn: 0x00000367 flg: 0x0012 siz: 188 fbi: 0
uba: 0x00c000df.00b0.1f pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.000c06de SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0010 sqn: 0x00000367 srt: 0 sta: 9 flg: 0x12 ktucf redo: uba: 0x00c000df.00b0.1f ext: 1 spc: 3576 fbi: 0
CHANGE #4 TYP:0 CLS:28 AFN:3 DBA:0x00c000df OBJ:4294967295 SCN:0x0000.000c06bf SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 188 spc: 3766 flg: 0x0012 seq: 0x00b0 rec: 0x1f
xid: 0x0006.010.00000367
ktubl redo: slt: 16 rci: 0 opc: 11.1 [objn: 71458 objd: 71458 tsn: 5]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c000df.00b0.1e
prev ctl max cmt scn: 0x0000.000c04c2 prev tx cmt scn: 0x0000.000c04d6
txn start scn: 0x0000.000c03a8 logon user: 80 prev brb: 12583134 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0005.015.000002a9 uba: 0x00c0342c.0097.25
flg: C--- lkc: 0 scn: 0x0000.000b9935
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 9 //第一行[slot: 0(0x0)]
ncol: 4 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400087 hdba: 0x01400082
itli: 1 ispac: 0 maxfr: 4858
vect = 22
col 3: [ 2] c1 1b //修改前值:第4列的值,长度为2：c11a(十进制：26)
REDO RECORD - Thread:1 RBA: 0x000063.00000aed.0010 LEN: 0x0070 VLD: 0x06
SCN: 0x0000.000c0753 SUBSCN: 1 08/28/2013 13:42:04
(LWN RBA: 0x000063.00000aed.0010 LEN: 0001 NST: 0001 SCN: 0x0000.000c0753)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 5 rdba: 0x01400087 BFT:(1024,20971655) non-BFT:(5,135)
scn: 0x0000.000c06de seq: 0x02 flg:0x06
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 1398Kb in 0.01s => 136.52 Mb/sec
Total redo bytes: 2047Kb Longest record: 12Kb, moves: 1/2348 moved: 0Mb (0%)
Longest LWN: 444Kb, reads: 218
Last redo scn: 0x0000.000c0753 (788307)
Change vector header moves = 310/4558 (6%)
----------------------------------------------
下边是开启表级日志记录的模式：
SQL> sho user
USER is "SYS"
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE AGE
---------- -------------------- --------- ----------
1 yallonking 22-JUL-13 25
2 oraking 22-JUL-13 26
3 xiaolong 22-JUL-13 26
SQL> alter system archive log current;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter table yallonking.yallonking add supplemental log data (all) 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
YALLONKING SYS_C008080 YALLONKING ALL COLUMN LOGGING ALWAYS GENERATED NAME
SQL> sho user
USER is "YALLONKING"
SQL> select * from yallonking;
ID NAME MY_DATE AGE
---------- -------------------- --------- ----------
1 yallonking 22-JUL-13 25
2 oraking 22-JUL-13 26
3 xiaolong 22-JUL-13 26
SQL> update yallonking set age=28 where id=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select rowid,t.* from yallonking t;
ROWID ID NAME MY_DATE AGE
------------------ ---------- -------------------- --------- ----------
AAARciAAFAAAACHAAA 1 yallonking 22-JUL-13 25
AAARciAAFAAAACHAAB 2 oraking 22-JUL-13 26
AAARciAAFAAAACHAAC 3 xiaolong 22-JUL-13 28
SQL> sho user
USER is "SYS"
SQL> select a.GROUP#,THREAD#,SEQUENCE#,a.STATUS,b.MEMBER from v$log a,v$logfile b where a.GROUP# =b.GROUP# ;
GROUP# THREAD# SEQUENCE# STATUS MEMBER
---------- ---------- ---------- ---------------- --------------------------------------------------
1 1 100 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo01.log
2 1 101 CURRENT /u01/app/oracle/oradata/ora11gr2/redo02.log
3 1 99 INACTIVE /u01/app/oracle/oradata/ora11gr2/redo03.log
SQL> select get_rowid('AAARciAAFAAAACHAAC') from dual;
GET_ROWID('AAARCIAAFAAAACHAAC')
--------------------------------------------------------------------------------
Row_id type is :1
Object# is :71458
Relative_fno is :5
Block number is :135
Row number is :2
SQL> alter session set tracefile_identifier='wyl2';
Session altered.
SQL> alter system dump logfile '/u01/app/oracle/oradata/ora11gr2/redo02.log' dba min 5 135 dba max 5 300;
System altered.
以下为trace文件
[oracle@rhl6 trace]$ pwd;cat ora11gr2_ora_2373_wyl2.trc
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace
Trace file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2373_wyl2.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rhl6.0x64
Release: 2.6.32-71.el6.x86_64
Version: #1 SMP Wed Sep 1 01:33:01 EDT 2010
Machine: x86_64
VM name: VMWare Version: 6
Instance name: ora11gr2
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 2373, image: oracle@rhl6.0x64 (TNS V1-V3)
*** 2013-08-28 14:35:36.072
*** SESSION ID:(33.35) 2013-08-28 14:35:36.072
*** CLIENT ID:() 2013-08-28 14:35:36.072
*** SERVICE NAME:(SYS$USERS) 2013-08-28 14:35:36.072
*** MODULE NAME:(sqlplus@rhl6.0x64 (TNS V1-V3)) 2013-08-28 14:35:36.072
*** ACTION NAME:() 2013-08-28 14:35:36.072
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/ora11gr2/redo02.log'
Opcodes *.*
DBAs: (file # 5, block # 135) thru (file # 5, block # 300)
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=119524391=0x71fcc27, Db Name='ORA11GR2'
Activation ID=119541799=0x7201027
Control Seq=1003=0x3eb, File size=102400=0x19000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000101, SCN 0x0000000c0bb3-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000065 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x2fdc16a7 scn: 0x0000.00000001 (1)
prev resetlogs count: 0x0 scn: 0x0000.00000000
Low scn: 0x0000.000c0bb3 (789427) 08/28/2013 14:18:33
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00000001 (1) 12/25/2012 10:28:49
Thread closed scn: 0x0000.000c0bb3 (789427) 08/28/2013 14:18:33
Disk cksum: 0x3c9 Calc cksum: 0x3c9
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
Zero blocks: 0
Format ID is 2
redo log key is 90877f587bf45259f1f7b6e97f6d48a
redo log key flag is 5
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x000065.000002ac.0010 LEN: 0x0298 VLD: 0x05
SCN: 0x0000.000c0ecc SUBSCN: 1 08/28/2013 14:32:29
(LWN RBA: 0x000065.000002ac.0010 LEN: 0002 NST: 0001 SCN: 0x0000.000c0ecb)
CHANGE #1 TYP:0 CLS:17 AFN:3 DBA:0x00c00080 OBJ:4294967295 SCN:0x0000.000c0e28 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0011 sqn: 0x000002be flg: 0x0012 siz: 228 fbi: 0
uba: 0x00c036f6.0092.37 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:18 AFN:3 DBA:0x00c036f6 OBJ:4294967295 SCN:0x0000.000c0e27 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 228 spc: 1890 flg: 0x0012 seq: 0x0092 rec: 0x37
xid: 0x0001.011.000002be
ktubl redo: slt: 17 rci: 0 opc: 11.1 [objn: 71458 objd: 71458 tsn: 5]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c036f6.0092.35
prev ctl max cmt scn: 0x0000.000c08ee prev tx cmt scn: 0x0000.000c08f8
txn start scn: 0xffff.ffffffff logon user: 80 prev brb: 12596981 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x000a.00d.000002a9 uba: 0x00c001cd.00ba.2d
flg: C--- lkc: 0 scn: 0x0000.000c03a8
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0 //第3行[slot: 2(0x2)]
ncol: 4 nnew: 1 size: 0
col 3: [ 2] c1 1b //修改前值：第4列：c11b(十进制的26)
CHANGE #3 TYP:2 CLS:1 AFN:5 DBA:0x01400087 OBJ:71458 SCN:0x0000.000c06de SEQ:2 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0001.011.000002be uba: 0x00c036f6.0092.37
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01400087 hdba: 0x01400082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 0 //第3行[slot: 2(0x2)]
ncol: 4 nnew: 1 size: 0
col 3: [ 2] c1 1d //修改后值：第4列：c11d(十进制的28)
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.19 ENC:0 //以下会记录比较详细的修改者的信息。
session number = 1
serial number = 21
current username = YALLONKING
login username = YALLONKING
client info =
OS username = oracle
Machine name = rhl6.0x64
OS terminal = pts/1
OS process id = 1769
OS program name = sqlplus@rhl6.0x64 (TNS V1-V3)
transaction name =
version 186647296
audit sessionid 50038
Client Id =
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 344Kb in 0.01s => 33.64 Mb/sec
Total redo bytes: 1023Kb Longest record: 8Kb, moves: 0/1135 moved: 0Mb (0%)
Longest LWN: 77Kb, reads: 88
Last redo scn: 0x0000.000c0efb (790267)
Change vector header moves = 147/2201 (6%)
----------------------------------------------
typ=2 表示number型
len=2 表示占2个字节