Don’t Handle Collisions in Oracle GoldenGate
I have recently been supporting Oracle GoldenGate at more than one client who had previous consultants tell them that it’s okay to use HANDLECOLLISIONS in their replicat. Furthermore, they have been told to always use that option. Well, I want to tell you this. Never use HANDLECOLLISIONS unless it is absolutely necessary. Even then, use it for the shortest time possible.
HANDLECOLLISIONS was added to the replicat process in order to allow for collisions caused by uncertainty in the initial load to be handled for a short period of time. It is designed to handle changes to rows that were made during the time that the initial load was happening and is not designed to handle collisions that occur because of bi-directional replication or logic or architectural problems.
So, here is what HANDLECOLLISIONS actually does:
- If there is an update to a column that is used as a key in GoldenGate the following will occur:
- If the row that has the old key is not found in the target database, the update is converted to an insert.
- If a row is found with the new key, the row with the old key is deleted and the update overlays the row with the new key.
Both of these cases require that all of the columns are logged. This is done by making sure trandata is complete and by using the extract parameter NOCOMPRESSUPDATES.
- If a duplicate record is found, the value in the trail file is used and the record that previously exists is discarded.
- If a missing record is found during an update or delete operation that does not affect the GoldenGate key value, it is simply discarded.
As you can see, HANDLECOLLISIONS might not be perfect for all situations. So, as I said earlier, avoid HANDLECOLLISIONS at all costs.
So, having said that, there are several methods to avoid HANDLECOLLISIONS. The main goal is to know the SCN number that you are doing your initial load from. How can you do that. Here are a few ways to accomplish this task.
- Use a Data Guard physical standby. This is done via the following steps on the standby database:
- Stop log apply.
- Capture the current SCN number of the standby database.
- Create a guaranteed restore point.
- Perform initial load from the physical standby
- Either via Datapump Export/Import
- GoldenGate SourceIsTable
- Transportable tablespaces
- Flashback to the restore point
- Continue physical standby
- Use a SAN Copy of the database after shutting down the source
- Use Datapump Export/Import with flashback SCN
- Use Datapump Export/Import with GoldenGate ENABLE_INSTANTIATION_FILTERING
I’m sure that there are other methods that I don’t know about. The point is; HANDLECOLLISIONS is a method of last resort and should be avoided if can possibly instantiate at a known SCN number.