In this post we are talking about Handle collision Parameter and its usage. Handle collision parameter is used to handle INSERT, UPDATE and DELETE collision.
For those who are new to Oracle Goldengate, is a software for real-time data integration and replication in heterogeneous IT Systems.
If you want to learn more about Oracle GoldenGate then check our previous posts about Oracle GoldenGate 12c Overview & Components. And GoldenGate 12c (12.3.0.1) New Features/Changes
If you want to install Goldengate 12c then go through our post here Oracle GoldenGate 12c Download & Installation and for troubleshooting go through Oracle GoldenGate 12c: Troubleshooting using LogDump Utility
Usage of Handle Collision parameter
The Goldengate HANDLECOLLISIONS parameter is configured on the target database in the Replicat process to Handle the collisions. It enables processing of the data when there are duplicate data integrity or no data found issues identified in the destination database.
There could be a number of reasons which could cause this condition. Some of them include the following.
- Duplicate data exists in the source table.
- Misconfiguration of the extract or Replicat configuration
- Data Overlap –The table data was instantiated at a particular CSN (Commit Sequence Number) in the destination database but the Replicat process was started at a CSN prior table load SCN.
- No Data exist
The HANDLECOLLISIONS parameter is used to overcome these collisions. There are 3 types of Collisions:
- Insert Collision – When a row is inserted on source database whose key column already exist on target DB
- Update Collision- When a row is updated on the source whose key column doesn’t exist on target DB.
- Delete Collision- When a row is deleted on the source whose key column doesn’t exist on target DB.
Without the use of this parameter, the Replicat will ABEND when it tries to process the inserts from the trail into the table which already has the rows (PK or unique constraint violation).
It will also ABEND when the Replicat tries an update or delete rows which are not present in the destination tables. To overcome this normally the RBA of the trail has to be moved forward one transaction before the Replicat can be restarted and will stay running.
The following is the behavior of the Replicat process when the Goldengate HANDLECOLLISIONS parameter is enabled.
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 |
Resolution :
Enabling HANDLECOLLISIONS
- Goldengate HANDLECOLLISIONS should be used only when and where necessary.
- It should be removed from the Oracle Goldengate Replication configuration as soon as possible.
- If it has to be enabled, it should only be done so ONLY for tables requiring this.
This can be achieved by using HANDLECOLLISION, but by listing the specific tables and then turning it off using the NOHANDLECOLLISIONS clause for the remaining tables, as shown below.
Set Globally
Enable global HANDLECOLLISIONS for ALL MAP statements
HANDLECOLLISIONS
MAP pdb1.ggtraining1.dept11, TARGET pdb2.ggtraining2.dept22;
MAP pdb1.ggtraining1.emp11, TARGET pdb2.ggtraining2.emp22;
MAP pdb1.ggtraining1.hr11, TARGET pdb2.ggtraining2.hr22;
MAP pdb1.ggtraining1.revenue11, TARGET pdb2.ggtraining2.revenue22;
Set for Group of MAP Statements
Enable HANDLECOLLISIONS for some MAP statements
HANDLECOLLISIONS
MAP pdb1.ggtraining1.dept11, TARGET pdb2.ggtraining2.dept22;
MAP pdb1.ggtraining1.emp11, TARGET pdb2.ggtraining2.emp22;
NOHANDLECOLLISIONS
MAP pdb1.ggtraining1.hr11, TARGET pdb2.ggtraining2.hr22;
MAP pdb1.ggtraining1.revenue11, TARGET pdb2.ggtraining2.revenue22;
Set for Specific Tables
Enable global HANDLECOLLISIONS but disable for specific tables
HANDLECOLLISIONS
MAP pdb1.ggtraining1.dept11, TARGET pdb2.ggtraining2.dept22;
MAP pdb1.ggtraining1.emp11, TARGET pdb2.ggtraining2.emp22;
MAP pdb1.ggtraining1.hr11, TARGET pdb2.ggtraining2.hr22; NOHANDLECOLLISIONS
MAP pdb1.ggtraining1.revenue11, TARGET pdb2.ggtraining2.revenue22, NOHANDLECOLLISIONS;
Remove the HANDLECOLLISIONS parameter after the Replicat has moved past the CSN where it was abending previously.
Also make sure to restart the Replicat after the removing this parameter.
Handle Collision parameter substitute
Since HANDLECOLLISION is not the recommended parameter to be used during ongoing replication, you can use a different set of parameters.
- You may use updateinserts to Handle Insert Collision with some limited functionality of handlecollisions
- To Handle Update Collision use parameter INSERTMISSINGUPDATES.
- To capture rows which are either duplicate INSERTS or do not exist in the destination to be updated or deleted, REPERROR can be used to record these rows into a discard file.
This post is from our Oracle GoldenGate 12c Administration Training, in which we cover Architecture, Installation, Configuring & Preparing the Environment, DML Replication – Online Change Synchronization, Initial Load, Zero Downtime Migration & Upgrading using GoldenGate, Oracle GoldenGate Security, Performance of Oracle GoldenGate and Troubleshooting and much more.
Next Task For You
Want to move ahead in your career and want to get a higher Earning Job?
Get 7+ Courses for DBAs & Apps DBA in a bundle program and learn from the Industry’s best Experts.
Erptree Oracle says
That is a good tip, especially to those new to the blogs here but very accurate information. Very nice blog post!! Thanks for the posting that is a really neat written blog.
Peter says
Thanks for tips.
1. what happens if you do not remove the handlecollissions parameter
2. what errors in report file will make you know you have to use handlecollissions
Thanks
KK says
Hi, nice post..
one quick thing to verify. I did that and not working as mentioned in above post.
Update Collisions where above post says: “MISSING ROW FROM TARGET IS CONVERTED TO INSERT”. Tested in 19c, its not happening, rather its ignoring like Delete collisions.
Please check once.
Regards,
KK
Rahul Dangayach says
Hi KK,
If that is the case, pls use a combination of HANDLECOLLISIONS and INSERTMISSISNGUPDATES together.
This seems to the update from 12.2 GG version.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Charly Soman says
Same in my case, update was just ignored when the key column value is missing. Assumption was that it will be converted to insert.. Oracle documentation itself is misleading for 19c
Sudheer Godgeri says
We face following types of issues on production goldengate replication:
1) we have insertmissingupdates parameters to insert a missing row required for update using values from the update info in trail ( Dont we need to use “alter database add supplemental log (all) columns ” for this work ?
2) We get frequent issues of missing DELETEs — i.e developers have deleted rows from both Source/Target — and the GG replicat process is stuck due to ORA 1401 missing row. The existing practice in our setup is to insert dummy rows with PK values from UPDATE into target — then restart the GG replicat process: Is this the only way to skip missing DELETEs operation in GG 12c?
Rahul Dangayach says
Hi Sudheer,
Please check the ab=nswers below:
1) Yes for this you need to have trandata/supplemental logging enabled on all columns.
2) First try to identify why the deletes are missing.
If you just want to skip the error, use REPERROR parameter in replicat param file or use HANDLECOLLISIONS.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Sudheer says
Thanks Rahul. Great article – helped understand issue clearly.
Most of issues for Goldengate are due to developer(s) using direct updates/deletes or data being loaded in bulk operations on both target/source.
Sudheer says
Great and clear decsription of handlecollisions here – did not find the same mentioned so clearly in oracle docs even. Thanks.
manav says
can we remove handlecollision from replicat if replicat in lag
Rahul Dangayach says
Hi Manav,
Stop Replicat and remove HANDLECOLLISIONS from the Replicat parameter file (can cause target latency). Alternatively, you can edit the parameter file to add NOHANDLECOLLISIONS before the MAP statements for which you want to disable the error handling.
For more info please check the link below:
https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters081.htm#GWURF520
Hope this helps.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Mahmoud says
The best explanation of HANDLECOLLISIONS parameter along with its alternatives. Thanks and Good job!