Oracle GoldenGate HANDLECOLLISIONS – NOHANDLECOLLISIONS

HANDLECOLLISIONS – NOHANDLECOLLISIONS

These parameters are configured in the Replicat side and they are set to continue the data processing if there are some data integrity issues on the Target tables. Upon setting these parameters Replicat Process tries to resolve the duplicate and missing records when applying the sql on the Target tables. These parameters are mainly used during the Initial Loads, when loading the data for synchronizing the Target Tables with Source.

These parameters are used when there is a possibility of an overlap or overwrite trail files which are being applied by the replicat process. It means, the replicat process tries to apply the changed data where already the data exists in the target.

The Replicat process will ABEND upon not using these parameters. This is because, the replicat process will try to insert the data from the new trail files and the data or rows already exists in the Target tables. It abends with the error like, Unique Constraint Primary Key Violated.

The Replicat process will also get ABEND, when it tries to process the UPDATES and DELETES for the rows which are not present in the Target Table. To overcome this situation, RBA has to be moved one transaction ahead so that the Replicat process will not get abend and will be running.

The table shows how HANDLECOLLISIONS parameter handles such scenarios,

HANDLECOLLISIONS should be used only when it is necessary. Once the necessity is accomplished, it should be removed soon as possible.

HANDLECOLLISIONS can be used in the following ways.,

• HANDLECOLLISIONS and NOHANDLECOLLISIONS can be used globally at root level in the parameter file. If HANDLECOLLISIONS is used, it remains active for all the MAP statements subsequently used until it faces or encounters the parameter NOHANDLECOLLISIONS. NOHANDLECOLLISIONS is the opposing parameter. It disables it.

• HANDLECOLLISIONS and NOHANDLECOLLISIONS can be enabled for specific tables or within specific MAP statements for error handling.

Enabling HANDLECOLLISIONS globally,

Enabling HANDLECOLLISIONS for some MAP Statements,

In the above example, HANDLECOLLISIONS will be active or used for only two tables, EMP and DEPT.

Enabling Table Specific HANDLECOLLISIONS,