Compare errors out with "Error 2291: ORA-02291: integrity constraint (constraint_name) violated" (4299584)

The following messages are observed in the target compare log (*declt* log) when compare errors out while trying to fix out of sync on the child table:

000: Oracle Error 2291: ORA-02291: integrity constraint (constraint_name) violated - parent key not found

000: Error ORA-2291 calling OCIStmtExecute (de_repair_insert,L2917)
000: No further repairs will be applied
000: Error 1 calling de_repair_insert in de_compare_insert_server_rows (301)
000: Continuing to process so all the out-of-sync rows are reported
000: Error -1 calling de_compare_client (main,L2546)
Â

Compare cannot fix the out of sync because of ORA-02291 it encountered.

While compare tries to fix the child table, it encounters the ORA-02291. The following workaround can be tried assuming that the referential integrity constraint has been enabled and maintained on source:

1. Disable the referential integrity constraint on target.
2. Run compare to fix out of sync
3. Re-enable the referential integrity constraint on target. If there are any issues with re-enabling, which would typically happen if there are messages belonging to parent or child tables in the pipeline and are yet to be posted, then wait for a quiet period when there are no messages expected for these tables and then re-enable the constraint.

The following caveat is worth noting. If the constraint on source has been created with NOVALIDATE option to begin with, then there may be data integrity issues on parent and child tables on source and consequently the target tables will inherit such inconsistencies. So even though the parent and child tables on target are in sync with the source (after the running of compare), the constraint will fail when re-enabled.

Here are some more points worth noting:

Make sure no queues are stopped and that they are not backlogged when re-enabling constraints

If re-enabling fails, one can attempt to re-enable them multiple times as needed (for a table that fails to enable it after a successful repair of both parent and child table). If the source is quiet and the queues are fully drained, the constraints should re-enable fine, unless there are data integrity issues on source, a condition rarely observed.

If there are hierarchies of RI constraints, say grandfather, father and child, then the enabling of constraints may fail with ORA-02291 or ORA-02292 even though one of the parent/child pair has been successfully repaired. One may then need to disable all the constraints in the chain, repair the tables and then re-enable the constraints.