HANDLECOLLISIONS is a handy parameter in Oracle GoldenGate that helps you manage data conflicts when replicating data. This feature comes in really handy for initial data loads and in scenarios where a subset of tables need to be reloaded on the target database.

How HANDLECOLLISIONS Works:

The parameter instructs Oracle GoldenGate on how to manage specific types of collisions that can occur in the following scenarios, allowing you to handle such conflicts:

1. Insert Collision: This occurs when an insert operation is attempted on the target, but the row already exists.
2. Update Collision: This happens when an update is performed, but the corresponding row is missing on the target.
3. Delete Collision: This arises when a delete operation is attempted, but the row cannot be found on the target.

HANDLECOLLISIONS will do the following:

• For insert collisions, GoldenGate will ignore the error if the row already exists.
• For update collisions, it will convert the update into an insert if the where clause doesn’t return any rows.
• For delete collisions, it will ignore the error if the row doesn’t exist.

This parameter should only be used for a short time during initial loads or when synchronizing databases for the first time, because long-term use can mask potential data integrity issues. I see this over and over with many customers that complaints about data integrity but they have this parameter enabled.

Enabling this parameter can help ensure that the replicat process runs smoothly and may reduce the likelihood of it terminating unexpectedly. However, it’s important to note that while this may provide immediate stability, it does not offer any alerts or notifications regarding potential underlying issues in your system. As a result, you may remain unaware of critical problems until they escalate, potentially leading to more significant complications down the line. It’s essential to weigh the benefits of stability against the risks of unaddressed concerns, especially in terms of handling data collisions effectively.

There are some prerequisites to using HANDLECOLLISIONS, they are:

• HANDLECOLLISIONS requires that all columns for every table are supplementally logged, and this adds a ton of overhead to the database. So ensure that once HANDLECOLLISIONS is turned off, the supplemental logging is set back to default (SCHEDULINGCOLS)

• HANDLECOLLISIONS requires that the full before image of every column is included in the trail file.  This requires the use of LOGALLSUPCOLS in Extract.

• HANDLECOLLISIONS only works on tables with a primary key or unique key that OGG can use (KEYCOLS with HANDLECOLLISIONS is not supported).  If used on tables without a key, you will get duplicate data on any update or delete collision.

It is crucial to turn off the HANDLECOLLISIONS feature once the initial data load or synchronization process has been completed. Keeping this feature enabled beyond that point can obscure real data consistency issues that may arise in a production environment. By disabling HANDLECOLLISIONS, you ensure that any potential problems are identified and addressed promptly, maintaining the integrity of your system and data, which is essential when handling data collisions. This practice helps you avoid overlooking significant discrepancies that could impact the performance and reliability of your application in the long run.