Oracle GoldenGate: Handle Collision Parameter and its usage

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.

1. Duplicate data exists in the source table.
2. Misconfiguration of the extract or Replicat configuration
3. 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.
4. No Data exist

The HANDLECOLLISIONS parameter is used to overcome these collisions. There are 3 types of Collisions:

1. Insert Collision – When a row is inserted on source database whose key column already exist on target DB
2. Update Collision- When a row is updated on the source whose key column doesn’t exist on target DB.
3. 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.

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.

1. You may use updateinserts to Handle Insert Collision with some limited functionality of handlecollisions
2. To Handle Update Collision use parameter INSERTMISSINGUPDATES.
3. 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.