Return
-
Title
How to sync a big out of sync (OOS ) table with no downtime on both Source and Target -
Description
How to sync a big OOS table with no downtime on both Source and Target Database?
-
Cause
N/A -
Resolution
Pre-requirement:1. It should be a one way SharePlex replication and the Foreign Key Constraints for the replicated table should be turned off on the Target.2. There shouldn't be any structural change on the table during the resync.
Procedure:
Note: Replace all the XXXX below with the new table name you are going to create and replace all in the procedure with real Post Queue names.
1. On target, create a new table XXXX with the same structure as the table you want to sync. For this procedure, we will use TRACKER as the table name that we want to sync
2. On Source, make a copy of the current config file and then add new line for the table XXXX in the copy of the config file with its own Post queue.For example, you current have following entry for TRACKER table in the active config file:Source_schema.TRACKER target_schema.TRACKER target_hostname:Queue1@o.target_sidAfter adding the table XXXX, it looks like below:Source_schema.TRACKER target_schema.TRACKER target_hostname:Queue1@o.target_sidSource_schema.TRACKER target_schema.XXXX target_hostname:Queue2@o.target_sid
3. On target, stop all Post (sp_ctrl>stop post)Once all the Post queues are stopped by user, start the Post queues one by one using the command below.Please replace below with real Post queue name.sp_ctrl>start post queue4. On source, activate the copy of the config file you made from step 2 above with nolock option. Replace with the new config file name before running it.
sp_ctrl>activate config nolock
Note: you should see the new Post queue named Queue2 created on Target and it is in stopped state. Please do not start this new Post queue until you reach step 10 below.
5. Get current SCN from Source database
6. Begin an Expdp for the Source table (TRACKER) with FLASHBACK_SCN option in table mode
7. On Target, run impdp to import the data into the Target table XXXX using FLASHBACK_SCN option
8. After the impdp is complete, disable any triggers, constraints (except PK or U type) for table XXXX on Target
9. On Target, run reconcile command for the Post queue Queue2 using SCN option and use the same export flashback SCN+1.
An example for reconcile command, assume the scn you used for expdp is 100 , then scn + 1 Is 101
sp_ctrl(sysB)> reconcile queue Queue2 for o.oraA-o.oraB scn 101
Note: oraA above is your source sid and oraB above is your Target sid. Please note that above is just an example, you need to replace 100 above with real scn you used for expdp earlier.
10. After the reconcile command complete, start Post for queue Queue2 (sp_ctrl>start post queue Queue2)
11. Wait until the backlog in Post queue Queue2 is cleared out and you are ready for renaming the table, you can follow steps below for renaming the table.
a. Run flush command on Source for all Post queues as below:
On Source:
Note: please replace oraA below with your real Source sid first before running it,
sp_ctrl>flush o.oraA
b. Wait all Post queues stopped by flush on Target
c. On Target, rename table TRACKER to a different name (here I put YYYY as an example)
d. Then rename target_schema.XXXX to target_schema.TRACKER
e. Rename table YYYY to XXXX
13. Now you can start all Post queues. Or if you want, you can leave the Post queue Queue2 in stopped state until you get chance to remove this Post queue from the replication.
Now, the table is back in sync. Please contact SharePlex Support if you need help to remove the table XXXX from replication and delete the Post queue Queue2.