A table can be unprepared/re-instantiated even if there is a conflict on that table. The recommended steps to do so follow below. Note: Tested with Dbvisit Replicate 2.7.11:
Shutdown the APPLY process and
unpreparethe table from the replication console:
dbvrep > shutdown apply dbvrep> UNPREPARE OFFLINE TABLE <schema>.<table>
- Drop tables on the target that were removed from replication. Note: You may need to drop/disable FK constraints on the target. These may need to be recreated at the end of this procedure.
On the target DB:
SQL> truncate table <schema>.<table>;
- On the Source from the console:
dbvrep> ENGINE SUPPLEMENTAL LOGGING TABLE <schema>.<table> ENABLE PRIMARY KEY dbvrep> ENGINE WAIT_SCN_FLIP
On the source DB:
SQL> lock table <owner.table> in share mode; COLUMN instantiate_scn FORMAT A15 COLUMN current_timestamp FORMAT A40 SELECT 'instantiateScn' as instantiate_scn, current_scn, current_timestamp FROM v$database;
This outputs a SCN such as 79359416. Use this SCN from the source DB in the following
export script/dp_network linksteps.
Release the Lock on the source DB:
- In Console:
dbvrep> PREPARE AS OF <scn> TABLE <schema>.<table>
dbvrep> PREPARE AS OF 79359416 table scott.emp
Note: If you get any errors, restart the console and retry the command.
- Instantiate the tables via export/import or data pump network link. For each table, Datapump via the network. Alternatively, use
expdp/impdb. Note: Be sure to use the
flashback_scnfound in step three.
Note: In this example the
<DBLINK>database link connecting to the SOURCE database pre-exisited in the target system. Therefore the
network_linkoption could be used.
On source server:
$ impdp SYSTEM/xxx@<TargetDB> table_exists_action=TRUNCATE network_link=<DBLINK\_toSOURCE> directory=DATA_PUMP_DIR flashback_scn=<scn> tables=<schema.table> logfile=><log\_filename>.log JOB_NAME=DP_TEST_0001
- Recreate/reenable any PK/FK constraints that were dropped from the target DB.
- Restart APPLY.
Donna Zehl February 25, 2015 08:18