We're here to help!

UNPREPARE/re-PREPARE Table on Target in Running Replication


Problem Description

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:

Steps Performed
  1. Shutdown the APPLY process and unprepare the table from the replication console:

    dbvrep > shutdown apply
    dbvrep> UNPREPARE OFFLINE TABLE <schema>.<table>
  2. 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>;
  3. Restart APPLY
  4. On the Source from the console:
    dbvrep>PAUSE MINE
    dbvrep>PAUSE APPLY
  5. 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 PREPARE & export script/dp_network link steps.

  6. In Console:
    dbvrep> PREPARE AS OF <scn> TABLE <schema>.<table>

    For example:

    dbvrep> PREPARE AS OF 79359416 table scott.emp 

    Note: If you get any errors, restart the console and retry the command.

  7. dbvrep>RESUME MINE
  8. Instantiate the tables via export/import or data pump network link. For each table, Datapump via the network. Alternatively, use exp/imp or expdp/impdb. Note: Be sure to use the flashback_scn found 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_link option 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
  9. Recreate/reenable any PK/FK constraints that were dropped from the target DB.
  10. dbvrep>RESUME APPLY

Donna Zehl February 25, 2015 08:18

Have more questions? Submit a request


  • Avatar

    Hi ,
    we need to increase the performance in apply and mine process.Client expect more performance than current status of the data apply process.
    Actually we need exact real time data apply replication.
    Is there any way to enhance performance in data replication.

  • Avatar
    Trangbui Dba

    Thanks. It's very helpful. Further more, these steps work in case schema name is the same b/w source and target. If schema name of source is different from schema name of target DB, conflict will happen. Inserting into SchemaNameSource.TableName on Target will cause error like that "ORA-00942: table or view does not exist".

    Could you please help this case?