实际运维中，由于OGG同步的数据，未使用DDL同步的情况下，并且DDL新增字段并不规范的情况下，导致数据不一致，本次实验测试不规范的新增字段，会导致什么问题？
数据库版本11.2.0.4， OGG 19.1.0.0.4
OGG测试源端新增字段,目标端并未及时同步字段，源端dml操作测试
OGG Version 19.1.0.0.4 Oracle 11.2.0.4 RAC 2节点。
测试3个表：分别为D1,D2,D3，用户源端YZ,目标端BAK_YZ。
如下分别测试3中情况：
1.复制进程使用assumetargetdefs；
2.复制进程使用OGG 默认情况下；
3.复制进程使用指定的Def文件。
最后：建议生产环境正确的添加字段的步骤。
为了避免阅读乏困，特在此文章前面进行总结：
1.oracle 19 ogg有优化,优先让保障OGG进程不中断，因此新增字段不规范的情况下，会导致新增字段的数据不会同步到ogg目标端，虽然数据不一致，但是OGG软件同步链路正常；
2.正确的步骤
源端关闭抽取进程；
执行DDL脚本；
DELETE,ADD TRANDATA OGG表；
目标端执行DDL脚本；
源端启动EXT抽取进程，完成。
如果使用defgen
源端关闭抽取进程；
执行DDL脚本；
对变更的表，重新输出表结构def文件；
DELETE,ADD TRANDATA OGG表；
目标端关闭复制进程；
执行DDL脚本；
根据源端def文件，更新或更换DEF文件
源端启动EXT抽取进程，完成。
测试1.复制进程使用assumetargetdefs
测试1.复制进程使用assumetargetdefs 源端目标端均有此前同步的一个DD表的数据,复制使用他们的数据进行测试。 SQL> select * from dd; ID CC_NA WITTIME ---------- ----- ------------------------------ 2 2 03-JUN-20 02.34.37.000000 PM SQL> create table d1 as select * from dd; SQL> create table d2 as select * from dd; SQL> create table d3 as select * from dd; 添加最小补充日志 GGSCI (t1 as ogg@t1) 18> dblogin USERID ogg,PASSWORD ogg GGSCI (t1 as ogg@t1) 21> add trandata yz.d1 GGSCI (t1 as ogg@t1) 21> add trandata yz.d2 GGSCI (t1 as ogg@t1) 21> add trandata yz.d3 GGSCI (t1 as ogg@t1) 24> info trandata yz.d1 Logging of supplemental redo log data is enabled for table YZ.D1. Columns supplementally logged for table YZ.D1: "CC_NAME", "ID", "WITTIME". Prepared CSN for table YZ.D1: 2815050 源端测试OGG同步性 SQL> delete d1; SQL> delete d2; SQL> delete d3; SQL> commit; 目标端验证[OK] 目标端如果没有对应的表，OGG复制进程报错，同步表过去就可以了。 2020-07-29 09:29:13 INFO OGG-06505 MAP resolved (entry YZ.D1): MAP "YZ"."D1",TARGET BAK_YZ.D1. 2020-07-29 09:29:13 WARNING OGG-00869 No data found when executing SQL statement <SELECT object_name FROM all_objects WHERE owner=:owner_name and object_name=upper(:object_name) and object_type in ('TABLE', 'VIEW')>. 源端新增字段 alter table d1 add status varchar2(10); 此时无论如何测试，OGG复制进程正常，为啥？ OGG源端 trandata只抽取3个列,因此本次测试需要排除次干扰。 源端OGG 抽取进程重启，trandata重置。 GGSCI (t1 as ogg@t1) 32> stop exta GGSCI (t1 as ogg@t1) 33> delete trandata yz.d1 GGSCI (t1 as ogg@t1) 34> add trandata yz.d1 GGSCI (t1 as ogg@t1) 35> info trandata yz.d1 Logging of supplemental redo log data is enabled for table YZ.D1. Columns supplementally logged for table YZ.D1: "CC_NAME", "ID", "STATUS", "WITTIME". GGSCI (t1 as ogg@t1) 36> start exta 源端INSERT SQL> insert into d1 values(1,1,sysdate,'Y'); SQL> commit; 目标端验证 OK 无报错，只是忽略了STATUS列的信息！ SQL> select * from d1; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 09.37.23.000000 AM 源端UPDATE SQL> UPDATE D1 SET ID=2,STATUS='N' WHERE CC_NAME=1 AND STATUS='Y'; SQL> commit; 目标端验证 OK 无报错，只是忽略了STATUS列的信息！ SQL> select * from d1; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 2 1 29-JUL-20 09.37.23.000000 AM 源端DELETE 一样 SQL> delete d1; SQL> commit; 目标端验证 OK 无报错 SQL> select * from d1; no rows selected 那么如果使用了assumetargetdefs 参数，同步表结构之后呢？ alter table d1 add status varchar2(10); 再次测试INSERT SQL> insert into d1 values(1,1,sysdate,'Y'); SQL> commit; 目标端验证 status 无值！ SQL> select * from d1; ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 1 1 29-JUL-20 09.42.04.000000 AM GGSCI (t2) 20> info repa Log Read Checkpoint File /u01/ogg/base/dirdat/t1000000031 2020-07-29 09:42:06.493758 RBA 3889 [ogg@t2 base]$ ./logdump Logdump >open /u01/ogg/base/dirdat/t1000000031 Logdump >pos 0 Logdump >DETAIL data 省略若干个N 及事务下一个下一个，走到最后一个 Logdump >n 2020/07/29 09:42:06.493.758 Insert Len 62 RBA 3721 Name: YZ.D1 (TDR Index: 4) After Image: Partition x0c G s 0000 0500 0000 0100 3101 0005 0000 0001 0031 0200 | ........1........1.. 1f00 0000 3230 3230 2d30 372d 3239 3a30 393a 3432 | ....2020-07-29:09:42 3a30 342e 3030 3030 3030 3030 3003 0005 0000 0001 | :04.000000000....... 0059 | .Y Column 0 (x0000), Len 5 (x0005) 0000 0100 31 | ....1 Column 1 (x0001), Len 5 (x0005) 0000 0100 31 | ....1 Column 2 (x0002), Len 31 (x001f) 0000 3230 3230 2d30 372d 3239 3a30 393a 3432 3a30 | ..2020-07-29:09:42:0 342e 3030 3030 3030 3030 30 | 4.000000000 Column 3 (x0003), Len 5 (x0005) 0000 0100 59 | ....Y 可以发现存在Clolumn 0,1,2,3 四个列，数据对应1,1,2020-07-29:09:42:04.000000000,Y与插入数据相同！因此源端抽取投递进程无异常！有问题的是复制进程！！！ 重启复制进程 GGSCI (t2) 2> stop repa GGSCI (t2) 3> start repa 源端再次UPdate update d1 set cc_name=2 where status='Y'; SQL> commit; 复制进程终于ABEND了！！！ GGSCI (t2) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REPA 00:00:00 00:00:22 2020-07-29 09:49:12 WARNING OGG-01004 Aborted grouped transaction on BAK_YZ.D1, Database error 1403 (No data found). [ogg@t2 base]$ tail -200f /u01/ogg/base/dirrpt/repa.dsc Oracle GoldenGate Delivery for Oracle process started, group REPA discard file opened: 2020-07-29 09:47:56.707549 Current time: 2020-07-29 09:49:12 Discarded record from action ABEND on error 1403 No data found Aborting transaction on /u01/ogg/base/dirdat/t1 beginning at seqno 31 rba 3,889 error at seqno 31 rba 3889 Problem replicating YZ.D1 to BAK_YZ.D1. Record not found Mapping problem with unified PK update record (target format) SCN:0.3.3.1505... * ID = 1 000000: 31 |1 | CC_NAME = 1 000000: 31 |1 | WITTIME = 2020-07-29 09:42:04.000000000 000000: 32 30 32 30 2d 30 37 2d 32 39 20 30 39 3a 34 32 |2020-07-29 09:42| 000010: 3a 30 34 2e 30 30 30 30 30 30 30 30 30 |:04.000000000 | STATUS = Y 000000: 59 |Y | ID = 1 000000: 31 |1 | CC_NAME = 2 000000: 32 |2 | WITTIME = 2020-07-29 09:42:04.000000000 000000: 32 30 32 30 2d 30 37 2d 32 39 20 30 39 3a 34 32 |2020-07-29 09:42| 000010: 3a 30 34 2e 30 30 30 30 30 30 30 30 30 |:04.000000000 | STATUS = Y 000000: 59 |Y | * Process Abending : 2020-07-29 09:49:12.260433 使用了HANDLECOLLISIONS 参数，但是带来了副作用 源端 ID CC_NA WITTIME STATUS ---------- ----- ------------------------------ ---------- 1 2 29-JUL-20 09.42.04.000000 AM Y 目标端，数据已经和源端不一致了，update不存在的记录，转换为了insert操作！ ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 1 1 29-JUL-20 09.42.04.000000 AM 1 2 29-JUL-20 09.42.04.000000 AM Y 本次不测试HANDLECOLLISIONS，跳过测试其它。 注释HANDLECOLLISIONS参数，重启进程。 源端DELETE操作 delete d1 where status='Y'; commit; 目标端同步OK
测试2.复制进程不使用def参数,默认情况下；
源端新增字段 alter table d2 add status varchar2(10); 此时无论如何测试，OGG复制进程正常，为啥？ OGG源端 trandata只抽取3个列,因此本次测试需要排除次干扰。 源端OGG 抽取进程重启，trandata重置。 GGSCI (t1 as ogg@t1) 32> stop exta GGSCI (t1 as ogg@t1) 33> delete trandata yz.d2 GGSCI (t1 as ogg@t1) 34> add trandata yz.d2 GGSCI (t1 as ogg@t1) 35> info trandata yz.d2 Columns supplementally logged for table YZ.D2: "CC_NAME", "ID", "STATUS", "WITTIME". GGSCI (t1 as ogg@t1) 36> start exta 源端INSERT SQL> insert into d2 values(1,1,sysdate,'Y'); SQL> commit; 目标端验证 OK 无报错，只是忽略了STATUS列的信息！ SQL> select * from d2; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 09.37.23.000000 AM 源端UPDATE SQL> UPDATE D2 SET ID=2,STATUS='N' WHERE CC_NAME=1 AND STATUS='Y'; SQL> commit; 目标端验证 OK 无报错，只是忽略了STATUS列的信息！ 并且update操作忽略了,并未修改。 SQL> select * from d2; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 09.37.23.000000 AM ？？？ ID=1并未同步修改操作 再次测试INSERT操作能否同步 SQL> insert into d2 values(2,2,sysdate,'N'); SQL> commit; 目标端验证 SQL> select * from d2; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 2 2 29-JUL-20 10.02.43.000000 AM 2 1 29-JUL-20 10.00.05.000000 AM 源端DELETE SQL> DELETE D2 WHERE STATUS='N'; 2 rows deleted. SQL> COMMIT; Commit complete. SQL> select * from d2; no rows selected 目标端验证OK SQL> select * from d2; no rows selected 添加字段 alter table d2 add status varchar2(10); 源端INSERT SQL> insert into d2 values(1,1,sysdate,'Y'); SQL> commit; 目标端验证 OK SQL> select * from d2; ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 1 1 29-JUL-20 10.30.10.000000 AM Y 使用默认的情况，目标端同步源端表的DDL操作后,在进行Insert操作正常同步。 源端UPDATE UPdate d2 set id=6,status='N' where status='Y' and id=1; commit; 目标端验证 SQL> select * from d2; ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 1 1 29-JUL-20 10.30.10.000000 AM Y SQL> r 1* select * from d2 ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 6 1 29-JUL-20 10.30.10.000000 AM N
测试3.复制进程使用指定def参数,默认情况下；
echo "defsfile ./dirdef/source_007.def,purge USERID ogg,PASSWORD ogg table YZ.B; table YZ.DD; table YZ.RANGE_PART_TAB; table YZ.SYS_EXPORT_SCHEMA_01; table YZ.TEST; table YZ.TESTA; table YZ.TESTC; table YZ.TEST_NEWLONG; table YZ.TT_PK; table YZ.TEST_LOB; TABLE YZ.D1; TABLE YZ.D2; TABLE YZ.D3;" >>./dirdef/source_007.prm ./defgen paramfile ./dirdef/source_007.prm 复制进程添加 sourcedefs /u01/ogg/base/dirdef/repa.def 同步测试 SQL> insert into d3 values(1,1,sysdate); commit; SQL> SELECT * FROM D3; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 11.02.34.000000 AM OK 此时源端新增一个字段！ SQL> alter table d3 add status varchar2(10); SQL> insert into d3 values(2,2,sysdate,'Y'); commit; 目标端,无影响。 ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 11.02.34.000000 AM 2 2 29-JUL-20 11.03.50.000000 AM GGSCI (t1) 3> dblogin USERID ogg,PASSWORD ogg GGSCI (t1 as ogg@t1) 4> delete trandata yz.d3 GGSCI (t1 as ogg@t1) 5> add trandata yz.d3 SQL> insert into d3 values(3,3,sysdate,'Y'); commit; SQL> SELECT * FROM D3; ID CC_NAME WITTIME ---------- ---------- ------------------------------ 1 1 29-JUL-20 11.02.34.000000 AM 2 2 29-JUL-20 11.03.50.000000 AM 3 3 29-JUL-20 11.06.08.000000 AM 不受影响！ 目标端同步表结构变更。 SQL> alter table d3 add status varchar2(10); 源端再次进行dml操作 SQL> insert into d3 values(4,4,sysdate,'Y'); SQL> SELECT * FROM D3; ID CC_NAME WITTIME STATUS ---------- ---------- ------------------------------ ---------- 1 1 29-JUL-20 11.02.34.000000 AM 2 2 29-JUL-20 11.03.50.000000 AM 3 3 29-JUL-20 11.06.08.000000 AM --重启复制进程 SQL> insert into d3 values(5,5,sysdate,'N'); GGSCI (t2) 54> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT ABENDED REPA 00:00:00 00:00:25 2020-07-30 04:07:12 ERROR OGG-00918 Key column STATUS is missing from map. [ogg@t1 base]$ ./defgen paramfile ./dirdef/source_007.prm OVERRIDE 参数使用
正确做法
1.当使用assumetargetdefs时
验证OGG同步 SQL> insert into d1 values(1,1,sysdate,'o'); OK 正确做法 源端停抽取进程 GGSCI (t1 as ogg@t1) 16> stop exta 源端新增表字段 SQL> alter table d1 add status1 varchar2(10); 源端重新delete,add trandata GGSCI (t1) 3> dblogin USERID ogg,PASSWORD ogg GGSCI (t1 as ogg@t1) 4> info trandata yz.d1 Columns supplementally logged for table YZ.D1: "CC_NAME", "ID", "STATUS", "WITTIME". GGSCI (t1 as ogg@t1) 5> delete trandata yz.d1 --测试delete trandata后，add之前插入数据，能否同步！ SQL> insert into d1 values(6,6,sysdate,'0','0'); SQL> commit; -- GGSCI (t1 as ogg@t1) 6> add trandata yz.d1 GGSCI (t1 as ogg@t1) 8> info trandata yz.d1 Logging of supplemental redo log data is enabled for table YZ.D1. Columns supplementally logged for table YZ.D1: "CC_NAME", "ID", "STATUS", "STATUS1", "WITTIME". 目标端新增OGG字段 SQL> alter table d1 add status1 varchar2(10); 源端启动OGG抽取进程 GGSCI (t1 as ogg@t1) 24> start exta 验证 目标端SQL> select * from d1 where id=6; ID CC_NAME WITTIME STATUS STATUS1 ---------- ---------- ------------------------------ ---------- ---------- 6 6 03-AUG-20 08.11.30.000000 AM 0 0 源端SQL> update d1 set status1=6 where status1='0'; SQL> commit; 目标端SQL> select * from d1 where id=6; ID CC_NAME WITTIME STATUS STATUS1 ---------- ---------- ------------------------------ ---------- ---------- 6 6 03-AUG-20 08.11.30.000000 AM 0 6
2.当使用DEF文件时
源端测试 SQL> insert into d3 values(1,2,sysdate,'o'); SQL> commit; ogg同步正常。 1.源端停抽取进程 GGSCI (t1) 14> info exta,detail 【关闭，启动抽取进程，会自动切换seq文件】 Seqno 40 RBA 2207 GGSCI (t1) 15> stop exta 添加字段 SQL> alter table d3 add status1 varchar2(10); 重新配置DEF文件 [ogg@t1 base]$ vi ./dirdef/source_007.prm defsfile ./dirdef/source_007.def,purge USERID ogg,PASSWORD ogg table YZ.D1; [ogg@t1 base]$ ./defgen paramfile ./dirdef/source_007.prm [ogg@t1 base]$ cat ./dirdef/source_007.def * Definition for table YZ.D1 Record length: 310 Syskey: 0 Columns: 4 ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 2 -1 0 0 0 CC_NAME 64 200 56 0 0 1 0 200 200 0 0 0 0 0 1 0 1 0 1 -1 0 0 0 WITTIME 192 29 262 0 0 1 0 29 29 29 0 6 0 0 1 0 1 0 187 -1 0 0 0 STATUS 64 10 294 0 0 1 0 10 10 0 0 0 0 0 1 0 1 0 1 -1 0 0 0 End of definition 源端对表进行delete,add trandata GGSCI (t1) 3> dblogin USERID ogg,PASSWORD ogg GGSCI (t1 as ogg@t1) 4> info trandata yz.d3 Columns supplementally logged for table YZ.D3: "CC_NAME", "ID", "STATUS", "WITTIME". GGSCI (t1 as ogg@t1) 5> delete trandata yz.d3 --测试delete trandata后，add之前插入数据，能否同步！ SQL> insert into d3 values(6,6,sysdate,'0','0'); SQL> commit; -- GGSCI (t1 as ogg@t1) 6> add trandata yz.d3 GGSCI (t1 as ogg@t1) 8> info trandata yz.d3 Logging of supplemental redo log data is enabled for table YZ.D3. Columns supplementally logged for table YZ.D3: "CC_NAME", "ID", "STATUS", "STATUS1", "WITTIME". 目标端更新def文件,肯定也需要关闭复制进程。 GGSCI (t2) 31> stop repa [ogg@t2 base]$ vi /u01/ogg/base/dirdef/repa.def 替换上述获得的最新def文件。 OGG 列手工同步 SQL> alter table d3 add status1 varchar2(10); 启动复制进程 GGSCI (t2) 2> start repa 源端启动抽取进程 GGSCI (t1 as ogg@t1) 13> start exta 验证？ OK SQL> select * from d3 where id=6; ID CC_NAME WITTIME STATUS STATUS1 ---------- ---------- ------------------------------ ---------- ---------- 6 6 03-AUG-20 08.03.50.000000 AM 0 0