We're here to help!

UNPREPARE/re-PREPARE Table on Target in Running Replication

Follow

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. 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 PREPARE & export script/dp_network link steps.

    Release the Lock on the source DB:

    SQL> ROLLBACK;
  4. 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.

  5. 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
  6. Recreate/reenable any PK/FK constraints that were dropped from the target DB.
  7. Restart APPLY.

Donna Zehl February 25, 2015 08:18

Have more questions? Submit a request

Comments

  • Avatar
    Kamal

    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.