According to the documentation it is not allowed to use NOSCHEDULINGCOLS with ADD TRANDATA when you would use Integrated Replicat. Why would Oracle care for that? Let’s find out what can happen if you don’t apply the rules.
This post is actually only about UPDATE operations and how they are affected by FOREIGN KEY and UNIQUE KEY supplemental logging in the Oracle database. Those two logging operations are covered by default by OGG and can be disabled using the NOSCHEDULINGCOLS option of ADD (SCHEMA)TRANDATA command. I have made a summary about the ADD TRANDATA command in one of my blog posts and there is also a great description about supplementary log on Julian Dyke’s blog.
We do not care about INSERT and DELETE operations. Insert operation always cause all column values to be written to the redo log. And delete operations always cause all column values also to be written to the redo log (as part of the UNDO). So not matter which options you choose – values of all columns will be available for OGG.
This topic is important when you have a constraint (UI or FK) both on the source and the target and you are dealing with UPDATE’s.
1. Supplemental Logging
The ADD TRANDATA command adds supplemental logging to the database for the chosen table. Every DML operation which affects such table will write some additional information in the redo log beside the essential information needed for it’s operation.
A typical unconditional supplementary logging (used for Classic Replicat) would include the PK to every UPDATE operation.
In addition to that we also can have conditional supplemental logging: for unique constraints (UI) and foreign keys (FK).
2. Test environment
I have used for testing 3 environments:
- OGG 12.1.2.1.170117 + Oracle 12.1.0.2 + PSU 12.1.0.2.180116,
- OGG 12.2.0.2.2 + Oracle 12.1.0.2 + PSU 12.1.0.2.180116,
- OGG 12.3.0.1.2 + DB 12.2.0.1 + RU 12.2.0.1.180116
All on Linux64
6 test cases have been used here with Integrated Replicat:
- OGG 12.1 with ADD TRANDATA
- OGG 12.1 with ADD TRANDATA NOSCHEDULINGCOLS
- OGG 12.2 with ADD TRANDATA
- OGG 12.2 with ADD TRANDATA NOSCHEDULINGCOLS
- OGG 12.3 with ADD TRANDATA
- OGG 12.3 with ADD TRANDATA NOSCHEDULINGCOLS
3. Unique key constraint
Let’s make a test to see what happens if we play around with the Unique Index constraint. The idea is to modify part of the Unique Constraint so that the DML operation would not contain the whole part of the key when NOSCHEDULINGCOLS is being used. This would lead to a situation when the Replicat can not tell if other DML operations refer to the same key or not.
Source table is:
CREATE TABLE adam.TAB( PK1 numeric not null, UI1 numeric not null, UI2 numeric not null ); ALTER TABLE adam.TAB ADD CONSTRAINT TAB_PK PRIMARY KEY(PK1); ALTER TABLE adam.TAB ADD CONSTRAINT TAB_UI UNIQUE(UI1, UI2);
Target table is:
CREATE TABLE anna.TAB(
PK1 numeric not null,
UI1 numeric not null,
UI2 numeric not null,
SRC_SCN numeric not null,
DST_SID numeric default sys_context('USERENV','SID') null,
DST_SESSSIONID numeric default sys_context('USERENV','SESSIONID') null,
DST_SEQ numeric GENERATED BY DEFAULT AS IDENTITY (START WITH 1) not null
);
ALTER TABLE anna.TAB ADD CONSTRAINT TAB_PK PRIMARY KEY(PK1);
ALTER TABLE anna.TAB ADD CONSTRAINT TAB_UI UNIQUE(UI1, UI2);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.TAB TO ggadmin;
The replicat param file has:
MAP adam.TAB, TARGET anna.TAB, COLMAP(PK1 = PK1, UI1 = UI1, UI2 = UI2, SRC_SCN = @GETENV('TRANSACTION','CSN'));
The test scenario is:
BEGIN
FOR x IN 0..99 LOOP
INSERT INTO adam.TAB VALUES (x * 2 + 1, x * 2 + 1, x * 2 + 1);
COMMIT;
UPDATE adam.TAB SET UI1 = x * 2 + 2 WHERE PK1 = x * 2 + 1;
COMMIT;
UPDATE adam.TAB SET UI1 = x * 2 + 3 WHERE PK1 = x * 2 + 1;
COMMIT;
INSERT INTO adam.TAB VALUES (x * 2 + 2, x * 2 + 2, x * 2 + 1);
COMMIT;
END LOOP;
END;
The following image shows what I am running. The colors represent which keys for the constraint the DML operation refers to.
The idea is to create a situation which is not possible to repair when the order of transactions has been changed. If certain transactions have been skipped they can no longer be applied because of a constraint.
If we run first:
INSERT INTO adam.TAB VALUES (1, 1, 1);
and then:
INSERT INTO adam.TAB VALUES (2, 2, 1);
It is not possible to run any more:
UPDATE adam.TAB SET UI1 = 2 WHERE PK1 = 1; UPDATE adam.TAB SET UI1 = 3 WHERE PK1 = 1;
The constraint will disallow that.
So it is important for the Replicat to look at the Unique Constraint and mind the uniqueness to be able to assign transactions that refer to the same rows to the same process.
4. Unique key constraint – results
4.1. OGG 12.1 + ADD TRANDATA
This works, but Integrated Replicat fallbacks to Classic Replicat multiple times with a warning:
2018-03-04 23:24:25 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26799: unique constraint ANNA.TAB_UI violated for table ANNA.TAB with column values ("UI1", "UI2") = (2, 1)
ORA-00001: unique constraint (.) violated) while processing the record at SEQNO 6, RBA 1887 in Integrated mode. REPLICAT will retry in Direct mode.
The ggserr.log is spoiled with a lot of warnings. A warning appears for every single transaction and the log can become quite huge very fast..
4.2. OGG 12.1 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with a message:
2018-03-04 23:26:26 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 7, RBA 1611 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:26:26 ERROR OGG-00870 Oracle GoldenGate Delivery for Oracle, repl.prm: Database error 2091 (ORA-02091: transaction rolled back ORA-00001: unique constraint (ANNA.TAB_UI) violated SQL BEGIN dbms_apply_adm.delete_error(:1); END;). 2018-03-04 23:26:26 ERROR OGG-00665 Oracle GoldenGate Delivery for Oracle, repl.prm: OCI Error calling OCITransCommit (status = 2091-ORA-02091: transaction rolled back ORA-00001: unique constraint (ANNA.TAB_UI) violated), SQL<BEGIN dbms_apply_adm.delete_error(:1); END;>. 2018-03-04 23:26:31 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04 23:27:43 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 7, RBA 1611 in Integrated mode. REPLICAT will retry in Direct mode.
4.3. OGG 12.2 + ADD TRANDATA
Integrated Replicat works fine. No errors or warnings appear in the log.
4.4. OGG 12.2 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with message:
2018-03-04 23:06:28 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:06:28 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 2291 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a4,x."SRC_SCN" = :a5 WHERE x."PK1" = :b0>. 2018-03-04 23:06:28 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB. 2018-03-04 23:06:33 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04 23:07:23 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:07:23 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode.
4.5. OGG 12.3 + ADD TRANDATA
Integrated Replicat works fine. No errors or warnings appear in the log.
4.6. OGG 12.3 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with message:
2018-03-04T22:53:55.676+0100 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26799: unique constraint ANNA.TAB_UI violated for table ANNA.TAB with column values ("UI1", "UI2") = (2, )
ORA-00001: unique constraint (.) violated) while processing the record at SEQNO 8, RBA 2102 in Integrated mode. REPLICAT will retry in Direct mode.
2018-03-04T22:53:55.767+0100 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, repl.prm: OCI Error calling OCITransCommit (status = 2091-ORA-02091: transaction rolled back
ORA-00001: unique constraint (ANNA.TAB_UI) violated), SQL<BEGIN dbms_apply_adm.delete_error(:1, FALSE); END;>.
2018-03-04T22:53:55.767+0100 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repl.prm: Repositioning to rba 2102 in seqno 8.
2018-03-04T22:53:55.767+0100 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, repl.prm: OCI Error calling OCITransCommit (status = 2091-ORA-02091: transaction rolled back
ORA-00001: unique constraint (ANNA.TAB_UI) violated), SQL<BEGIN dbms_apply_adm.delete_error(:1, FALSE); END;>.
2018-03-04T22:53:55.767+0100 WARNING OGG-02044 Oracle GoldenGate Delivery for Oracle, repl.prm: Grouped transaction was aborted, most likely to use an Oracle workspace to handle a transient primary-key duplicate. Check for additional messages.
2018-03-04T22:53:55.767+0100 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repl.prm: Repositioning to rba 2102 in seqno 8.
2018-03-04T22:53:55.767+0100 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 1 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-00001: unique constraint (ANNA.TAB_UI) violated (status = 1), SQL <UPDATE "ANNA"."TAB" x SET x."UI1" = :a4,x."SRC_SCN" = :a5 WHERE x."PK1" = :b0>.
2018-03-04T22:53:55.767+0100 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB.
2018-03-04T22:54:00.935+0100 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04T22:56:09.882+0100 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26799: unique constraint ANNA.TAB_UI violated for table ANNA.TAB with column values ("UI1", "UI2") = (2, )
ORA-00001: unique constraint (.) violated) while processing the record at SEQNO 8, RBA 2102 in Integrated mode. REPLICAT will retry in Direct mode.
5. Foreign key constraint
The test for FK is pretty much the same as with UI. It leads to a situation after executing a transaction from the future older transactions can no longer be applied because of a FK constraint.
Source table is:
CREATE TABLE adam.REF( RF1 numeric not null, RF2 numeric not null ); ALTER TABLE adam.REF ADD CONSTRAINT REF_PK PRIMARY KEY(RF1, RF2); CREATE TABLE adam.TAB( PK1 numeric not null, RF1 numeric not null, RF2 numeric not null ); ALTER TABLE adam.TAB ADD CONSTRAINT TAB_PK PRIMARY KEY(PK1); ALTER TABLE adam.TAB ADD CONSTRAINT TAB_FK FOREIGN KEY(RF1, RF2) REFERENCES adam.REF(RF1, RF2);
Target table is:
CREATE TABLE anna.REF(
RF1 numeric not null,
RF2 numeric not null,
SRC_SCN numeric not null,
DST_SID numeric default sys_context('USERENV','SID') null,
DST_SESSSIONID numeric default sys_context('USERENV','SESSIONID') null,
DST_SEQ numeric GENERATED BY DEFAULT AS IDENTITY (START WITH 1) not null
);
ALTER TABLE anna.REF ADD CONSTRAINT REF_PK PRIMARY KEY(RF1, RF2);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.REF TO ggadmin;
CREATE TABLE anna.TAB(
PK1 numeric not null,
RF1 numeric not null,
RF2 numeric not null,
SRC_SCN numeric not null,
DST_SID numeric default sys_context('USERENV','SID') null,
DST_SESSSIONID numeric default sys_context('USERENV','SESSIONID') null,
DST_SEQ numeric GENERATED BY DEFAULT AS IDENTITY (START WITH 1) not null
);
ALTER TABLE anna.TAB ADD CONSTRAINT TAB_PK PRIMARY KEY(PK1);
ALTER TABLE anna.TAB ADD CONSTRAINT TAB_FK FOREIGN KEY(RF1, RF2) REFERENCES anna.REF(RF1, RF2);
GRANT SELECT, INSERT, UPDATE, DELETE ON anna.TAB TO ggadmin;
The replicat param file has:
MAP adam.REF, TARGET anna.REF, COLMAP(RF1 = RF1, RF2 = RF2, SRC_SCN = @GETENV('TRANSACTION','CSN'));
MAP adam.TAB, TARGET anna.TAB, COLMAP(PK1 = PK1, RF1 = RF1, RF2 = RF2, SRC_SCN = @GETENV('TRANSACTION','CSN'));
Test scenario is:
BEGIN
FOR x IN 0..99 LOOP
INSERT INTO adam.REF VALUES (x * 2 + 1, x * 2 + 1);
COMMIT;
INSERT INTO adam.TAB VALUES (x * 2 + 1, x * 2 + 1, x * 2 + 1);
COMMIT;
INSERT INTO adam.REF VALUES (x * 2 + 2, x * 2 + 1);
COMMIT;
UPDATE adam.TAB SET RF1 = x * 2 + 2 WHERE PK1 = x * 2 + 1;
COMMIT;
DELETE FROM adam.REF WHERE RF1 = x * 2 + 1 and RF2 = x * 2 + 1;
COMMIT;
INSERT INTO adam.REF VALUES (x * 2 + 3, x * 2 + 1);
COMMIT;
UPDATE adam.TAB SET RF1 = x * 2 + 3 WHERE PK1 = x * 2 + 1;
COMMIT;
DELETE FROM adam.REF WHERE RF1 = x * 2 + 2 and RF2 = x * 2 + 1;
COMMIT;
END LOOP;
END;
The following image shows what I am running. The colors represent which keys for the constraint the DML operation refers to:
This scenario is almost the same as with UI, but uses FK as a constraint.
If we run too early:
DELETE FROM adam.REF WHERE RF1 = 2 and RF2 = 1;
It is not possible to run any more:
UPDATE adam.TAB SET RF1 = 2 WHERE PK1 = 1;
The constraint will disallow that.
So it is important for the Replicat to look at the Foreign Constraint and mind the keys to be able to assign transactions that refer to the same rows to the same process.
6. Foreign key constraint – results
6.1. OGG 12.1 + ADD TRANDATA
Integrated Replicat fails with a message:
2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 4, RBA 2378 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 4, RBA 2378 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 INFO OGG-01971 Oracle GoldenGate Delivery for Oracle, repl.prm: The previous message, 'WARNING OGG-02544', repeated 1 times. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 4, RBA 2378 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 4, RBA 8619 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 INFO OGG-01971 Oracle GoldenGate Delivery for Oracle, repl.prm: The previous message, 'WARNING OGG-02544', repeated 3 times. 2018-03-04 23:17:42 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:17:42 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 2291 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a1,x."RF2" = :a2,x."SRC_SCN" = :a3 WHERE x."PK1" = :b0>. 2018-03-04 23:17:42 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB. 2018-03-04 23:17:47 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04 23:18:48 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:18:48 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 4, RBA 8619 in Integrated mode. REPLICAT will retry in Direct mode.
6.2. OGG 12.1 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with a message:
2018-03-04 23:20:33 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 5, RBA 1845 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:20:33 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 2291 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a1,x."SRC_SCN" = :a2 WHERE x."PK1" = :b0>. 2018-03-04 23:20:33 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB. 2018-03-04 23:20:38 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04 23:21:40 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 5, RBA 1845 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:21:40 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:21:40 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-26688: missing key in LCR) while processing the record at SEQNO 5, RBA 2230 in Integrated mode. REPLICAT will retry in Direct mode.
6.3. OGG 12.2 + ADD TRANDATA
Integrated Replicat works fine. No errors or warnings appear in the log.
6.4. OGG 12.2 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with a message:
2018-03-04 23:06:28 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:06:28 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 2291 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a4,x."SRC_SCN" = :a5 WHERE x."PK1" = :b0>. 2018-03-04 23:06:28 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB. 2018-03-04 23:06:33 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04 23:07:23 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04 23:07:23 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode.
6.5. OGG 12.3 + ADD TRANDATA
Integrated Replicat works fine. No errors or warnings appear in the log.
6.6. OGG 12.3 + ADD TRANDATA NOSCHEDULINGCOLS
Integrated Replicat fails with a message:
2018-03-04T22:47:28.666+0100 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04T22:47:28.676+0100 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repl.prm: Aborted grouped transaction on ANNA.TAB, Database error 2291 (OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a4,x."SRC_SCN" = :a5 WHERE x."PK1" = :b0>). 2018-03-04T22:47:28.676+0100 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repl.prm: Repositioning to rba 2610 in seqno 6. 2018-03-04T22:47:28.676+0100 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repl.prm: SQL error 2291 mapping ADAM.TAB to ANNA.TAB OCI Error ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found (status = 2291), SQL <UPDATE "ANNA"."TAB" x SET x."RF1" = :a4,x."SRC_SCN" = :a5 WHERE x."PK1" = :b0>. 2018-03-04T22:47:28.686+0100 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repl.prm: Error mapping from ADAM.TAB to ANNA.TAB. 2018-03-04T22:47:33.891+0100 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repl.prm: PROCESS ABENDING.
After dropping the constraint and resuming the replication the following message appears in the ggserr.log:
2018-03-04T22:48:37.689+0100 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02291: integrity constraint (ANNA.TAB_FK) violated - parent key not found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode. 2018-03-04T22:48:38.082+0100 WARNING OGG-02544 Oracle GoldenGate Delivery for Oracle, repl.prm: Unhandled error (ORA-02292: integrity constraint (ANNA.TAB_FK) violated - child record found) while processing the record at SEQNO 0, RBA 0 in Integrated mode. REPLICAT will retry in Direct mode.
7. Conclusions
The results tests let us make the following conclusions:
- Don’t use OGG 12.1 with Integrated Replicat – for Unque Index Constraints it displays a lot of warnings and for Foreign Key Constraints it does not work at all,
- OGG 12.1 displays some LCR errors which are difficult to interpret,
- OGG 12.2 and OGG 12.3 both work well,
- When you use Integrated Replicat don’t use ADD TRANDATA NOSCHEDULINGCOLS,
- If you did use NOSCHEDULINGCOLS and come to a situation when the Replicat fails, remove the constraint, resume Replicat and afterwards recreate the constraint.
This example shows clearly that if you have a multi-column Foreign Key or Unique Constraint then you might get into errors. For single column constraint this problem should not appear since every modification of such constraint would include all the required columns.
For multi-column constraint the Integrated Replicat assumes an optimistic version of DML operations when supplemental column information is missing.
how do I get the bind variables values when I get error ORA-2291?