-
Title
Error "Out of sync: table - .......ORA-02291 integrity constraint ...violated - parent key not fo -
Description
The following messages are observed in the event log due to integrity constraint violation in a named Post queue environment:
sp_ctrl (105.252.10.132:2110)> show log reverse
08/19/07 18:33 Out of sync: table - "ARBOR"."EMF" (rid:AADUjWAF/AAAdLaAAE) ORA-02291: integrity constraint (ARBOR.EMF_EMF_SUBSCR_NO_RESETS_FK) violated - parent key not found. [sp_opst (for o.arpm1v-o.arrm1v queue ebill1)/17476]
08/19/07 18:17 Out of sync: table - "ARBOR"."EMF" (rid:AADUjWAF/AAAdLXAAA) ORA-02291: integrity constraint (ARBOR.EMF_EMF_SUBSCR_NO_RESETS_FK) violated - parent key not found. [sp_opst (for o.arpm1v-o.arrm1v queue ebill1)/17476]
.
.
. -
Cause
Configuration not created as per Shareplex guidelines.
-
Resolution
The problem occurred because parent and child tables are not replicating within the same named Post queue. This can sometimes result in the jumbling of the order of posting of messages on target database whereby the child record may attempt to be created first though the parent row has not yet been inserted. Shareplex Admin Guide very clearly delves on this as the following excerpt from the chapter "Planning Shareplex Replication" shows:
"If you are using named export or post queues, all tables with referential integrity to one another must be in the same queue."
To correct this issue, one has to fix the issue from the source by activating a new config file with children tables and parent table in the same post queue.
Henceforth, one should modify the config file so that tables that have referential integrity relationship should be made to traverse through same named queue. To do this, one can make a copy of the currently active config file, edit the copy to change the routing as appropriate and activate the copy. This will require a very brief lock on the source table since there is only a routing change involved. In most cases this will not incur any database downtime. In the event that some application locks the source table in EXCLUSIVE mode, thereby making it impossible to activate the new config, the existing config will prevail and one may have to find downtime to achieve this change.
For the existing messages in the queues, there is something one can do to minimize the out of sync, but for the tables that already went out of sync one can correct the out of sync problem by using compare or any other means. Since messages are separated into different queues, one can stop post for children table queue and let post keep posting the parent queue messages first.