Oracle GoldenGate Initial Load Techniques

In order to start using OGG replication, the source and target databases must be in sync. This means that the source data must be copied over to the target and remain in sync while changes are occurring in the source database.

In this article I’m going to discuss the options available to instantiate the target database with the data in sync, their pros and conns, and their pre-requisites.

From Oracle documentation and some MOS notes, it is recommended as a general rule to use native tools if available to instantiate the data in the target database. This works well if the source and target databases are the same, but if they are not the same then an initial load technique may be the best option.

So, why is a native tool recommended? Some of Oracle native tools like RMAN and expdp/impdp can be used to sync the source and target to a particular SCN. Afterwards the OGG replication is started after that SCN; so no chance of duplicates and/or collisions in these cases. In the case of initial load techniques, there are collisions that occur and as a result, there are several steps that must be executed in order.

General Steps for Performing an Initial-Load

1. The continuous standard extract for replication ( or change synchronization extract ) is configured and started; however the corresponding replicat is not started yet.

2. Next the initial load and replicat are configured and executed.

3. Once the intial load and replicat are done, they will stop.

4. The replicat associated with the original change synchronization extract is now started with the HANDLECOLLISIONS parameter set.

5. When the replicat gets past the point where the intial load/replicat stopped, the HANDLECOLLISIONS parameter is turned off.

Here’s a quick time diagram of the order of events.

>-----|-----------|-----------|-----------|-----------|-------->  ( time )
	Time 1		Time 2		Time 3		Time 4		Time 5

	Change		Initial		Intial		Change		Turn
	Sync		Load		Load		Sync		Off
	Extract		Started		Done		Replicat	HandleCollisions
	Started								Started

OGG is now up and running. But did you notice in step 4 that the change synchronization replicat will be applying the same changes that the initial load replicat has already done? This is because the change synchronization was configured to start at a point in time before the intial load technique was. This is necessary in heterogenous systems to ensure that no transactional data is not lost between the time when the intial load is done and when change synchronization is started. The purpose of the HANDLECOLLISIONS parameter is to keep the target data in sync with the source with the same/multiple changes coming in.

There are several different techniques of intial load techniques, each with their own advantages and disadvantages.

Here’s a list of some of the different initial load techniques available:

1 File to Replicat

2 Trail to Replicat

3 File to Database Utility

4 Direct Load

5 Direct Bulk Load to SQL*Loader

Before performing an intial load technique, the following prerequisites must be met.

1 Disable DDL Processing

2 Prepare Target Tables

– Data : the target tables must be empty
– Constraints : disabled on the target
– Triggers : disabled on the target
– Indexes : delete for performance ( they can be added back in after the initial load is complete )

3 Configure and start the MGR Process on both Source and Target

4 Create Data-Definitions File

– this is only necessary if source and target tables differ in structure

5 Create Change-Sync Groups

I have a series of blogs that compare the different techniques advantages and disadvantages as well as show how to perform them.

Visual Diagram of Initial-Load Techniques

Spreadsheet Comparing Initial-Load Techniques

4 thoughts on “Oracle GoldenGate Initial Load Techniques

  1. Pingback: GoldenGate Initial-Load File to Replicat | Rick Miners' Oracle Blog

  2. Pingback: GoldenGate Initial-Load Trail to Replicat | Rick Miners' Oracle Blog

  3. Pingback: GoldenGate Initial-Load Direct Load | Rick Miners' Oracle Blog

  4. Pingback: GoldenGate Initial-Load Trail to Replicat – Part 2 | Rick Miners' Oracle Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s