Scenario:
Parent and child table with delete cascade enabled and row movement enabled on parent table. There is after insert trigger on parent to insert into child record.
When update on parent table partition key column causing row movement, Oracle generates UPDATE and recursive DELETE and INSERT on the parent record.
SharePlex by default will capture the DELETE and INSERT on parent and send to target, and as a result of DELETE on PARENT it will delete CHILD record due to delete cascade enabled. Therefore this results in missing record for child table and child table is out of sync.
SOLUTION:
To prevent SharePlex from sending recursive DELETE and INSERT to target, set below parameter on source server
sp_ctrl> set param SP_OCT_OPTIMIZE_ROW_MOVEMENT 1
This is a live parameter and should take effect immediately.
