OGG新增字段标准化测试一(非主键情况下，且add trandata schema.table)

实际运维中，由于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