OGG19版本源端新增字段,目标端复制进程不报错，使用MAPALLCOLUMNS进行测试

2023-09-30 09:29:28

一、需求，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