在原有同步进程里新增同步表的时候,考虑到源端表上可能会有事务,数据是实时变化的,不能单纯的使用expdp直接导出再导入,这样可能会导致丢失数据或者数据重复冲突。针对这种情况,有两种方法可以实现在原有同步进程里添加新表并且可以保证数据一致性,一种是基于SCN的expdp导出,还有一种是基于SCN的flashback query和dblink远程插入(适用于表数据量不大的情况)。
示例:
1.使用基于SCN的expdp导出方法新增表的同步:
1.1 在登录主库,添加该表的附加日志
GGSCI (ngppdg) 4> info credentialstore
Reading from ./dircrd/:
Default domain: OracleGoldenGate
Alias: NGPDBDG
Userid: ogg@ngppdg
Alias: NGPDB
Userid: ogg@NGPDB
GGSCI (ngppdg) 5> dblogin useridalias NGPDB
Successfully logged into database.
GGSCI (ngppdg as ogg@ngpdb2) 6> ADD TRANDATA,ZYNGPRD.PRODUCT_YPDX_GEO;
查看附加日志是否添加成功:
GGSCI (ngppdg as ogg@ngpdb2) 7> INFO TRANDATA,ZYNGPRD.PRODUCT_YPDX_GEO
Logging of supplemental redo log data is enabled for table ZYNGPRD.PRODUCT_YPDX_GEO.
Columns supplementally logged for table ZYNGPRD.PRODUCT_YPDX_GEO: GEO_ID, PRODUCT_ID, SALE_PRODUCT_ID.
Prepared CSN for table ZYNGPRD.PRODUCT_YPDX_GEO: 33731391784
1.2 编辑抽取进程添加该表,重启抽取进程
GGSCI (ngppdg as ogg@ngpdb2) 8> edit params ngp_e01
GGSCI (ngppdg as ogg@ngpdb2) 9> view params ngp_e01
EXTRACT NGP_E01
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERIDALIAS NGPDBDG
FETCHOPTIONS USESNAPSHOT
FETCHOPTIONS USELATESTVERSION
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
TRANLOGOPTIONS MINEFROMACTIVEDG
EXTTRAIL /u01/app/OGG12_2/dirdat/ng
TABLE ZYNGPRD.PRODUCT_SHIPMENT_BOX_TYPE;