X

Best Practices from Oracle Development's A‑Team

Database Objects Creation Sequence Could Impact Oracle GoldenGate DDL Replication

Introduction

The way in which a schema is set up and objects are created in the source and target environments could impact Oracle GoldenGate DDL replication. This is illustrated by the example in this document.

Main Article

For Oracle GoldenGate (OGG) DDL replication to work correctly, the source and target schemas should be identical. Sometimes even if the sequence of operations used to create the schemas are different, it could impact DDL replication. For example the Primary Constraint and Unique Index on a table can be created in different ways. In scenario1, we create a Unique Index and Primary Constraint using that Unique Index in separate SQL steps as shown below:

CREATE TABLE "TABLE_X" ("DATA1" NUMBER(22, 0) NOT NULL ENABLE, "DATA2" VARCHAR2(4000), "DATA3" VARCHAR2(32) NOT NULL ENABLE, "DATA4" VARCHAR2(8) NOT NULL ENABLE, "DATA5"  VARCHAR2(32) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "DATA_TABLESPACE" LOGGING NOCOMPRESS
/
CREATE UNIQUE INDEX "TABLE_X_PK_X1" ON "TABLE_X" ("DATA5" , "DATA4" , "DATA3" , "DATA1" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE  "INDEX_TABLESPACE" LOGGING
/
ALTER TABLE "TABLE_X" ADD  CONSTRAINT "TABLE_X_PK_X1" PRIMARY KEY("DATA5" , "DATA4" , "DATA3" , "DATA1" ) USING INDEX
/

 

In scenario 2, we create a Unique Index and Primary Constraint using that Unique Index in the same SQL step as shown below:

CREATE TABLE "TABLE_X" ("DATA1" NUMBER(22, 0) NOT NULL ENABLE, "DATA2" VARCHAR2(4000), "DATA3" VARCHAR2(32) NOT NULL ENABLE, "DATA4" VARCHAR2(8) NOT NULL ENABLE, "DATA5"  VARCHAR2(32) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "DATA_TABLESPACE" LOGGING NOCOMPRESS
/
ALTER TABLE "TABLE_X" ADD  CONSTRAINT "TABLE_X_PK_X1" PRIMARY KEY("DATA5" , "DATA4" , "DATA3" , "DATA1" ) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE  "INDEX_TABLESPACE" LOGGING
/

Now in both scenarios, we will try to execute the same DDL statement to drop primary constraint as shown below:

ALTER TABLE "TABLE_X" DROP CONSTRAINT "TABLE_X_PK_X1" CASCADE
/

In  scenario1, we see that the Primary Key Constraint is dropped, but the Unique Index is not dropped

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME = 'TABLE_X_PK_X1';

INDEX_NAME
------------------------------
TABLE_X_PK_X1

SQL>

In scenario 2, we see that both the primary key Constraint and the Unique Index are dropped.

SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME = 'TABLE_X_PK_X1';

no rows selected

SQL>

If we follow scenario 1 in source database and scenario 2 in target database, DDL replication will have issues when processing the Drop Constraint statement. In one environment Unique index is not dropped which could give error “Index name already exists” while trying to recreate the index.

Summary

Care should be exercised while crating objects in source and target databases when Oracle GoldenGate DDL Replication is used.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha