Oracle GoldenGate: HANDLECOLLISIONS Is a Migration Tool — Not a Permanent Fix

One of the Oracle GoldenGate parameters that deserves more attention during migrations is:

HANDLECOLLISIONS

It sounds harmless enough. In fact, it can be extremely useful.

But it is important to understand why it exists and when it should be removed.

Oracle GoldenGate uses HANDLECOLLISIONS primarily to deal with duplicate-record and missing-record conditions that can occur when an initial data load and transactional replication overlap.

A common migration sequence looks something like this:

Source Database → Initial Load → Target Database

while at the same time:

Source Database → Extract → Trail → Replicat → Target Database

That overlap creates an interesting timing problem.

Imagine that a row exists in the source when the initial load begins.

While the initial load is running, the application updates that same row.

The update gets captured by GoldenGate.

Depending on timing, Replicat may encounter situations such as:

• An INSERT where the target row already exists.
• An UPDATE where the target row cannot yet be found.
• A DELETE for a row that has not reached the target through the initial load.
• Transactions arriving in a different relationship to the bulk-load process than they occurred on the source.

Those are exactly the situations HANDLECOLLISIONS was designed to help manage.

The important part comes next.

HANDLECOLLISIONS should generally be temporary.

Once the initial-load synchronization period has completed and the source and target are properly aligned, continuing to run with:

HANDLECOLLISIONS

can potentially mask replication problems that should instead be investigated.

A duplicate key after synchronization is no longer necessarily an "initial load collision."

It may indicate:

• Data was manually inserted into the target.
• Replication was restarted from the wrong position.
• A transaction was applied twice.
• Tables are not properly synchronized.
• Key definitions are incorrect.
• Replicat configuration is wrong.
• Another application or process is modifying the target.

If GoldenGate is allowed to automatically work around those situations indefinitely, the environment may appear healthy while the source and target slowly diverge.

That is why a mature GoldenGate migration process should have an explicit transition point:

Initial Load

HANDLECOLLISIONS

↓

Validate Source and Target Synchronization

↓

NOHANDLECOLLISIONS

↓

Normal Replication

At that point, duplicate or missing-record errors should generally be treated as diagnostic information, not automatically suppressed.

The operational lesson

When inheriting an existing GoldenGate environment, don't just ask:

"Is Replicat running?"

Also ask:

"What parameters is Replicat running with?"

A Replicat showing RUNNING does not necessarily mean replication is configured correctly.

Reviewing parameter files should be part of any GoldenGate operational assessment, particularly after a migration, initial load, rebuild, or recovery operation.

A parameter that was absolutely correct during migration can become exactly the wrong parameter six months later.

HANDLECOLLISIONS is a perfect example.

#Oracle #OracleGoldenGate #GoldenGate #OracleDatabase #OCI #DatabaseMigration #DataReplication #DBA #DatabaseAdministration #CloudMigration