Allow DDL DROP and CREATE for Individual Tables Which Are Replicated on Table Level (not Schema Level)

Problem Description

When a table is replicated on an individual table level (with the command prepare table owner.table_name) and not on a schema level, then when the table is dropped with DDL, it is also removed from the replication.

This means that if the table is created again with DDL, the table must be prepared again before any DML or DDL is replicated. However, the table must exist before it is prepared. This means that the DDL to create the table is not replicated because replication happens before the table is newly prepared.

If the replication is done on a schema level (with command prepare schema schema_name) then this is not an issue for new tables. This is because DDL is replicated for all new tables (currently Oracle to Oracle only).

If the create DDL should also be replicated for individual tables that are replicated on a table level (rather than a schema level) the following workaround can be used:

Steps Performed

1. Prepare the existing table. Note: that table must exist on both source and target. In this example the schema is avi4and the table is called test4:

   dbvrep> prepare table avi4.test4

2. Run the following dbvrep commands. This will turn on DDL at a schema level. Note: the schema name in this example is avi4:

   dbvrep> PROCESS SETUP MINE SCHEMA avi4 MINE_DDL YES dbvrep> PROCESS SETUP MINE SCHEMA avi4 MINE_OUT YES dbvrep> PROCESS SETUP MINE SCHEMA avi4 MINE_ENABLED YES

3. Shutdown the MINE engine. MINE has to be restarted for these settings to take affect.

   dbvrep> shutdown mine

4. Restart the MINE engine with the run script provided on the source system.

5. Now the table can be dropped and re-created with DDL and this will be replicated to the target database.

6. Note: All new create table DDL will be replicated to the target database. Use the EXCLUDE CREATE TABLE with wildcard option to exclude any specific tables.

Arjen Visser December 11, 2014 23:31