OGG: Reliable initial load using nothing but GoldenGate

Performing standard initial load that involves transfering of data from extract to remote replicat by TCP is slow and can cause serious performance decrease or even deadlocks on some source systems. For MSSQL especially extract will block a table while waiting for replicat to apply inserts on target system. To break this pityful dependency use the following method: unload all data from source DB to external files, move these files to the target system, then to load it on target system.
First create an ordinal extract ex1 that will cover our back and capture all changes to source tables that will occur during our inital load process (extracting and loading). Start it.
Create an extract that will unload all data to external files.

GGSCI> create extract ex2 sourceistable

with the `extfile` directive in `ex2.prm` file

sourceistable
extract ex2
sourcedb GGODBC, userid ggate, password !@cGate34
discardfile C:\GoldenGate\gg11_2_1_0_18\dirrpt\ex2.dsc, purge
extfile C:\GoldenGate\gg11_2_1_0_18\dirdat\II, maxfiles 100, megabytes 1024
table dbo.Header;

Start extract ex2, wait it to finish unloading data.
Resulting external files will have the same format as ordinal trail files.
Move files to the target system with the help of some magic, for ex. WinScp.
On target system create a replicat with exttrail option

GGSCI> create replicat r2 exttrail /opt/oracle/ggate/dirdat/II

This will make replicat treat our files like ordinal trails. Start it and wait some time until it loads all data, so it does not increase Log Read Checkpoint (quiery it by issuing `info r2` command).
Stop the replicat.
Create an ordinal replicat operating with handlecollision option. Run it. Wait until it reaches the current time.
That’s it, we’ve just performed a reliable initial load of target system using external files.

Leave a Reply

Your email address will not be published. Required fields are marked *