Adding new table to a existing extract and replicat in goldengate DBACLASS

Below are the steps for adding a new table to the existing extract and replicat. In this example, we will add DBACLASS.SAL table to existing replication.

1.stop extract and pump process [ SOURCE]

GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:00
EXTRACT     RUNNING     EXT1P       00:00:00      00:00:00


GGSCI> stop EXT1

GGSCI> stop EXT1P



GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:00:00
EXTRACT     STOPPED     EXT1P       00:00:00      00:00:00

2. STOP replicat:[ TARGET ]

confirm that lag is zero i.e replicat consumed all the transactions.

GGSCI  > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1       00:00:00      00:00:02

GGSCI >  lag replicat REPR1

2017-07-24 15:33:12  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REPR1 ...
Last record lag 6 seconds.
At EOF, no more records to process. . <<<<<< ------- This indicates lag is zero

As LAG command reports that no more records to process(i.e lag is zero), we can stop the replicat also.

GGSCI (DBDvLBSS01) 3> stop REPR1

Sending STOP request to REPLICAT REPR1 ...
Request processed.

3. Enable supplemental logging for the tables, that need to be added [ SOURCE].

GGSCI > dblogin USERID ggate, PASSWORD ggate123
Successfully logged into database.

GGSCI > add trandata DBACLASS.SAL

Logging of supplemental redo data enabled for table DBACLASS.SAL.
TRANDATA for scheduling columns has been added on table 'DBACLASS.SAL'.
TRANDATA for instantiation CSN has been added on table 'DBACLASS.SAL'.

4. Include the table in extract param file:[ SOURCE ]

Add the line TABLE DBACLASS.SAL; in parameter file.

GGSCI> edit params EXT1

EXTRACT EXT1
SETENV (ORACLE_SID="SRCDB")
SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ggate, PASSWORD ggate123
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /ggate/app/oracle/product/11.2.1.0.3/TST/EXT1.dsc, APPEND, MEGABYTES 100
EXTTRAIL /ggate/app/oracle/product/11.2.1.0.3/TST/E5
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE DBACLASS.EMP;
TABLE DBACLASS.DEPT;
TABLE DBACLASS.SAL;

5. Include the table in extract pump file: [ SOURCE]

Add the line TABLE DBACLASS.SAL; in pump parameter file.

GGSCI> edit params EXT1P

EXTRACT EXT1P
SETENV (ORACLE_HOME = "/oracle/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "SRCDB")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID ggate, PASSWORD ggate123
RMTHOST 172.20.21.56, MGRPORT 7809
RMTTRAIL /ggate/oradata/datacapture/TRG/P1
TABLE DBACLASS.EMP;
TABLE DBACLASS.DEPT;
TABLE DBACLASS.SAL;

6 . start the extract: [ SOURCE ]

GGSCI > start EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

7. Start the extract pump: [ SOURCE ]

GGSCI > start EXT1P

Sending START request to MANAGER ...
EXTRACT EXT1P starting

8. Now do the initial loading:

Here for initial loading, take export dump of the table that we are adding and import in target database.

expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.SAL
.
.
.
Username: / as sysdba

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "DBACLASS"."SAL"                            3.473 MB   90323 rows
Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_02 is:
  /archive/dump/initload.dmp

Copy the dump to target host and import.

scp oracle@172.38.342.29:/archive/dump/initload.dmp .

impdp dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH

Import: Release 12.1.0.2.0 - Production on Sun Jul 23 12:01:58 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Starting "SYS"."SYS_IMPORT_FULL_04":  /******** AS SYSDBA dumpfile=initload.dmp logfile=imp_initialload.log directory=FULL_REFRESH
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBACLASS"."SAL"                            3.473 MB   90323 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

9. Update the replicat parameter file with handlecollisions parameter: [ TARGET]

Add the line MAP DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS; in param file

REPLICAT REPR1
setenv (ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/dbhome_1")
setenv (ORACLE_SID="TRGDB")
USERID ggate, PASSWORD ggate123
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
ASSUMETARGETDEFS
DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000
MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;
MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;
MAP  DBACLASS.SAL,TARGET DBACLASS.SAL, HANDLECOLLISIONS;

10 . Start the replicat and wait for the lag to be cleared.[TARGET]

ggsci> start repr1


GGSCI >  lag replicat REPR1

2017-07-24 15:33:12  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REPR1 ...
Last record lag 6 seconds.

Wait till this lag is cleared( lag command will report as At EOF, no more records to process.)

GGSCI >  lag replicat REPR1

2017-07-24 15:33:12  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to REPLICAT REPR1 ...
Last record lag 6 seconds.

At EOF, no more records to process.

11. Remove the HANDLECOLLISIONS from the replicat parameter file and restart the replicat[TARGET]

As the lag is cleared, remove the handlecollision parameter and restart the replicat.

ggsci>stop REPR1

ggsci> edit replicat REPR1

REPLICAT REPR1
setenv (ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/dbhome_1")
setenv (ORACLE_SID="TRGDB")
USERID ggate, PASSWORD ggate123
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST
ASSUMETARGETDEFS
DISCARDFILE /ggate/oradata/datacapture/TRG/reprt.dsc,append,megabytes 1000
MAP  DBACLASS.EMP,TARGET DBACLASS.EMP;
MAP  DBACLASS.DEPT,TARGET DBACLASS.DEPT ;
MAP  DBACLASS.SAL,TARGET DBACLASS.SAL;


GGSCI>start REPR1

With these steps, we have added a new table successfully to the existing extract and replicat.

5 thoughts on “Adding new table to a existing extract and replicat in goldengate”

Gus says:
March 22, 2019 at 5:32 pm
Step 9 (Update the replicat parameter file with handlecollisions parameter) should say TARGET, not SOURCE, correct?
1. Admin says:
  March 23, 2019 at 4:58 am
  Thanks a lot Gus :). I have corrected it now.
Laverde Williams says:
January 21, 2020 at 8:19 pm
Hi, nice post.
Beside using CSN, export,import without handlecollisions. What could be the ins and outs between those two methods
Deb says:
March 24, 2021 at 1:22 pm
Don’t we need to export using flashback_scn?
1. Admin says:
  March 25, 2021 at 3:43 am
  HANDLECOLLISIONS will take care of that. Sometime in big environements using flashback_scn is not allowed , as it generates lot of undo. So HANDLECOLLISIONS will take care of that one.

Adding new table to a existing extract and replicat in goldengate