What Does The HANDLECOLLISIONS Parameter Do? [ID 966207.1]

In this Document
Goal
Solution
Explanation of HANDLECOLLISIONS
HANDLECOLLISIONS and PKUPDATES:
HANDLECOLLISIONS and KEYCOLS

---

Applies to:

Oracle GoldenGate - Version: 4.0.0 and later [Release: 4.0.0 and later ]
Information in this document applies to any platform.

Goal

What does the Oracle GoldenGate (OGG) Replicat parameter HANDLECOLLISIONS do?

Solution

Explanation of HANDLECOLLISIONS

HANDLECOLLISIONS is a Replicat parameter that is mainly used during initial loads. It lets you continue processing data in a trail even when there is a data integrity problem in the target environment, such as a missing row for an update, a missing row for a delete, or a duplicate insert.

When the HANDLECOLLISIONS parameter is set, data is processed as follows : -

Missing updates are ignored.
Missing deletes are ignored.
Duplicate inserts are turned into updates.

To enable this functionality, add the HANDLECOLLISIONS parameter to your Replicat parameter file. You can use it for a specific table or globally. After adding it, stop and start Replicat for it to take effect.

You can enable HANDLECOLLISIONS dynamically, without stopping Replicat by using the SEND command as follows:

GGSCI> SEND REPLICAT <NAME>, HANDLECOLLISIONS [<owner.table_name>]

To see whether HANDLECOLLISIONS is enabled, use the SEND command as follows:

SEND REPLICAT <NAME>, REPORT HANDLECOLLISIONS [<owner.table_name>]

It is NOT recommended to have HANDLECOLLISIONS active all of the time, because you may introduce data inconsistencies to the target. Processing will continue according the the rules stated previously, even if data is missing or duplicated. The main use of HANDLECOLLISIONS is when you start Replicat after an initial load during which source user applications remained online and GoldenGate captured their changes. HANDLECOLLISIONS resolves those ongoing changes with the changes made by the load. HANDLECOLLISIONS also can be used when you reposition Replicat backward in a trail to resolve other issues.

The default setting for replicat is NOHANDLECOLLISIONS

From GoldenGate version 8.0.2.1 and above, you will see a collision count for duplicate inserts in the Replicat statistics or report file when Replicat has been stopped.

HANDLECOLLISIONS and PKUPDATES:

If the missing update is a primary key update then replicat will try to insert the record rather than ignore it. However, the record in the trail to be inserted may not be a full image when the insert is attempted. The full image is required to do a PKUPDATE.

To get the full image for a primary key update, the parameter:

FETCHOPTIONS FETCHPKUPDATECOLS

needs to be added to extract parameter file and extract needs to be restarted. This will cause the extract to capture the full image for primary key update.

如果想要获取到主键更新的redo全镜像，则必须要在源端抽取进程中添加FETCHPKUPDATECOLS参数，否则目标端更新数据则会丢失非主键列的镜像。

注意：该参数要和目标端的 HANDLECOLLISIONS  一起使用才能保证主键更新在目标端成功！

HANDLECOLLISIONS and KEYCOLS

Handlecollisions and DML operations on tables without primary keys or unique index works differently even when KEYCOLS