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.