How Oracle GoldenGate Replicat handles Foreign Key Constraints

According to the Oracle database documentation there are two types of constraints: DEFERRED and NOT DEFERRED. The first one can be evaluated at the and of the transaction, and the second one if validated immediately. On the other hand Oracle GoldenGate can handle the replication when the target uses both of them. Let’s find out how this works.

That convinced me to dig deeper here is a note from OGG documentation that DBOPTIONS DEFERREFCONST parameter allows to defer constraints that are declared as NOT DEFERRED:

You can use DEFERREFCONST instead of disabling the constraints on the target tables or setting them to DEFERRED. When used, DEFERREFCONST defers both DEFERABLE and NOT DEFERABLE constraints. DEFERREFCONST applies to every transaction that is processed by Replicat. DEFERREFCONST is valid for Oracle Database 12c, 11g (11.2.0.2), and later 11g R2 releases.

How come? Is the OGG Replicat handled other than SQL*Plus? Let’s find out how constraints are handled by Classic and Integrated Replicat and how does DBOPTIONS DEFERREFCONST work.

This blog post (like all others) is just written for educational purposes to understand how the internals work.

1. environment

As usual I have run my tests on the latest available versions of OGG:

• 11.2.1.0.33
• 12.1.2.1.170117
• 12.2.0.2.2
• 12.3.0.1.2

And Oracle Database:

• 11.2.0.4 + PSU 180116
• 12.1.0.2 + PSU 180116
• 12.2.0.1 + RU 180116

All work on Linux64 platform.

2. Test schemas

I have tested a replication for 3 tables with FK.

On the source site all FK are created with option DEFERRABLE INITIALLY DEFERRED.

CREATE TABLE adam.ref0(
  pk1 INT NOT NULL
);
ALTER TABLE adam.ref0 ADD CONSTRAINT ref0pk PRIMARY KEY(pk1);

--PK, UI, (pk1) is PK
CREATE TABLE adam.tab0(
  pk1 INT NOT NULL,
  fk1 INT NOT NULL
);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab0 ADD CONSTRAINT tab0fk1 FOREIGN KEY(fk1) references adam.ref0(pk1) DEFERRABLE INITIALLY DEFERRED;

--PK, UI, (pk1) is PK
CREATE TABLE adam.tab1(
  pk1 INT NOT NULL,
  fk1 INT NOT NULL
);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab1 ADD CONSTRAINT tab1fk1 FOREIGN KEY(fk1) references adam.ref0(pk1) DEFERRABLE INITIALLY DEFERRED;

--PK, UI, (pk1) is PK
CREATE TABLE adam.tab2(
  pk1 INT NOT NULL,
  fk1 INT NOT NULL
);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2pk PRIMARY KEY(pk1);
ALTER TABLE adam.tab2 ADD CONSTRAINT tab2fk1 FOREIGN KEY(fk1) references adam.ref0(pk1) DEFERRABLE INITIALLY DEFERRED;

On the target site all 3 tables had 3 different settings:

• tab0: DEFERRABLE INITIALLY IMMEDIATE
• tab1: DEFERRABLE INITIALLY DEFERRED
• tab2: NOT DEFERRABLE

CREATE TABLE anna.ref0(
  pk1 INT NOT NULL
);
ALTER TABLE anna.ref0 ADD CONSTRAINT ref1pk PRIMARY KEY(pk1);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.ref0 TO GGADMIN;

CREATE TABLE anna.tab0(
  pk1 INT NULL,
  fk1 INT NULL
);
ALTER TABLE anna.tab0 ADD CONSTRAINT tab0pk PRIMARY KEY(pk1);
ALTER TABLE anna.tab0 ADD CONSTRAINT tab0fk1 FOREIGN KEY(fk1) references anna.ref0(pk1) DEFERRABLE INITIALLY IMMEDIATE;
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.tab0 TO GGADMIN;

CREATE TABLE anna.tab1(
  pk1 INT NULL,
  fk1 INT NULL
);
ALTER TABLE anna.tab1 ADD CONSTRAINT tab1pk PRIMARY KEY(pk1);
ALTER TABLE anna.tab1 ADD CONSTRAINT tab1fk1 FOREIGN KEY(fk1) references anna.ref0(pk1) DEFERRABLE INITIALLY DEFERRED;
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.tab1 TO GGADMIN;

CREATE TABLE anna.tab2(
  pk1 INT NULL,
  fk1 INT NULL
);
ALTER TABLE anna.tab2 ADD CONSTRAINT tab2pk PRIMARY KEY(pk1);
ALTER TABLE anna.tab2 ADD CONSTRAINT tab2fk1 FOREIGN KEY(fk1) references anna.ref0(pk1) NOT DEFERRABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.tab2 TO GGADMIN;

3. Test scenarios

The trick was to make 2 inserts: one to the fact table (tabX) and one to the dictionary table (dict0) but in the opposite order:

insert into adam.tab0 values (10, 10);
insert into adam.ref0 values (10);
commit;

insert into adam.tab1 values (11, 11);
insert into adam.ref0 values (11);
commit;

insert into adam.tab2 values (12, 12);
insert into adam.ref0 values (12);
commit;

This INSERT operation was possible on the source database (because the constraints were set to DEFERRABLE INITIALLY DEFERRED).

The question is if the transaction could be replicated to the target site.

4. Results

The results were the same on all OGG versions and database versions.

4.1. Integrated Replicat

The Integrated Replicat was able to handle the INSERT operations for all scenarios. The database behaves like the FOREIGN KEY constraint was set to DEFERRABLE INITIALLY DEFERRED in all cases.

4.2. Classic Replicat without DBOPTIONS DEFERREFCONST

The Replicat process is able to handle only tab1, which uses the DEFERRABLE INITIALLY DEFERRED option. For tab0 and tab2 it is not able to replicate the transaction because of CONSTRAINT violation.

Since the order of the INSERT commands is fixed in the transaction in this case the transaction can not be applied for tab0 and tab2.

4.3. Classic Replicat with DBOPTIONS DEFERREFCONST

With the DBOPTIONS DEFERREFCONST the Classic Replicat is able to succeed in all test cases. So this is the same behavior the documentation was talking about.

Let’s investigate why and trace the SQL commands.

The SQL command that is replicated show a special hint RESTRICT_ALL_REF_CONS:

INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ANNA"."TAB0" ("PK1","FK1") VALUES (:a0,:a1);
INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ANNA"."REF0" ("PK1") VALUES (:a0);

This hint is undocumented. It does not work for user written SQL code run by SQL*Plus. This hint is intended to being used by OGG only.

It seems that this hint changes the type of all constraints to DEFERRED even if they are declared as NOT DEFERRED. And they are validated at the end of the transaction. Further test and Oracle Note 1644272.1 tells that using of this option requires the ENABLE_GOLDENGATE_REPLICATION setting.

The setting ENABLE_GOLDENGATE_REPLICATION requires OGG license. So, the only SQL Replicat that is able to defer constraints that are declared as NOT DEFERRED is OGG. And the functionality which allows to defer all constraints requires special OGG settings.

5. The RESTRICT_ALL_REF_CONS hint

The database is somehow able to detect the client type connection and allow it only for OGG. After some investigation I came to the conclusion that using the  RESTRICT_ALL_REF_CONS hint is possible if:

1. The PROGRAM name is “replicat”
2. The MODULE name is “OGG-REPL-OPEN_DATA_SOURCE”
3. The dbms_xstream_gg.set_gg_session is run

Of course you need to have the OGG license and set the ENABLE_GOLDENGATE_REPLICATION parameter anyway.

-bash-4.2$ ln -s $ORACLE_HOME/bin/sqlplus replicat
-bash-4.2$ replicat ggadmin/ggpwd

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 21 23:18:35 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE(MODULE_NAME=>'OGG-REPL-OPEN_DATA_SOURCE', ACTION_NAME=>'');

PL/SQL procedure successfully completed.

SQL> begin dbms_xstream_gg.set_gg_session(); end;
 2   /
PL/SQL procedure successfully completed.

SQL> INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ANNA"."TAB0" ("PK1","FK1") VALUES (99,99);

1 row created.

SQL> INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ANNA"."REF0" ("PK1") VALUES (99);

1 row created.

SQL> commit;

Commit complete.

I do not understand all the trouble with the program and module name since the OGG option is needed anyway.

6. Conclusions

My main conclusions are:

1. The Integrated Replicat does not care about (NOT) DEFERRABLE claues – it works in all cases,
2. The Classic Replicat works in all cases only if the DBOPTIONS DEFERREFCONST option is used,
3. The Classic Replicat is not able to DEFER constraint declared as DEFERRABLE INITIALLY IMMEDIATE without the DBOPTIONS DEFERREFCONST option
4. The database allows only for the OGG Replicat process to use the RESTRICT_ALL_REF_CONS hint