Prevent Create Index from Replication when DDL Replication Is Enabled

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

Problem Description

DDL replication is enabled on schema and table level. Therefore, disabling DDL replication on schema level will not prevent create index from being replicated. We have to disable DDL replication on the table to stop create index from being replicated.

Example 

  • Replication is setup on test1 schema with temp1 and temp2 tables.

  • DDL replication is ON.

    dbvrep> list prepare List of prepared schemas: TEST1 (DDL) List of prepared tables: DBVREP.DBRSCOMMON_HEARTBEAT -> DBVREP (schema rename) DBVREP.DBRSOBJ$ -> DBVREP (schema rename) DBVREP.DBRSUSER$ -> DBVREP (schema rename) TEST1.TEMP1 (DDL) TEST1.TEMP2 (DDL) dbvrep>

  • As we do not want to replicate create index command on temp1 table, we will disable DDL replication on the temp1 table and restart MINE:

    dbvrep> ENGINE SETUP MINE TABLE TEST1.TEMP1 MINE_DDL NO dbvrep> shutdown mine Dbvisit Replicate mine process shutting down. ***** MINE RESTARTED **** dbvrep> list prepare List of prepared schemas: TEST1 (DDL) List of prepared tables: DBVREP.DBRSCOMMON_HEARTBEAT -> DBVREP (schema rename) DBVREP.DBRSOBJ$ -> DBVREP (schema rename) DBVREP.DBRSUSER$ -> DBVREP (schema rename) TEST1.TEMP1 TEST1.TEMP2 (DDL) dbvrep>

  • On Source, create an index:

    SQL> conn test1/test1 Connected. SQL> create index temp1_name on temp1(name); Index created. SQL> select INDEX_NAME from USER_INDEXES where TABLE_NAME='TEMP1'; INDEX_NAME TEMP1_NAME SYS_C0048410< SQL>

  • On Target, confirm the index is not replicated:

As can be seen, temp1_name index is not replicated.

Krupesh Desai October 06, 2014 07:11