I will give a brief overview of the scenario before my doubts.
We have a very specific scenario where we want to exclude the changes made by a specific user to the database during replication to another database. The changes which are made by other users to the same tables must be replicated . It goes like this
- Sync primary database to standby database till SCN XXX
- Stop dataguard synchronization at point XXX and covert it to snapshot standby
- Now the changes made by User A must be replicated to snapshot standby and changes made by User B must not be replicated till a particular point in time .Lets call it SCN YYY
- After the snapshot standby reaches point YYY with changes from only User A , the replication should be stopped for some batches.
- After the batch run is complete convert the snapshot standby back to physical standby and start data guard again
This is the scenario plus my plan to use snapshot standby for a part of the solution. For the second part of the solution to replicate user specific changes I am planning to user ORACLE GOLDENGATE with EXCLUDE USER option in extract and with DDL changes enabled .
I have done a basic test for the solution mentioned above for a small table and it is working as expected. The only issue being if there are new rows inserted into table by USER B during goldengate replication and user A tries to update the row the replicate fails since the row is not yet present which is understandable and expected.
CONCERNS:
I have tested the solution for single table but the database to be implemented is in order of several GB's (close to 800 GB) and the during the duration of goldengate replication, redo generation will be in several GB's.
For the solution I plan to use multiple extracts and replicats in order to reduce the replication time . I am not aware of the load on the database it might cause in the production database , will it cause significant load on the database other than the load on CPU and disk for mining the archive logs for extract process?
I plan to start the extract on the primary database about 5 minutes before the SCN XXX where the user specific replication must start. I am yet to validate if this approach will work but logically I don't see any issues from the goldengate side . The idea behind this is to reduce the runtime of extracts in primary database server.
After I convert the snapshot standby to physical standby , the checkpoint information of goldengate replicats in checkpoint tables are lost and every time i do this i had to manually "Alter replicate rep1 extseq somevalue extrbn somevalue" to create checkpoint manually before starting the replicat again. Is this right way to go ?
I know these are lot of questions but It would be helpful if you can validate my solution and share your opinion on it and if there is a better approach to solve this problem rather than using goldengate ?
Thanks