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]

2. Prepare extract parameter file[ SOURCE ]

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]

 

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]

5. Add the extract trail [SOURCE]

6. Add the extract pump process [SOURCE]

7. Add remote trail [SOURCE]

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

9. add the replicat process [ TARGET ]

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 ]

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:

COPY THE DUMP TO TARGET:

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

IMPORT IN TARGET DB:

Initial loading is completed.

11. Start the datapump extract [ SOURCE ]

12. Start the replicat [ TARGET ]

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

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

 

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

Check the replicat status:

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.