Post

1 follower Follow
0
Avatar

DDL replication: Schema level Vs Table level

Question

How does DDL replication on table level differ from Schema level DDL replication?

Answer

DDL replication acts differently for schema and tables. When DDL replication is enabled on a schema ( by specifying schema names in setup wizard), the MINE process replicates the following:

  • Changes in table structure.
  • Drop table.
  • Create table ( including CTAS)

Therefore, when DDL replication is enabled on schema level, you can drop and recreate tables on source. Dbvisit Replicate will sync the target without any user intervention.

DDL replication on table level only replicates changes in table structures to the target. When table names are specified in the setup wizard (separated by comma) and DDL replication is enabled, it will replicate "drop table" statements. However, recreating the same table (using create table or CTAS) will not ensure it is replicated. In such a scenario you have to add the table to the replication process manually using the prepare command. The correct steps would be:

  • Drop the table from source. The table will also be dropped on the target. The table will be removed from replication.
  • Re-create table on source.
  • Pause APPLY.
  • Add the table in replication. For example:
    prepare table scott.emp noddl rename to scott1.emp1
  • Load data in target table to sync it with the source. You can use the ENGINE PREPARE_DP WRITE command to create an import/export script)
  • Resume APPLY.
Krupesh Desai Answered

Please sign in to leave a comment.