OGG-01004 Aborted grouped OCI Error ORA-02291: integrity const raint (xx.xx) viola

问题描述

生产环境部署了一套OGG同步，源端环境是Linux oracle 11.2.0.4，目标端环境是windows oracle 11.2.0.4
OGG版本是18.3

在同步的过程中，目标端应用进程出现如下提示，报错的内容跟外键相关。

2024-08-01 00:04:22  WARNING OGG-01004  Aborted grouped transaction on xx.xx, Database error 2291 (OCI Error ORA-02291: integrity const
raint (FLIGHT.DF_CRAFT_IBFK_1) violated - parent key not found (status = 2291), SQL <UPDATE "xx"."DF_CRAFT" x SET xx."xx" = :a1 WHERE x
."ID" = :b0>).

2024-08-01 00:04:22  WARNING OGG-01003  Repositioning to rba 5921 in seqno 2.

2024-08-01 00:04:59  WARNING OGG-01154  SQL error 2291 mapping xx.xxto FLIGHT.DF_CRAFT OCI Error ORA-02291: integrity constraint (FLIG
HT.DF_CRAFT_IBFK_1) violated - parent key not found (status = 2291), SQL <UPDATE "xx"."xx" x SET x."AFLIGHTID" = :a1 WHERE x."ID" = :b0
>.

处理方法：在目标端禁用外键约束，（有时候要把触发器也禁用掉）

SELECT 'ALTER TABLE '||owner||'.'|| table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';'
FROM DBA_constraints
WHERE constraint_type = 'R'
and table_name='xx';

ALTER TABLE xx.xxDISABLE CONSTRAINT DF_CRAFT_IBFK_2;
ALTER TABLE xx.xxDISABLE CONSTRAINT DF_CRAFT_IBFK_1;

重启OGG进程恢复正常。