HANDLECOLLISIONS was added to the replicat process in order to allow
for collisions caused by uncertainty in the initial load to be handled
for a short period of time. It is designed to handle changes to rows
that were made during the time that the initial load was happening and
is not designed to handle collisions that occur because of
bi-directional replication or logic or architectural problems.
Never use HANDLECOLLISIONS unless it is absolutely necessary. Even
then, use it for the shortest time possible.
If there is an update to a column that is used as a key in GoldenGate
the following will occur:
If the row that has the old key is not found in the target database,
the update is converted to an insert.
If a row is found with the new key, the row with the old key is
deleted and the update overlays the row with the new key.
Both of these cases require that all of the columns are logged. This
is done by making sure trandata is complete and by using the extract
parameter NOCOMPRESSUPDATES.
If a duplicate record is found, the value in the trail file is used
and the record that previously exists is discarded.
CONDITION ACTION
INSERT Collision INSERT Collision INSERT IN SOURCE WHOSE KEY CLUMN EXIST ON TARGET Converted to UPDATES
UPDATE Collision UPDATE Collision Updated in source but row not present in target MISSING ROW FROM TARGET IS CONVERTED TO INSERT
DELETE Collision DELETE Collision Deleted in source but row not present in target Ignored
Discard file :
SOURCE TARGET ERROR MESSAGE
Duplicate inserts Send to discard file when it comes across – REPERROR (0001 Discard) Unique constraint violation.
Updated in source but row not present at target Send to discard file when it comes across – REPERROR (1403 Discard) No data found
Deleted in source but row not present at target Send to discard file when it comes across – EPERROR (1403 Discard) No data found
If a missing record is found during an update or delete operation
that does not affect the GoldenGate key value, it is simply
discarded.
Enabling HANDLECOLLISIONS---->
Globally: Enable global HANDLECOLLISIONS for ALL MAP statements
/*
HANDLECOLLISIONS
MAP soe.emp, TARGET soe.emp;
MAP soe.device, TARGET soe.device;
MAP soe.deviceinfo, TARGET soe.deviceinfo;
MAP soe.orders, TARGET soe.orders;
*/
Group : Enable HANDLECOLLISIONS for some MAP statements
/*
HANDLECOLLISIONS
MAP soe.emp, TARGET soe.emp;
MAP soe.device, TARGET soe.device;
NOHANDLECOLLISIONS -- (from here NOHANDLECOLLISIONS )
MAP soe.deviceinfo, TARGET soe.deviceinfo;
MAP soe.orders, TARGET soe.orders;
*/
/*
Tables: Enable global HANDLECOLLISIONS but disable for specific
tables
HANDLECOLLISIONS
MAP soe.emp, TARGET soe.emp;
MAP soe.device, TARGET soe.device;
MAP soe.deviceinfo, TARGET soe.deviceinfo, NOHANDLECOLLISIONS; --
This is imp
MAP soe.orders, TARGET soe.orders, NOHANDLECOLLISIONS; -- This is
imp
*/
Detail record count : stats replicate --- redirected
Don't forget to remove the HANDLECOLLISIONS parameter after the
Replicat has moved old CSN where it was abending previously.
Also make sure to restart the Replicat after the removing this
parameter.
