Understanding HANDLECOLLISIONS in Oracle GoldenGate: When and How to Use It

Leave a reply

Oracle GoldenGate (OGG) is a powerful real-time data replication and integration tool widely used for high availability, disaster recovery, migrations, and reporting systems.

One of the parameters that often confuses new GoldenGate users is HANDLECOLLISIONS.

In this blog, let’s break down what it does, when to use it, and the pros and cons of enabling it.

🔹 What is HANDLECOLLISIONS?

When you set up GoldenGate replication, there are chances that the same record may be inserted, updated, or deleted on both the source and target systems.

This results in conflicts or collisions.

The HANDLECOLLISIONS parameter tells GoldenGate to automatically handle these conflicts instead of abending (failing) the Replicat process.

GoldenGate internally uses primary key (or unique key) lookups to decide whether to:

  • INSERT → If row doesn’t exist on target, it will be inserted.
  • UPDATE → If row exists, it will be updated.
  • DELETE → If row exists, it will be deleted. If not, it will be ignored.

👉 In short, it helps avoid replication failure during initial load or catch-up scenarios.

🔹 When Do We Use HANDLECOLLISIONS?

You typically use HANDLECOLLISIONS during these scenarios:

  1. Initial Data Load with Ongoing Replication
    • When you perform initial load (bulk copy) into target and at the same time capture changes, collisions can occur.
    • Example: If a row was already inserted during the load and again comes via trail file, GoldenGate might fail.
  3. Resync / Repair Operations
    • If source and target are temporarily out of sync due to network failure, downtime, or skipped transactions, collisions may occur.
  5. During Migrations (Zero Downtime)
    • While switching applications from source to target, both systems might be live for some time → collisions are common.

🔹 Example of HANDLECOLLISIONS

Without HANDLECOLLISIONS

Let’s assume you are replicating EMP table from Source → Target.

On target, a row already exists:

EMPID | NAME     | SALARY
1001  | JOHN     | 5000

Now the same insert comes from source trail file:

INSERT INTO EMP VALUES (1001, 'JOHN', 5000);

👉 Result:

Replicat will abend with ORA-00001: unique constraint violated.

With HANDLECOLLISIONS

If you enable in Replicat:

REPLICAT REMP
USERID ggs_admin, PASSWORD ggs_pwd
HANDLECOLLISIONS
MAP hr.emp, TARGET hr.emp;

👉 GoldenGate logic:

  • It sees the row exists.
  • Instead of failing, it ignores the insert.
  • Replication continues without abending.

🔹 Advantages of HANDLECOLLISIONS

✅ Prevents replication failure during:

  • Initial load with ongoing extract/replicat
  • Resync after downtime
  • Live migrations

✅ Ensures replication continues without manual intervention.

✅ Good temporary solution for bulk sync operations.

🔹 Drawbacks of HANDLECOLLISIONS

❌ Performance Impact – Extra lookups for every transaction to detect collisions.

❌ Silent Data Inconsistency – Since conflicts are ignored or auto-resolved, the target may not always reflect the true source state.

❌ Not for Long-Term Use – It’s a band-aid, not a permanent fix. Recommended only during initial load or sync phases.

❌ Debugging becomes difficult – because changes might be skipped silently.

🔹 Best Practices

  1. Use only during initial load or repair.
    Remove it after sync is complete.
  2. Check for out-of-sync rows using:
GGSCI> ADD TRANDATA hr.emp
GGSCI> VALIDATE CHECKSUM

or custom row counts / minus queries.

  1. After initial sync, stop replicat, remove HANDLECOLLISIONS, and restart.
-- Edit parameter file, remove HANDLECOLLISIONS
GGSCI> STOP REPLICAT REMP
GGSCI> START REPLICAT REMP

🔹 Conclusion

The HANDLECOLLISIONS parameter in Oracle GoldenGate is a useful tool for handling conflicts during initial loads, resyncs, or migrations.

But remember — it is a temporary helper, not a long-term solution. Once your source and target are fully in sync, remove it to ensure accurate replication and better performance.

Leave a Reply