OGG Tip: HANDLECOLLISIONS is slow car crash

K.Gan Feb 25, 2016 10:27 AM

Copied from manual:

Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target. These errors, called collisions, occur during an initial load, when data from source tables is being loaded to target tables while Oracle GoldenGate is replicating transactional changes that are being made to those tables. When Oracle GoldenGate applies the replicated changes after the load is finished, HANDLECOLLISIONS provides Replicat with error-handling logic for these collisions.

Comments:

If you are capturing from an Oracle database you should never ever need to use HANDLECOLLISIONS for initial load or otherwise. I highlighted 'initial load' and should never be continually used for any databases that is initial load phase is deem completed. The detail mechanics of  handlecollisions is extensively documented in the manual. Initial loads from an Oracle database should use a flashback SCN for accurate initial load syncs.

Using this parameter for continually used will almost certainly to make your target database out of sync with the source silently thus the comparison with a slow car crash.

If your replicat abends due to constraint or row not found error then you need to ask yourself why. Using handlecollisions to basically ignore this error is like a bank customer depositing money into an account that does not exist and basically ignoring it. I am sure you won't want to be that customer.

When you get a database error, try to find out why and if you cannot resolve this in a timely manner consider excluding the problem table for now and resolve this later. You can at least recover. If you use handlecollsions to consider it will very likely to be unrecoverable.

If you think that this may be a once off for one or a few records, use REPERROR (, discard) option on the MAP statement for that table. If you don't have a specific one because the MAP table names are wildcarded, create one for this table.