Oracle GoldenGate - 1403 and KeyCols defination by GoldenGate automatically

********************* READ My update below for quick read **********

Dec 30 2023:

Can we trust the oracle GG's default mechanism to pick keycols for target when there is no primary key or unique key with not null constraint defined?

NO!!!

Oracle GG tries it level best to pick to pick a proper keycol for the target to perform the DMLs.

But at times it fails, so be prepare to face it, in case your table list part of replication doesn't have PK or UK with not null defined.

GG replication will throw 1403 errors during delete, update operations.

Since the keycol it developed at times may not identify the record in target.

For ex.

You may have a source record with below signature...

N,C1,C2,C3 -- attributes

1,A,C,D

Target:

1,B,D,D

In this case if oracle picks N as keycol and N happened to be a unique key, your good.

If oracle GG goes an extra mile and identifies N,C1 as keycols for target.

Then you will get 1403 error (which is data not found in target).

So it is wise, as soon as you face a 1403 error or proactively identify all the tables with non unique identifiers and get your fixup routine ready.

The discard will tell you the reason and report file will tell you the mapping info. These two piece of info should help you identify the cause and fix.

Hope this helps!

Thanks

****************** Update to this blog **********************

Jan 27 2024:

After handling a migration project involving golden gate and working with oracle support, my experience with oracle goldengate improved. I learnt that 1403 error in my case popped up due to the fact, the keycol values are being updated.

Table 1: cola, colb, colc

unique index; cola,colb

OGG, properly picks unique index as the keycol definition for replication.

Imagine if the keycol value is changed meaning updated in source, the corresponding keycol value pair mightnt be available in target, this means a no data found error.

imagine:

1,2,'A'

kecol: 1,2

if this value is changed to 2,3 --> then this wont be found in target.

Oracle assumes your not changing the search field or index field, which is expected as a product.

So it is upto the GG DBA to ensure this is the case. In my it was my responsibility.

Thank you!