Return
-
Title
How to sync a Target table using Oracle Export/Import with FLASHBACK_SCN option and reconcile. -
Description
Starting with Oracle 9i, it is possible to take an Export of a database which is consistent from a specified SCN.This allows for it's use in SharePlex to resync a Target table.Especially when the Source table is constantly being accessed for changes by users.The procedure does involve using the SharePlex Reconcile command.A limitation of this procedure is that the table has to replicate via a dedicated named Post queue.The named Post queue should not hold data for any other table because Reconcile will remove all committed transactions less than or equal to the reconcile SCN-1,regardless of the table it belongs to. -
Resolution
The following are the steps involved:1. Ensure that the table is replicated via a dedicated named Post queue and that queue does not hold data for any other table.2. Flush from the Source to the Target.sp_ctrl> flush o.sourcesid queue <queue_name>3. Begin an Export of the Source table with FLASHBACK_SCN option in table mode.4. After the Export is finished on the Source, and after Post stops due to flush on the Target,compare the Source table to the Target table to make sure both are the same structure (i.e. no DDL changes have occurred).5. Import the data into the Target table.6. Disable any triggers or constraints (except PK or UI type) on Target table.7. Run Reconcile on the Post queue using SCN option and use the same export flashback SCN+1.8. Start Post on the Target. -
Additional Information
For details on FLASHBACK_SCN option, see Oracle documentation. For details on Reconcile, see SharePlex Reference and Administrator Guides.