HANDLECOLLISIONS oracle goldengate 26ai part 3 read&learn gg
Photo by NASA Hubble Space Telescope on Unsplash
Handlecollision parameter lives in replicat parameter file . This parameter helps in solving data conflicts preventing replicat to ABEND . This feature is a double-edged sword; use it with extreme caution rather than as a magic bullet to solve conflicts. One shouldn’t use this parameter without knowing what happens behind the scene and should also know the scenarios in which to use it . In this blog we will try to uncover the same.
Collision Scenarios
There are three types of Data Collision which replicat can encounter , Insert / Update and Delete .
Insert Collision : Replicat tries to insert a row in the target which already exists . The replicat ignores the row.
Update Collision : Replicat is trying to update a row but row is missing on the target . The replicat converts the UPDATE DML to an INSERT and processes it in the target.
Delete Collision : Replicat is running a delete statement on target but row is missing . Replicat simply ignores the row
In all the above scenarios Replicat would have ABENDED if not for the Handlecollision parameter. Readers might wonder why the scenarios look rational and why you must use the parameter at all times. Here is the catch this parameter will quietly cause data integrity issues at the target without you knowing , leading to an escalation.
When to Use?
You use Handlecollision in the scenarios below.
Initial load , while loading source data to target there could be inconsistent backup/restore timestamps leading to potential gaps in data.
Initial Synchronization , keeping this parameter on for just a while until target backlog is cleared . Once target is in sync remove the parameter.
Table Resync , When a table at target is completely out of sync with source ,stop replicat , truncate the table, bring data from source ( using dblink or datapump ) , enable handlecollision only for the affected table , start the replicat for few mins and once backlog is drained , remove handlecollision and restart replicat.
How to Use ?
replicat reptarget
USERIDALIAS admin DOMAIN OracleGoldenGate
-- Tables
HANDLECOLLISIONS
MAP SCOTT.table1, TARGET EMP.table1;
...
MAP SCOTT.table1, TARGET EMP.table100;
In the above scenario , simply mention handlecollision parameter at the top of 1st MAP and its applicable to all the tables beneath.
replicat reptarget
USERIDALIAS admin DOMAIN OracleGoldenGate
-- Tables
HANDLECOLLISIONS
MAP SCOTT.table1, TARGET EMP.table1;
MAP SCOTT.table2, TARGET EMP.table2;
MAP SCOTT.table3, TARGET EMP.table3;
NOHANDLECOLLISIONS
...
MAP SCOTT.table1, TARGET EMP.table100;
In above scenario 3 MAP tables are sandwiched between HANDLECOLLISIONS | NONOHANDLECOLLISIONS .
Handlecollision only works for 3 tables.
Ref:
https://docs.oracle.com/en/database/goldengate/core/26/index.html
https://alexlima.com/2024/10/28/what-is-handlecollisions-all-about-in-goldengate/
