一、需求，OGG19版本源端新增字段,目标端复制进程不报错,而是直接跳过这个字段进行同步数据了
现在客户提了一个需求，能否让OGG在不同步DDL的情况下,源端新增字段后，目标端OGG复制进程abend提示表结构不一致！
找了半天，找到一个可能相关的OGG参数，接下来使用这个参数进行测试！！！MAPALLCOLUMNS参数
着急结果的朋友们直接看此处总结： 无论何种办法，源端新增字段，目标端没有同步这个字段的情况下,OGG会自动跳过！因此需要规范化操作！！！
Oracle优先保障数据同步的实时性
1. 正常OGG链路同步测试,正常
2.使用MAPALLCOLUMNS参数表级别生效,模拟源端新增字段测试
3.对新增字段重新add trandata测试,新增字段在目标端直接忽略跳过,OGG不报错
4.对测试表级别使用NOMAPALLCOLUMNS参数,无效果,OGG不报错
5.对复制进程层面使用MAPALLCOLUMNS参数,测试新增字段,OGG目标端不报错;
6.对复制进程层面使用MAPALLCOLUMNS参数 + assumetargetdefs参数,测试新增字段,OGG目标端不报错
备注说明：本次测试源端、目标端环境为同数据库类型，如果是Oracle->Mysql 有同事在参数使用MAPALLCOLUMNS，如果未手工同步DDL，即使OGG版本19一样abend!!!
二、测试步骤
2.1. 正常OGG链路同步测试,正常
EXT_GBK SETENV (ORACLE_SID = "gbkt1") EXTTRAIL /u01/ogg/base/dirdat/ext_gbk/cc TABLE YZ.TEST00A1; TABLE YZ.TEST00A2; SQL> desc YZ.TEST00A2 Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) GGSCI (t1) 52> info d_gbk_a,detail Trail Name Seqno RBA Max MB Trail Type /u01/ogg/base/dirdat/gbk/aa 9 19439384 200 RMTTRAIL Extract Source Begin End /u01/ogg/base/dirdat/ext_gbk/cc000000021 2021-07-01 11:04 2021-06-13 05:04 GGSCI (t2) 10> info rep_gbk,detail Log Read Checkpoint File /u01/ogg/base/dirdat/gbk/aa000000009 map yz.TEST00A1 ,target bak_yz.TEST00A1; map yz.TEST00A2 ,target bak_yz.TEST00A2,MAPALLCOLUMNS;
--测试环境性能太差,从集成抽取降级为经典模式抽取 https://blog.csdn.net/zzt_2009/article/details/105581190 EXTRACT RUNNING EXT_GBK 00:03:11 00:00:03 Log Read Checkpoint Oracle Integrated Redo Logs GGSCI (t1 as ogg@gbkt1) 200> dblogin USERID ogg,PASSWORD OGG Successfully logged into database. GGSCI (t1 as ogg@gbkt1) 201> info EXTRACT EXT_GBK downgrade ERROR: Extract EXT_GBK is not ready to be downgraded because recovery SCN values are not set. SQL> select current_scn from v$database; CURRENT_SCN ----------- 4063221 >stop ext_gbk >alter EXTRACT EXT_GBK,scn 4063221 > info EXTRACT EXT_GBK downgrade Extract EXT_GBK is ready to be downgraded from integrated capture. Archive logs corresponding to SCN 4063221
and higher must be accessible by the downgraded extract. >ALTER EXTRACT EXT_GBK DOWNGRADE INTEGRATED TRANLOG Extract EXT_GBK successfully downgraded from integrated capture. > start ext_gbk
--测试数据一致 select count(*) from yz.test00a1; COUNT(*) ---------- 86581 select count(*) from yz.test00a2; select count(*) from bak_yz.test00a1; select count(*) from bak_yz.test00a2; create public database link gbk_db_link1 connect to SYSTEM identified by oracle using '10.0.0.31:1521/gbkt1'; create table bak_yz.TEST00A1 as select * from yz.test00a1@gbk_db_link1; create table bak_yz.TEST00A2 as select * from yz.test00a1@gbk_db_link1; -OGG TEST delete yz.test00a1 where rownum=1; delete yz.test00a2 where rownum=1; commit; Target 数据正常同步！！！
2.2.使用MAPALLCOLUMNS参数表级别生效,模拟源端新增字段测试
测试 Source 新增字段 alter table yz.test00a1 add test001 varchar2(20); SQL> insert into yz.test00a1(OBJECT_ID,TEST001) values(6666666,'qwer'); SQL> commit; select OBJECT_ID,TEST001 from yz.TEST00A1 where object_id=6666666; OBJECT_ID TEST001 ---------- -------------------- 6666666 qwer target select OBJECT_ID from bak_yz.TEST00A1 where object_id=6666666; OBJECT_ID ---------- 6666666 --另一个表！ alter table yz.test00a2 add test001 varchar2(20); insert into yz.test00a2(OBJECT_ID,TEST001) values(6666666,'qwer'); commit; select OBJECT_ID,TEST001 from yz.TEST00A2 where object_id=6666666; OBJECT_ID TEST001 ---------- -------------------- 6666666 qwer Target SQL> select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666666; OBJECT_ID ---------- 6666666 --无报错！！！
2.3.对新增字段重新add trandata测试,新增字段在目标端直接忽略跳过,OGG不报错
---Source 对表重新add trandata GGSCI (t1 as ogg@gbkt1) 256> delete trandata yz.test00a2 GGSCI (t1 as ogg@gbkt1) 257> add trandata yz.test00a2 GGSCI (t1 as ogg@gbkt1) 258> info trandata yz.TEST00A2 Columns supplementally logged for table YZ.TEST00A1: "CREATED", "DATA_OBJECT_ID", "EDITION_NAME", "GENERATED",
"LAST_DDL_TIME", "NAMESPACE", "OBJECT_ID", "OBJECT_NAME", "OBJECT_TYPE", "OWNER", "SECONDARY", "STATUS",
"SUBOBJECT_NAME", "TEMPORARY", "TIMESTAMP". Columns supplementally logged for table YZ.TEST00A1: "CREATED", "DATA_OBJECT_ID", "EDITION_NAME", "GENERATED",
"LAST_DDL_TIME", "NAMESPACE", "OBJECT_ID", "OBJECT_NAME", "OBJECT_TYPE", "OWNER", "SECONDARY", "STATUS",
"SUBOBJECT_NAME", "TEMPORARY", "TEST001", "TIMESTAMP". Source 再次插入测试 insert into yz.test00a1(OBJECT_ID,TEST001) values(6666667,'qwer'); commit; select OBJECT_ID,TEST001 from yz.TEST00A1 where object_id=6666667; Target select OBJECT_ID from bak_yz.TEST00A1 where object_id=6666667; OBJECT_ID ---------- 6666667 insert into yz.test00a2(OBJECT_ID,TEST001) values(6666667,'qwer'); commit; select OBJECT_ID,TEST001 from yz.TEST00A2 where object_id=6666667; Target select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666667; OBJECT_ID ---------- 6666667 不报错！！！
2.4.对测试表级别使用NOMAPALLCOLUMNS参数,无效果,OGG不报错
--换一个参数 NOMAPALLCOLUMNS -- map yz.TEST00A2 ,target bak_yz.TEST00A2,noMAPALLCOLUMNS; 再次测试 alter table yz.test00a2 add test002 varchar2(20); insert into yz.test00a2(OBJECT_ID,TEST002) values(6666668,'qwer'); commit; select OBJECT_ID,TEST002 from yz.TEST00A2 where object_id=6666668; OBJECT_ID TEST002 ---------- -------------------- 6666668 qwer Target select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666668; OBJECT_ID ---------- 6666668 NOMAPALLCOLUMNS source insert into yz.test00a2(OBJECT_ID,TEST002) values(6666669,'qwer'); commit; target select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666669; OBJECT_ID ---------- 6666669
2.5.对复制进程层面使用MAPALLCOLUMNS参数,测试新增字段,OGG目标端不报错;
--参数级别 map yz.TEST00A2 ,target bak_yz.TEST00A2; MAPALLCOLUMNS map yz.TEST00A1 ,target bak_yz.TEST00A1; Source 新增字段 alter table yz.test00a1 add test001 varchar2(20); SQL> insert into yz.test00a1(OBJECT_ID,TEST001) values(6666666,'qwer'); SQL> commit; select OBJECT_ID,TEST001 from yz.TEST00A1 where object_id=6666666; OBJECT_ID TEST001 ---------- -------------------- 6666666 qwer target select OBJECT_ID from bak_yz.TEST00A1 where object_id=6666666; OBJECT_ID ---------- 6666666 --另一个表！ alter table yz.test00a2 add test001 varchar2(20); insert into yz.test00a2(OBJECT_ID,TEST001) values(6666666,'qwer'); commit; select OBJECT_ID,TEST001 from yz.TEST00A2 where object_id=6666666; OBJECT_ID TEST001 ---------- -------------------- 6666666 qwer Target SQL> select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666666; OBJECT_ID ---------- 6666666 --无报错！！！
2.6.对复制进程层面使用MAPALLCOLUMNS参数 + assumetargetdefs参数,测试新增字段,OGG目标端不报错
!!! 使用参数！ GGSCI (t2) 16> stop rep_gbk assumetargetdefs GGSCI (t2) 18> start rep_gbk 再次测试 Source 新增字段 alter table yz.test00a1 add test002 varchar2(20); insert into yz.test00a1(OBJECT_ID,TEST002) values(6666666,'qwer'); commit; select OBJECT_ID,TEST002 from yz.TEST00A1 where object_id=6666666; OBJECT_ID TEST002 ---------- -------------------- 6666666 6666666 qwer target select OBJECT_ID from bak_yz.TEST00A1 where object_id=6666666; OBJECT_ID ---------- 6666666 6666666 --另一个表！ alter table yz.test00a2 add test002 varchar2(20); insert into yz.test00a2(OBJECT_ID,TEST002) values(6666666,'qwer'); commit; select OBJECT_ID,TEST002 from yz.TEST00A2 where object_id=6666666; OBJECT_ID TEST002 ---------- -------------------- 6666666 6666666 qwer Target SQL> select OBJECT_ID from bak_yz.TEST00A2 where object_id=6666666; OBJECT_ID ---------- 6666666 6666666