Automatically add table back into replication after it is has been dropped

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
1 min read

Problem Description

A table is being dropped (drop table) every week, but the customer wants to automatically add it back to replication.

Normally, this is done with the PREPARE SCHEMA command. However, because the replication is currently set up and running with existing tables being replicated with the PREPARE TABLE command, it would be costly to re-initialize  the whole replication. Described here is a method to add PREPARE SCHEMA to the replication without affecting the current PREPARE TABLE(s) in the same schema:

Solution

For this example, the table in question is SCOTT.SALES2

  • On source database, update a record in the Dbvisit Replicate data dictionary to enable replication of the schema.

    UPDATE "DBVREP123"."DBRSUSER$" SET MINE_ENABLED = 'Y', MINE_OUT = 'Y', MINE_INHERIT = 'Y', MINE_DDL = 'Y' WHERE NAME = 'SCOTT'; commit;

  • On target target, insert a new record into the Dbvisit Replicate data dictionary to enable replication of the schema. Changes SCN range to 1-inifinity, dates to sysdate.

    INSERT INTO "DBVREP456"."DBRSAPPLY_DICT_SCHEMAS" (DDC_ID, MINE_PROCESS_NAME, APPLY_PROCESS_NAME, SCHEMA_NAME, INSTANTIATION_SCN, VALID_FROM_SCN, VALID_TO_SCN, CREATE_DATE, LAST_CHANGE_DATE) VALUES ('1', 'MINE', 'APPLY', 'SCOTT', '1', '1', '562949953421312', sysdate, sysdate); commit;

  • On the dbvrep console, execute the following. This will ensure only table sales2 is affected by this change:

    exclude create table scott.% include create table scott.sales2

  • Restart MINE and APPLY.

  • Test SQL:

  • Insert one row into sales2 and only sales2 is replicated.

Arjen Visser  October 29, 2013 14:53