In this tutorial, we will explain how to do schema replication in goldengate. Schema replication means, replicating all the tables of a schema.

 

1. add supplemental for all tables of a schema [ SOURCE ]

add trandata <schema_name>.*

2. Prepare extract file [ SOURCE ]

3. Prepare extract pump parameter file [ SOURCE ]

4. Add extract process [SOURCE]

5. Add the extract trail [SOURCE]

6. Add the extract pump process [SOURCE]

7. Add remote trail [SOURCE]

8. Prepare replicat 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 ]

10.Start the extract and pump process [ SOURCE ]

11. Initial loading:

Now export this schema using expdp in the source database and import in the target database.

12. Start the replicat: [ SOURCE ]

Use LAG command to monitor the lag at replicat.

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

13. Remove handlecollison parameter and restart replicat[ TARGET ]

goldengate Schema replication has been completed.