Goldengate source and target table differ in column structure part-2(ogg同步不同的表结构数据)

接前一篇
如何让ogg colmap的两个字段在调用datenow goldengate的函数时，create_date只在记录生成时取当前时间。

Source DB

In GGSCI on the source server add supplemental logging for the table ICME.TEST_S
GGSCI (icme-db) 2> dblogin userid ggsmgr, password passwordxxx
Successfully logged into database.
GGSCI (icme-db) 3> add trandata icme.test_s
Logging of supplemental redo data enabled for table ICME.TEST_S.
如果没有附加日志，有可能出现丢失update，查看discard 文件有如下记录。
GGSCI (ggsdb) 1> view params ricme
...
DiscardFile dirrpt/ricme.dsc, Append, megabytes 400
...
GGSCI (ggsdb) 2> shell vi dirrpt/ricme.dsc
Current time: 2013-09-17 16:26:09
OCI Error ORA-01403: no data found, SQL <UPDATE "ICME"."TEST_T" SET "NAME" = :a1,"CREATE_DATE" = :a2,"LAST_UPDATE_DATE" = :a3 WHERE "ID" = :b0>
Operation failed at seqno 744 rba 16740095
Discarding record on action DISCARD on error 1403
Problem replicating ICME.TEST_S to ICME.TEST_T
Record not found
Mapping problem with compressed update record (target format)...
*
ID =
NAME = x
CREATE_DATE = 2013-09-17 16:26:09
LAST_UPDATE_DATE = 2013-09-17 16:26:09

Verify

Source DB

SQL> insert into icme.test_s values(10,'h');
1 row created.
SQL> commit;
Commit complete.

Target DB

icme@GGS>select * from test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
1 a 2013-09-17 15:18:51 2013-09-17 15:18:51
10 h 2013-09-17 16:30:36 2013-09-17 16:30:36
9 h 2013-09-17 16:19:33 2013-09-17 16:19:33

Source DB

SQL> update icme.test_s set name='x' where id=10;
1 row updated.
SQL> commit;
Commit complete.

Target DB

icme@GGS>select * from test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
1 a 2013-09-17 15:18:51 2013-09-17 15:18:51
10 x 2013-09-17 16:31:07 2013-09-17 16:31:07
9 h 2013-09-17 16:19:33 2013-09-17 16:19:33
当然也可以取源端的当前时间，或其它变量值传递给目标端使用token ogg函数，比如在data pump进程组修改,不允许使用passthru，同replicat 指定表定义文件SOURCEDEFS，增加table tokens列，可以增加dbuser,osuser,dbversion,optype,table 等等。下面是增加了optype

Source DB

GGSCI (icme-db) 1> edit params eicmed
...省略
-- passthru
SOURCEDEFS dirsql/deficme.prm
NODYNAMICRESOLUTION
table icme.test_s,
tokens(
TKN-OP-TYPE=@GETENV("GGHEADER", "optype"),
);
DYNAMICRESOLUTION
tableexclude icme.test_s
table icme.*;

Target DB

ogg replicat 进程修改colmap如下
map icme.test_s, target icme.test_t,
COLMAP (id=id,
name=@TOKEN("TKN-OP-TYPE"),
last_update_date=@datenow());

Verify token

Source DB

sys@ICME>insert into icme.test_s values(14,'bb');
1 row created.
sys@ICME>commit;
Commit complete.
sys@ICME>update icme.test_s set name='anbob' where id=14;
1 row updated.
sys@ICME>commit;

Target DB

sys@GGS>select * from icme.test_t where id=14;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
14 INSERT 2013-09-18 12:56:35
ok, 我们回归正题来实现create_date 记录insert 时间，last_update_date记录最后update的时间
方法1.based on transaction type

Target DB

GGSCI (ggsdb) 74> edit params ricme
... 省略
NODYNAMICRESOLUTION
WILDCARDRESOLVE IMMEDIATE
ALLOWDUPTARGETMAP
GETINSERTS
IGNOREUPDATES
IGNOREDELETES
map icme.test_s, target icme.test_t,
COLMAP(usedefaults,
create_date =@datenow(),
last_update_date=@datenow()
);
GETUPDATES
IGNOREINSERTS
IGNOREDELETES
map icme.test_s, target icme.test_t,
COLMAP(usedefaults,
last_update_date=@datenow());
DYNAMICRESOLUTION
mapexclude icme.test_s
Map icme.*, Target icme.* ;

Tip:
If ALLOWDUPTARGETMAP is not specified and the same source and target tables are mapped more than once, only the first MAP statement is used and the others are ignored.
Default NOALLOWDUPTARGETMAP
Syntax ALLOWDUPTARGETMAP | NOALLOWDUPTARGETMAP

Verify

Source DB

sys@ICME>insert into icme.test_s values(16,'weejar');
1 row created.
sys@ICME>commit;
Commit complete.

Target DB

sys@GGS>select * from icme.test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
...
16 weejar 2013-09-18 13:45:53 2013-09-18 13:45:53

Source DB

sys@ICME>update icme.test_s set name='anbob' where id=16;
1 row updated.
sys@ICME>commit;
Commit complete.

Target DB

sys@GGS>select * from icme.test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
...
16 anbob 2013-09-18 13:45:53 2013-09-18 13:46:28
方法二，base on goldengate function @if @STREQ optype

Target DB

GGSCI (ggsdb) 1> view params ricme
... 省略
map icme.test_s, target icme.test_t,
COLMAP(usedefaults,
create_date =@IF(@STREQ(@GETENV("GGHEADER","OPTYPE"),"INSERT"),@DATENOW(),@COLSTAT(MISSING)),
last_update_date=@datenow()
);

Verify colmap if

Source DB

sys@ICME>insert into icme.test_s values(17,'weejar');
1 row created.
sys@ICME>commit;
Commit complete.

Target DB

-- target
sys@GGS>select * from icme.test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
...
16 weejar 2013-09-18 13:45:53 2013-09-18 13:45:53
10 rows selected.

Source DB

sys@ICME>update icme.test_s set name='anbob' where id=17;
1 row updated.
sys@ICME>commit;
GGSCI (icme-db) 5> stats eicmed latest
Sending STATS request to EXTRACT EICMED ...
Start of Statistics at 2013-09-18 13:55:07.
Output to dirdat/l2:
Extracting from ICME.TEST_S to ICME.TEST_S:
*** Latest statistics since 2013-09-18 12:39:30 ***
Total inserts 5.00
Total updates 5.00
Total deletes 0.00
Total discards 0.00
Total operations 10.00

Target DB

sys@GGS>select * from icme.test_t;
ID NAME CREATE_DATE LAST_UPDATE_DATE
-------------------- -------------------- ------------------- -------------------
...
16 anbob 2013-09-18 13:45:53 2013-09-18 13:46:28
--done
在此感谢网友newway提供的帮助
Summary:
表结构不同时需要defgen生成定义文件效率相对于ASSUMETARGETDEFS 要慢一些。
本测试修改完配置文件后都有重启进程使修改生效
使用colmap 可以对表结构做映射关系
使用token function可以从源端附加变量值
使用get/IGNORE[DML]可以做基于事务的filter
DYNAMICRESOLUTION为了提高性能在控制表名解析时只读取一次，这样在mapexclude时会存在整个map table没有按顺序而是全部排除.
goldengate的函数很多，多参考 OGG官方联机首册