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:
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:
drop table scott.p2; drop table scott.sales2; drop table scott.p3;
create table scott.p2 (i number); create table scott.sales2 (i number); create table scott.p3 (i number);
insert into scott.p2 values (1); insert into scott.sales2 values (1); insert into scott.p3 values (1); commit;
- Insert one row into sales2 and only sales2 is replicated.
Arjen Visser October 29, 2013 14:53