Feb 25
Oracle GoldenGate HANDLECOLLISIONS — Part 1
GoldenGate HANDLECOLLISIONS and tables with Primary Key columns…
To HANDLECOLLISIONS or NOHANDLECOLLISIONS is the question — Jigar Doshi
Working with customers over the last 15 years, this is the GoldenGate parameter which keeps coming up daily in my discussions. My friends and colleagues are well aware of my no-love relationship for this parameter.
HANDLECOLLISIONS is touted as a magic bullet by several GoldenGate magicians. However, very few understand the implications of setting this parameter and what it does under the covers. Do you have this parameter enabled in your replication setup? If yes, I highly recommend you keep reading.
Used correctly, it can be very useful during initial load sync. Used incorrectly, you will most likely never be able to remove this parameter without requiring a full data re-sync. It’s something best used on limited occasions for a very limited period. Sometimes I wish this parameter came with parental advisory or mandatory professional license requirement. I have lost track of occasions when this parameter caused a cutover plan abort, or customer escalations and a waste of everyone’s time.
Putting it in layman terms, GoldenGate by default abends when an error is encountered. HANDLECOLLISIONS will silently suppress all data mismatch errors and continue working. You will have peaceful weekends at cost of one big nightmare at a random time.
Murphy’s law states this will happen when you least expect it and at the worst possible time.
Let’s look at examples of the effect of HANDLECOLLISONS on seven different scenarios using a table with a primary key. In part 2 of this series, I will cover similar test scenarios on table without a primary key.
In summary, with HANDLECOLLISIONS an extra record is created on target, and if matching PK does not exist for INSERT/UPDATE. If matching PK exists on both sides, then target record is silently overwritten. Delete operations are ignored on target if matching PK doesn’t exist, else matching records are deleted irrespective of non-key column values.
There are very very few scenarios where you want to end up with extra records on target. Hence use HANDLECOLLISIONS with proper understanding and care.
What can you do if you already have HANDLECOLLISIONS configured in an on-going replication? I can think of 2 choices
- Compare data on source and target. If data matches, remove HANDLECOLLISIONS if not required.
- If data does not match on source and target, only a full resync of data can help resolve the data mismatch.
If it’s so dangerous why does this parameter exist? In the concluding article of this series, I will discuss on some of the scenarios where this parameter can be useful.
Read on if you are interested to see the setup and test results.
--Setup - 21c OCI GG and 19c Autonomous DB
--Integrated EXTRACT and Parallel Non-Integrated REPLICAT
--Source Table
CREATE TABLE SOURCE_TABLE
(
NAME VARCHAR2 (20) ,
CITY VARCHAR2 (20) ,
AGE NUMBER ,
BIRTHDATE DATE ,
MEMBERID NUMBER
)
TABLESPACE DATA ;
CREATE UNIQUE INDEX SOURCE_TABLE_PK ON SOURCE_TABLE
( MEMBERID ASC ) ;
ALTER TABLE SOURCE_TABLE
ADD CONSTRAINT SOURCE_TABLE_PK PRIMARY KEY ( MEMBERID )
USING INDEX SOURCE_TABLE_PK ;
-- Target TABLE
CREATE TABLE TARGET_TABLE
(
NAME VARCHAR2 (20) ,
CITY VARCHAR2 (20) ,
AGE NUMBER ,
BIRTHDATE DATE ,
MEMBERID NUMBER
) ;
CREATE UNIQUE INDEX TARGET_TABLE_PK ON TARGET_TABLE
( MEMBERID ASC ) ;
ALTER TABLE TARGET_TABLE
ADD CONSTRAINT TARGET_TABLE_PK PRIMARY KEY ( MEMBERID )
USING INDEX TARGET_TABLE_PK ;
EXTRACT Parameters --
EXTRACT EHCPK
USERIDALIAS <aliasname> DOMAIN <domainname>
EXTTRAIL pk
TABLE SOURCE_TABLE;
REPLICAT Parameters --
REPLICAT RHCPK
USERIDALIAS <aliasname> DOMAIN <domainname>
HANDLECOLLISIONS
MAP SOURCE_TABLE, TARGET TARGET_TABLE;The above script has all table creation and EXTRACT/REPLICAT params
INSERT Test 1
EXTRACT and REPLICAT created but stopped
Before starting replication insert row on target to generate conflict. Note that target record has differing value for City and PK MEMBERID is same.
Source Record
=============
Target RECORD
=============
NAME CITY AGE BIRTHDATE MEMBERID
------ --------- --- --------------------- --------
Andrew Rotterdam 21 1/1/2023, 12:00:00 AM 1234 Start EXTRACT and REPLICAT
Test — INSERT a record on source with MEMBERID=1234 and CITY Amsterdam
Result — Target record is overwritten with source values and no error reported. City value matches at target with source:
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
------ --------- --- --------------------- --------
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234
Target Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
------ --------- --- --------------------- --------
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234INSERT Test 2
EXTRACT and REPLICAT created but stopped.
Target row is inserted directly with non-matching key value i.e., 2222 and 3333.
Test — Insert source row with MEMBERID 2222
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
------ --------- --- ---------------------- --------
Bob Berlin 31 1/21/2023, 12:00:00 AM 2222
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234
Target Record
==============
Bob Berlin 31 1/21/2023, 12:00:00 AM 3333
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234Start EXTRACT and REPLICAT.
Result — A new target row is created for MEMBERID=2222 in target table. A net result of one additional row in target table.
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
------ --------- --- ---------------------- --------
Bob Berlin 31 1/21/2023, 12:00:00 AM 2222
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234
Target Record
==============
Bob Berlin 31 1/21/2023, 12:00:00 AM 2222
Bob Berlin 31 1/21/2023, 12:00:00 AM 3333
Andrew Amsterdam 21 1/1/2023, 12:00:00 AM 1234UPDATE Test 1
Both EXTRACT and REPLICAT are started. Target row is updated directly such that age value differs on source and target.
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ---------------------- --------
Bob Berlin 31 1/21/2023, 12:00:00 AM 2222
Target Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ---------------------- --------
Bob Berlin 41 1/21/2023, 12:00:00 AM 2222 Test — Update source City from Berlin to Bremen.
Result — Target age value is overwritten with source values.
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ---------------------- --------
Bob Bremen 31 1/21/2023, 12:00:00 AM 2222
Target Record
==============
Bob Bremen 31 1/21/2023, 12:00:00 AM 2222 UPDATE Test 2
Both EXTRACT and REPLICAT are started.
Update target MEMBERID from 2222 to 2234 directly on target to create a Missing PK on target.
Source Record
=============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ---------------------- --------
Bob Bremen 31 1/21/2023, 12:00:00 AM 2222
Target Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ---------------------- --------
Bob Bremen 31 1/21/2023, 12:00:00 AM 2234Test — Update Source City from Bremen to Bonn.
Result — A new record is inserted on target. Update on source, inserts a new record on target due to missing PK.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ------ --- ------------------------------
Bob Bonn 31 1/21/2023, 12:00:00 AM 2222
Target Record
==============
Bob Bremen 31 1/21/2023, 12:00:00 AM 2234
Bob Bonn 31 1/21/2023, 12:00:00 AM 2222 DELETE Test 1
Both EXTRACT and REPLICAT are started.
Target Record — MEMBERID updated directly on target to create a Missing PK on target.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- --------- --- ---------------------- --------
Bob Bonn 31 1/21/2023, 12:00:00 AM 2222
Target Record
==============
Bob Bonn 31 1/21/2023, 12:00:00 AM 5678Test — Delete from source MEMBERID=2222
Result — Record is deleted from source and missing record silently ignored on target.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- --------- --- ---------------------- --------
Target Record
==============
Bob Bonn 31 1/21/2023, 12:00:00 AM 5678 DELETE Test 2
Both EXTRACT and REPLICAT are started.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- --------- --- ---------------------- --------
Target Record
==============
Bob Bonn 31 1/21/2023, 12:00:00 AM 5678Test — Delete from source MEMBERID=5678. This will delete 0 records since no record exists on source.
Result — no impact to Target.
Target Record
==============
Bob Bonn 31 1/21/2023, 12:00:00 AM 5678DELETE Test 3
Both EXTRACT and REPLICAT are started.
Target record— city and age updated directly on target.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ---- --- ---------------------- --------
Bob Bonn 31 1/21/2023, 12:00:00 AM 3333
Target Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ---- --- ---------------------- --------
Bob Frankfurt 41 1/21/2023, 12:00:00 AM 3333 Test — Delete from source MEMBERID=3333
Result — Record is deleted on target.
Source Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ---- --- ---------------------- --------
Target Record
==============
NAME CITY AGE BIRTHDATE MEMBERID
---- ---- --- ---------------------- --------
Thank you for reading. Hope this has been informative. Leave a comment or just a hello in the comments.
If you haven’t already done so, you can sign up for an Oracle Cloud Free Tier account today. It’s not necessary to follow along to with this post, if you’re curious about how to get started with OCI, signing up is the first step!
If you’re curious about the goings-on of Oracle Developers in their natural habitat, come join us on our public Slack channel!
