[轉載] Goldengate: How to use HANDLECOLLISIONS correctly?

Ref: http://www.vitalsofttech.com/when-to-use-handlecollisions-in-goldengate-replicat/

 

When is the Oracle Goldengate HANDLECOLLISIONS parameter useful?

 

The Goldengate HANDLECOLLISIONS parameter is configured on the Replicat side to continue processing of the data when there are data integrity issues on the destination side and is okay to do so. It is utilized when there is a possibility of an overlap of the trail data being applied by the replicat process (replicat) where the data already exists in the destination tables.

 

Otherwise without this parameter, the REPLICAT will abend when it tries to process the inserts from the trail into the table which already has the rows (PK or unique constraint violation). It will also abend when the replicat tries updating or deleting rows which are not present in the destination tables. To overcome this normally the RBA of the trail has to be moved forward one transaction before the replicat can be restarted and will stay running.

 

 

The following is the behavior of the REPLICAT process when the Goldengate HANDLECOLLISIONS parameter is enabled.

 

 

 

ON SOURCE    ON TARGET     CONDITION     ACTION

INSERTS   INSERTS   Duplicate INSERTS  Converted to UPDATES

UPDATES UPDATES Updated in source but row not present in target   Ignored

DELETES  DELETES  Deleted in source but row not present in target    Ignored

 

How do you apply rows which would normally fail on target?

 

To capture rows which are either duplicate INSERTS or do not exist in the destination to be updated or deleted, REPERROR can be used to record these rows into a discard file.

 

In the example below, the REPERROR (1403, Discard) parameter is used to identify a condition when the row the REPLICAT is looking for, is not present in the destination database. Similarly, the REPERROR (0001, Discard) is raised when a duplicate INSERT is attempted but it violates a PK or unique value key as the row is already present in the table.

 

REPLICAT rep02

USERID gg_user, PASSWORD DCJINAREOFHCTHCHVGNATACHGAKHICHEPDXG, ENCRYPTKEY key1

ASSUMETARGETDEFS

DISCARDFILE /u01/app/ha/ggs/dirrpt/rep02.dsc, APPEND, MEGABYTES 1024

DBOPTIONS SUPPRESSTRIGGERS

DDLOPTIONS UPDATEMETADATA, REPORT

REPERROR (0001, DISCARD)

REPERROR (1403, DISCARD)

…..

 

SOURCE   TARGET   ERROR MESSAGE

Duplicate inserts     Send to discard file when it comes across – REPERROR (0001 Discard)       Unique constraint violation.

Updated in source but row not present at target  Send to discard file when it comes across – REPERROR (1403 Discard)   No data found

Deleted in source but row not present at target   Send to discard file when it comes across – REPERROR (1403 Discard)   No data found

How can we enable HANDLECOLLISIONS for only one table?

 

Firstly, as discussed above the Goldengate HANDLECOLLISIONS should be used only when and where necessary. It should be removed from the Oracle Goldengate replication configuration as soon as possible. Secondly if it has to be enabled, it should only be done so ONLY for tables requiring this. This can be achieved by using HANDLECOLLISION, but by listing the specific tables and then turning it off using the NOHANDLECOLLISIONS clause for the remaining tables, as shown below.

 

Enable global HANDLECOLLISIONS for ALL MAP statements

 

HANDLECOLLISIONS

MAP vst.inventory, TARGET vst.inventory;

MAP vst.trans_hist, TARGET vst.trans_hist;

MAP vst.trans, TARGET vst.trans;

MAP vst.orders, TARGET vst.orders;

Enable HANDLECOLLISIONS for some MAP statements

HANDLECOLLISIONS

MAP vst.inventory, TARGET vst.inventory;

MAP vst.trans_hist, TARGET vst.trans_hist;

 

NOHANDLECOLLISIONS

MAP vst.trans, TARGET vst.trans;

MAP vst.orders, TARGET vst.orders;

 

Enable global HANDLECOLLISIONS, disable for specific tables

HANDLECOLLISIONS

MAP vst.inventory, TARGET vst.inventory;

MAP vst.trans_hist, TARGET vst.trans_hist;

MAP vst.trans, TARGET vst.trans, NOHANDLECOLLISIONS;

MAP vst.orders, TARGET vst.orders, NOHANDLECOLLISIONS;

 

 

You can read more on Oracle Replication Features and other products.

 

Troubleshooting Series

 

• GoldenGate: Skipping Transactions

• GoldenGate: Finding Open Database Transactions

• GoldenGate: How to use handle Collisions correctly?