July 29, 2018 by Atul Kumar
from: https://k21academy.com/oracle-goldengate-12c/oracle-goldengate-handle-collision-parameter-and-its-usage/
Handle collision parameter is used to handle INSERT, UPDATE and DELETE collision.
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.
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.