What does “HANDLECOLLISIONS” do in GoldenGate?

HANDLECOLLISIONS is a parameter in Oracle GoldenGate designed to help manage data conflicts that arise during data replication. It's especially useful during initial data loads or when a specific subset of tables needs to be reloaded on the target database.

How HANDLECOLLISIONS Works: This parameter directs GoldenGate on how to handle specific types of data conflicts that can occur:

• Insert Collision: When an insert operation is attempted, but the row already exists on the target.
• Update Collision: When an update is attempted, but the target row is missing.
• Delete Collision: When a delete operation is attempted, but the row is not found on the target.

HANDLECOLLISIONS takes the following actions:

• For insert collisions, GoldenGate will skip the error if the row already exists.
• For update collisions, it will convert the update to an insert if no row is found.
• For delete collisions, it will skip the error if the row doesn't exist.

This parameter should only be used temporarily during initial loads or first-time synchronization, as keeping it on long-term can mask real data consistency issues. Many users mistakenly leave it on and later face data integrity concerns.

While enabling this parameter can prevent the replicat process from stopping unexpectedly, it’s important to understand that it does not provide alerts about underlying issues in the system. This lack of visibility can mean that critical issues go unnoticed until they escalate. Therefore, you should carefully consider the trade-off between stability and the potential risk of unresolved issues.

Prerequisites for using HANDLECOLLISIONS:

1. HANDLECOLLISIONS requires supplemental logging of all columns for each table, which can add significant overhead to the database. Once HANDLECOLLISIONS is disabled, revert supplemental logging to default settings (SCHEDULINGCOLS).
2. It requires the full "before" image of every column in the trail file, which is enabled through LOGALLSUPCOLS in the Extract.
3. HANDLECOLLISIONS only works with tables that have a primary or unique key, which GoldenGate can use. If used on tables without such a key, it may result in duplicate data during update or delete collisions.

Be sure to turn off HANDLECOLLISIONS once the initial load or synchronization is complete. Keeping it enabled beyond this can hide data consistency issues in production. Disabling HANDLECOLLISIONS after the initial synchronization helps detect and address data problems promptly, preserving system and data integrity and preventing performance and reliability issues in the long term.