OGG-如何只同步最近某个时间范围的数据
一、需求,某客户希望使用OGG只同步时间大于2021-02-01日期之后的数据变化
需求如标题所示，如何使用OGG进行配置？
客户环境需要同步的表有几百G,表数据太大了;如果同步所有数据,目标库空间存储方面消耗太大；
并且表太大之后,索引数据量也会成倍增长；
如果使用OGG只迁移数据+实时同步半年的数据，那么表的数据量将减少为原来的1/10；
二、环境测试
2.1 参考资料
How to Filter Records by Date in Oracle GoldenGate Extract and Replicat (Doc ID 1458966.1) GOAL How to filter based on this Date column CREATION_TIME >='2012-01-01' SOLUTION Replicat: MAP {source table}, TARGET {target table}, FILTER (@COMPUTE (@DATE("JUL", "YYYY-MM-DD", creation_time) >= @DATE("JUL", "YYYY-MM-DD", "2012-01-01")) > 0); Extract: TABLE {source table}, FILTER (@COMPUTE (@DATE("JUL", "YYYY-MM-DD", creation_time) >= @DATE("JUL", "YYYY-MM-DD", "2012-01-01")) > 0); -- 错误 OGG-01157 使用带有日期的 Where 子句和带有 @NULL 子句的 OGG-00375 和用于 Teradata 的 GoldenGate（文档 ID 1564687.1） Bug 27278457 - @DATE Not Working As Expected in OGG 12.3 Error OGG-01157 Using Where Clause With Dates and OGG-00375 with @NULL Clause With GoldenGate for Teradata (Doc ID 1564687.1)
2.2 测试环境配置
create table c1(id int ,create_time date); create table c2(id int ,create_time date); create table c3(id int ,create_time date); GGSCI (t1) 4> dblogin USERID goldengate, PASSWORD goldengate add trandata yz.c1 add trandata yz.c2 add trandata yz.c3 使用语法是@DATE ('output format', 'input format', 'source column')。 > edit param ext_t1 TABLE YZ.c1; TABLE YZ.c2,FILTER(@COMPUTE(@DATEDIFF ('DD',create_time,'2021-06-01') ) <=0 ); TABLE YZ.c3; --failed 如下配置均失败！ --TABLE YZ.c2,FILTER(@COMPUTE(@DATE('CCCDATA','YYYY-MM-DD HH24:MI:SS',create_time)>= @DATE('CCCDATA','YYYY-MM-DD HH24:MI:SS',
'2012-06-01 00:00:00'))); --TABLE YZ.c2,FILTER(@DATE('YYYY-MM-DD HH24:MI:SS',create_time)>=@DATE('YYYY-MM-DD HH24:MI:SS','2012-06-01 00:00:00')); --TABLE YZ.c2,FILTER(@DATE('CCCDATA','YYYY-MM-DD HH24:MI:SS',create_time)>=@DATE('CCCDATA','YYYY-MM-DD HH24:MI:SS','2012-06-01 00:00:00')); --TABLE YZ.c2,WHERE (create_time>TO_DATE('2021-06-01','YYYY-MM-DD')); --TABLE YZ.c2,FILTER(@COMPUTE(@DATE('CDATA','YYYY-MM-DD HH24:MI:SS',create_time)- @DATE('CDATA','YYYY-MM-DD HH24:MI:SS','2012-06-01 00:00:00'))>0); --TABLE YZ.c2,FILTER(@COMPUTE(@DATE('YYYY-MM-DD HH24:MI:SS',create_time)>=@DATE('YYYY-MM-DD HH24:MI:SS','2012-06-01 00:00:00'))>0); --TABLE YZ.c2,FILTER (create_time>TO_DATE('2021-06-01','YYYY-MM-DD'));
2.3 正确的配置
> edit param dp_t1 TABLE YZ.c1; TABLE YZ.c2; TABLE YZ.c3; --tag create table c1(id int ,create_time date); create table c2(id int ,create_time date); create table c3(id int ,create_time date); > edit param rep_gbk map yz.c1 ,target yz.c1; map yz.c2 ,target yz.c2; map yz.c3 ,target yz.c3,FILTER(@COMPUTE(@DATEDIFF ('DD',create_time,'2021-06-01') ) <=0 ); --channel 2 --source --test insert into c2 values(1,to_date('20210101','yyyymmdd')); insert into c2 values(2,to_date('20210701','yyyymmdd')); insert into c2 values(1,to_date('20210101 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c2 values(2,to_date('20210701 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c2 values(11,to_date('20210601 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c2 values(12,to_date('20210601 00:00:01','YYYY-MM-DD HH24:MI:SS')); insert into c2 values(13,to_date('20210530 23:00:00','YYYY-MM-DD HH24:MI:SS')); commit; --target SQL> select id,to_char(create_time,'yyyy-mm-dd hh24:mi:ss') from c2; ID TO_CHAR(CREATE_TIME ---------- ------------------- 2 2021-07-01 00:00:00 2 2021-07-01 00:00:00 11 2021-06-01 00:00:00 12 2021-06-01 00:00:01 --channel 3 --source --test insert into c3 values(1,to_date('20210101','yyyymmdd')); insert into c3 values(2,to_date('20210701','yyyymmdd')); insert into c3 values(3,to_date('20210101 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c3 values(4,to_date('20210701 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c3 values(11,to_date('20210601 00:00:00','YYYY-MM-DD HH24:MI:SS')); insert into c3 values(12,to_date('20210601 00:00:01','YYYY-MM-DD HH24:MI:SS')); insert into c3 values(13,to_date('20210530 23:00:00','YYYY-MM-DD HH24:MI:SS')); commit; --target SQL> select id,to_char(create_time,'yyyy-mm-dd hh24:mi:ss') from c3; ID TO_CHAR(CREATE_TIME ---------- ------------------- 2 2021-07-01 00:00:00 4 2021-07-01 00:00:00 11 2021-06-01 00:00:00 12 2021-06-01 00:00:01
小结：这种语法基本上也是抄袭MOS的，但是MOS举例无法直接使用,OGG 19.1版本 for 11g db对这种date类型的转换无法正常获取,但是使用函数转换为数值在进行对比就可以了。
