The requirement was not to over load the production system at any point.
Thought about Oracle Active Data Guard but the target database also will be used for updates and so had to rule this option out.
The best option which came to be after doing a complete research on the replication what Oracle offers was the Downstream Setup.
Very simple and very promising to me.
A picture is better than a detailed explanation
Here is a simple over view of what and how it can be done.
- Create a streams administrator user on both the databases.
- Create a database link between both the databases.
- Setting parameter for Downstream archeving.
- Creating standby redo-logs on the Downstream site to receive redo data from Source
- Get Source and Downstream database to archive-log mode
- Setting up the source schema
- Creating the streams queue on the downstream site
- Creating the apply process at the downstream site
- Creating the capture process at the downstream site
- Set capture for real-time captuing of changes
- Add rules to instruct the capture process, what to capture
- Instantiating the to be replicated objects.
- Start the capture process
- Start the apply process
(Detailed implementation can be found at Oracle Note 753158.1)
