Starting with Oracle 9i, it is possible to take an Export of a database which is consistent as of a specified SCN. This opens door for its use in conjunction with Shareplex to resync a target database, especially when the source database is constantly being accessed for changes by the users. The procedure does involve using Shareplex reconcile command.
The following steps are involved:
1. Flush from the Source to the target (i.e. on source at sp_ctrl> flush o.sourcesid ). This establishes a reference point and stops Post on the target, whereby any activity on the source (after the flush is issued) is queued on the Post queue(s) on the target.
2. Begin an Export of the Source database with FLASHBACK_SCN option in FULL or SCHEMAS mode as appropriate depending on whether the full database export is performed or only export of specific schemas is performed.
3. After the Export is finished on the Source, and after Post stops due to flush on the Target, compare the Source tables to the Target tables to make sure both are the same structure (no DDL changes have occurred).
4. Import the data into the Target database.
5. Disable any triggers or constraints (except PK or U type) on target tables.
6. Run reconcile on the Post queue(s) using SCN option and use the same SCN+1 as was used in step 2.
6a. If the FULL mode is used, and if the SharePlex schema or user is same on source and target, then you may also want to run cleanup.sql script on the target prior to starting Post so as to clean up the SharePlex schema on target whereby it does not inherit any values from source schema. Inheriting the values from source by SharePlex schema can interfere with replication. The script is located in /proddir/bin directory where proddir refers to SharePlex Product directory.
7. Start Post on target.
It is strongly recommend testing out the procedure, or at least the scaled down part that does SCHEMAS sync, since the underlying concepts are same for both FULL and SCHEMAS mode. The procedure is a bit involved as opposed to running a “copy using”, albeit it is much faster.
