We're here to help!

Prevent Create Index from Replication when DDL Replication Is Enabled

Follow
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:

    SQL> conn test1/test1
    Connected.
    
    SQL> select INDEX_NAME from USER_INDEXES where TABLE_NAME='TEMP1';
    INDEX_NAME
    SYS_C0045038
    
    SQL>

As can be seen, temp1_name index is not replicated.

Krupesh Desai October 06, 2014 07:11

Have more questions? Submit a request

Comments