In this below tutorial, we will setup one-way goldengate replication for below two tables from database SRCDB to TRGDB.

DBACLASS.EMP;
DBACLASS.DEPT;

SEE – Oracle goldengate Fundamentals

NOTE – Here we are replicating only DML transactions

PREREQUISITE:

Make sure the goldengate installation is completed and manager process is running on both source and target hosts.

REFER: Installation and basic configuration of goldengate 

Once the installation is done, proceed with below steps.

1. Enable supplemental logging for those tables.[SOURCE]

GGSCI > dblogin USERID ggate_user, PASSWORD ggate_user

Successfully logged into database.

GGSCI > add trandata DBACLASS.EMP

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


GGSCI > add trandata DBACLASS.DEPT

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

2. Prepare extract parameter file[ SOURCE ]

GGSCI> edit params EXT1

EXTRACT EXT1
SETENV (ORACLE_SID="SRCDB")
SETENV (ORACLE_HOME = "/home/oracle/app/oracle/product/12.1.0/dbhome_1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid ggate_user, PASSWORD ggate_user
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;

Here – /ggate/app/oracle/product/11.2.1.0.3/TST is the location on LOCAL(SOURCE HOST) where the extract trail files will be created with prefix E5*

3. Prepare the extract pump parameter file [SOURCE]

GGSCI> EDIT PARAMS EXT1P

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

 

RMTTRAIL – Here /ggate/oradata/datacapture/TRG in the location on TARGET HOST where the extract trails from source will be pushed and the prefix will be P1*.

4. Add extract process [SOURCE]

add extract EXT1,tranlog,begin now 

EXTRACT added.

5. Add the extract trail [SOURCE]

GGSCI > add exttrail /ggate/app/oracle/product/11.2.1.0.3/TST/E5,extract EXT1
EXTTRAIL added.

6. Add the extract pump process [SOURCE]

GGSCI > add extract EXT1P, exttrailsource /ggate/app/oracle/product/11.2.1.0.3/TST/E5
EXTRACT added.

7. Add remote trail [SOURCE]

GGSCI > add rmttrail /ggate/oradata/datacapture/TRG/P1,extract EXT1P
RMTTRAIL added.

Now we will configure replicat on target server:

8. Prepare replicat parameter file [ TARGET ]

Here we have to add HANDLECOLLISION parameter to resolve conflicts for the transactions during initial loading. We will remove this parameter once the lag is cleared after initial loading.

As per Oracle DOC:

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions

ggsci> edit params REPR1

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

9. add the replicat process [ TARGET ]

GGSCI > add replicat REPR1, exttrail /ggate/oradata/datacapture/TRG/P1
REPLICAT added.

Now complete setup is done. Next step is start the extract process and do the initial loading .
There are multiple methods to do initial loading, But here We will use the simple expdp impdp method.

10. Start the extract [ SOURCE ]

GGSCI > start EXT1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03 ----- >>> RUNNING 
EXTRACT     STOPPED     EXT1P       00:00:00      00:02:34


Now as the extract is running, we will proceed with initial loading(datapump method). i.e it will start capturing the transactions.

10. INITIAL LOADING:

EXPORT FROM SOURCE:

expdp dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.EMP,DBACLASS.DEPT

E

With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_02":  /******** AS SYSDBA dumpfile=initload.dmp logfile=initload.log directory=DIRT tables=DBACLASS.EMP,DBACLASS.DEPT
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 16 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "DBACLASS"."EMP"                            3.473 MB   90323 rows
. . exported "DBACLASS"."DEPT"                           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
Job "SYS"."SYS_EXPORT_TABLE_02" successfully completed at Sun Jul 23 11:56:50 2017 elapsed 0 00:00:13

COPY THE DUMP TO TARGET:

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

IMPORT IN TARGET DB:

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and UTF8 NCHAR character set
WARNING: possible data loss in character set conversions
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
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBACLASS"."EMP"                            3.473 MB   90323 rows
. . imported "DBACLASS"."DEPT"                           3.473 MB   90323 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Initial loading is completed.

11. Start the datapump extract [ SOURCE ]

GGSCI > start EXT1P

Sending START request to MANAGER ...
EXTRACT EXT1P starting


GGSCI (dm-relay-dev-Test) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03
EXTRACT     RUNNING     EXT1P       00:00:00      00:00:01


12. Start the replicat [ TARGET ]

GGSCI (DBDvLBSS01) 17> start REPR1

Sending START request to MANAGER ...
REPLICAT REPR1 starting

13. Remove HANDLECOLLSION after lag is cleared and restart replicat.

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.
 
 GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1      00:43:54         00:00:06       --- >>>> Lag at checkpoint is not  zero yet.

 
GGSCI >  !
 
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.


GGSCI > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPR1      00:00:00          00:00:06 -- > Lag at checkpoint is zero, i.e lag is zero now.



As lag is zero now, Remove the handlescollision parameter and restart the replicat.

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

 

stop REPR1


start REPR1

Let’s add some record in source and see whether it is replicating to target or not:

SQL> insert into "DBACLASS"."EMP" values ('TEST','DB','DB2',384302938);

1 row created.

SQL> COMMIT;

Commit complete.


GGSCI > stats EXT1

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-23 12:07:45.

Output to /ggate/app/oracle/product/11.2.1.0.3/TST/E5:

Extracting from DBACLASS.EMP to DBACLASS.EMP:

*** Total statistics since 2017-07-23 12:04:16 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00
GGSCI > stats EXT1

Sending STATS request to EXTRACT EXT1 ...

Start of Statistics at 2017-07-23 12:07:45.

Output to /ggate/app/oracle/product/11.2.1.0.3/TST/E5:

Extracting from DBACLASS.EMP to DBACLASS.EMP:

*** Total statistics since 2017-07-23 12:04:16 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

Check the replicat status:

GGSCI > stats REPR1

Sending STATS request to REPLICAT REPR1 ...

Start of Statistics at 2017-07-23 12:06:58.

Replicating from DBACLASS.EMP to DBACLASS.DEPT:

*** Total statistics since 2017-07-23 12:06:58 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

We can see the extract captures one insert and replicat received one insert. Lets do the row_count on both dbs .

ROW COUNT ON SOURCE:

SQL> select count(*) from “DBACLASS”.”EMP” ;

COUNT(*)
———-
90324

ROW COUNT ON TARGET:

SQL> select count(*) from “DBACLASS”.”EMP” ;

COUNT(*)
———-
90324

 

Both row_count are matching. Our table level replication setup  in goldengate  is completed. It will now apply all the transactions happening on the source to target db.