GoldenGate 12c 在原有同步进程中新增同步表的操作步骤

   在原有同步进程里新增同步表的时候，考虑到源端表上可能会有事务，数据是实时变化的，不能单纯的使用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; TABLE ZYNGPRD.E_PRODUCT_STORE_ADDRESS; TABLE ZYNGPRD.STORE_STATUS; TABLE ZYNGPRD.PARTY_TYPE; TABLE ZYNGPRD.PARTY_GROUP; TABLE ZYNGPRD.GEO; TABLE ZYNGPRD.PRODUCT_STORE_TYPE; TABLE ZYNGPRD.PRODUCT_STORE; TABLE ZYNGPRD.PRODUCT; TABLE ZYNGPRD.PRODUCT_TYPE; TABLE ZYNGPRD.ZDEV_PARAM_CONTENT; TABLE ZYNGPRD.UOM; TABLE ZYNGPRD.GOOD_IDENTIFICATION; --20200821 TABLE ZYNGPRD.PRODUCT_YPDX_GEO; GGSCI (ngppdg as ogg@ngpdb2) 10> stop ngp_e01 Sending STOP request to EXTRACT NGP_E01 ... STOP request pending end-of-transaction (1 records so far).. GGSCI (ngppdg as ogg@ngpdb2) 11> start ngp_e01 Sending START request to MANAGER ... EXTRACT NGP_E01 starting

1.3编辑pump进程，添加该表

GGSCI (ngppdg as ogg@ngpdb2) 13> edit params ngp_p01 GGSCI (ngppdg as ogg@ngpdb2) 14> stop ngp_p01 Sending STOP request to EXTRACT NGP_P01 ... Request processed. GGSCI (ngppdg as ogg@ngpdb2) 15> start ngp_p01 Sending START request to MANAGER ... EXTRACT NGP_P01 starting GGSCI (ngppdg as ogg@ngpdb2) 16> view params ngp_p01

1.4 查看当前复制进程状态，确认无延迟后，停止复制进程，查询主库当前的SCN

GGSCI (zysf as ogg@crmngpsd) 8> lag ngp_r01 Sending GETLAG request to REPLICAT NGP_R01 ... Last record lag 5 seconds. Low watermark lag: 6. High watermark lag: 6. Low watermark position: 37244483463. High watermark position: 37244483468. At EOF, no more records to process. GGSCI (zysf as ogg@crmngpsd) 9> stop ngp_r01 Sending STOP request to REPLICAT NGP_R01 ... Request processed. [oracle@NGPDB01 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 3 14:54:38 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 33731759127

1.5 在主库执行基于SCN的expdp导出，并上传到目标数据库服务器上进行导入

expdp \'/ as sysdba\' directory=dmpdir dumpfile=T.dmp tables=ZYNGPRD.PRODUCT_YPDX_GEO compression=all flashback_scn=33731759127 impdp \'/ as sysdba\' directory=dumpdir dumpfile=T.dmp

1.6 编辑复制经常，添加同步表的映射关系

GGSCI (zysf as ogg@crmngpsd) 12> edit params ngp_r01

–添加如下内容，添加导出时的SCN号，这样启动进程后，该表会从该SCN往后应用trail日志，实现数据的一致性

MAP ZYNGPRD.PRODUCT_YPDX_GEO, TARGET ZYNGPRD.PRODUCT_YPDX_GEO,FILTER ( @GETENV ('TRANSACTION', 'CSN') >33731759127);

1.7 启动复制进程

GGSCI (zysf as ogg@crmngpsd) 13> start ngp_r01 Sending START request to MANAGER ... REPLICAT NGP_R01 starting

2.使用基于SCN的flashback query+dblink远程插入

  和第一种方式相比，该方式除了上面1.5的步骤不一样，其他步骤基本上是一致的。这里就不重复把全部步骤写出，只写出和第一种方式不同的步骤，之前的操作参考1.1-1.4

2.1 源端导出表结构建表语句.sql的文件，并去目标端数据库执行创建表

CREATE TABLE "ZYNGPRD"."PRODUCT_YPDX_GEO" ("GEO_ID" VARCHAR2(20), "SALE_PRODUCT_ID" VARCHAR2(20), "PRODUCT_ID" VARCHAR2(20), "MEMO" VARCHAR2(200), "UDP1" VARCHAR2(200), "USER_ID" VARCHAR2(25), "UPDATE_USER_ID" VARCHAR2(25), "LAST_UPDATED_STAMP" TIMESTAMP (6), "LAST_UPDATED_TX_STAMP" TIMESTAMP (6), "CREATED_STAMP" TIMESTAMP (6), "CREATED_TX_STAMP" TIMESTAMP (6), CONSTRAINT "PK_PRODUCT_YPDX_GEO" PRIMARY KEY ("GEO_ID", "SALE_PRODUCT_ID", "PRODUCT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "ZYNGPRD_TBS"

2.2 进行基于flashback query和dblink的远程插入操作

–目标端数据库执行下面插入，其中NGPDBDG 是源端数据库的dblink

INSERT INTO ZYNGPRD.PRODUCT_YPDX_GEO SELECT * FROM ZYNGPRD.PRODUCT_YPDX_GEO@NGPDBDG AS OF SCN 33731759127;

后续操作参考1.6-1.7

  总结：这两种在原有进程里新增表同步的方法实质上都是基于SCN的导出操作，如果时从备库抽取数据，并且主库有一定限制不方便使用expdp进行导出，那么就可以使用第二种方法，否则可以在大量数据的情况下，第一种方法的效率更高。需要注意的两点就是：1.执行scn的导出或者闪回查询远程插入之前，一定要停止复制进程，这样防止丢失导出到停止复制进程这段时间的事务变化的数据。2.最后启动复制进程的时候，不能执行start XXX,aftercsn xxxxxxxx这样去启动，否则会丢失除了这张表以外该进程所有表在停止复制进程和开始基于SCN导出时这段时间内的所有数据，这种方法只适用于新增同步进程里添加表的操作。