My Oracle Support Banner

OGG target database columns being updated one commit in arrears (Doc ID 2340183.1)

Last updated on FEBRUARY 19, 2019

Applies to:

Oracle GoldenGate - Version 12.1.2.0.0 and later
Information in this document applies to any platform.

Symptoms

GoldenGate 12.2
Classic Extract -> Pump -> Classic Replicat.

Replicat includes some transformations on some columns. For table <TABLE> a number transformations are being performed like this...


"COL=@IF(@VALONEOF(ABA_PBC_CODE,'MAIN','SCHOOLING','CULTUREANDSPORT','RECREATION','FOOD','MEAL','GIFT','ETR','EEC','WELLNESS'),ABA_VAL_1,@COLSTAT(NULL)),"

When a change is made to a source record and committed, the change is replicated to the target, but not for the columns mapped using the above transformation.
Columns NOT being transformed are replicated correctly. When the same record is changed and committed for a second time on the source
system, the non-transformed columns in the target record are again updated, and the transformed columns in the target are updated to the values from the
first change.
So, non-transformed columns are always up to date, transformed columns are 1 change behind.

This behavior is so far unique to this database. The same Replicat with the same parameters is working perfectly in other databases.

 

TESTCASE STEP-BY-STEP INSTRUCTIONS:

update <SCHEMA>.<TABLE> set aba_val_1=11 where aba_id=1;

But I have done the below test case and I found the below.,

SQL> insert into <SCHEMA>.<TABLE> (ABA_ID,ABA_PBC_CODE,ABA_VAL_1,ABA_VAL_2) values (1,'MAIN',1,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from <SCHEMA>.<TABLE>;

ABA_ID ABA_PBC_CODE ABA_VAL_1 ABA_VAL_2 ABA_LAST_ ABA_CREAT
---------- -------------------- ---------- ---------- --------- ---------
1 MAIN 1 2 11-AUG-17 11-AUG-17

SQL> select ABA_ID,ABA_PREPAID_CLEARED_BALANCE,ABA_PREPAID_AVAILABLE_BALANCE,ABA_LAST_REPL
ICATED,ABA_LAST_UPDATED from <TARGET_SCHEMA>.<TARGET_TABLE> where aba_id=1;

ABA_ID ABA_PREPAID_CLEARED_BALANCE ABA_PREPAID_AVAILABLE_BALANCE ABA_LAST_ABA_LAST_
---------- --------------------------- -----------------------------
--------- ---------
1 1 2 11-AUG-17
11-AUG-17

SQL> update <SOURCE_SCHEMA>.<SOURCE_TABLE> set aba_val_1=3 where aba_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from <SOURCE_SCHEMA>.<SOURCE_TABLE>;

ABA_ID ABA_PBC_CODE ABA_VAL_1 ABA_VAL_2 ABA_LAST_ ABA_CREAT
---------- -------------------- ---------- ---------- --------- ---------
1 MAIN 3 2 11-AUG-17 11-AUG-17

SQL> select
ABA_ID,ABA_PREPAID_CLEARED_BALANCE,ABA_PREPAID_AVAILABLE_BALANCE,ABA_LAST_REPL
ICATED,ABA_LAST_UPDATED from <TARGET_SCHEMA>.<TARGET_TABLE> where aba_id=1;

ABA_ID ABA_PREPAID_CLEARED_BALANCE ABA_PREPAID_AVAILABLE_BALANCE ABA_LAST_ABA_LAST_
---------- --------------------------- -----------------------------
--------- ---------
1 1 2 11-AUG-17
11-AUG-17

If you see the above, the target table has not been update. It means the
record did not replicated to the target. But I am performing another update
as below.,

SQL> update <SOURCE_SCHEMA>.<SOURCE_TABLE> set aba_val_1=5 where aba_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from <SOURCE_SCHEMA>.<SOURCE_TABLE>;

ABA_ID ABA_PBC_CODE ABA_VAL_1 ABA_VAL_2 ABA_LAST_ ABA_CREAT
---------- -------------------- ---------- ---------- --------- ---------
1 MAIN 5 2 11-AUG-17 11-AUG-17

SQL> select
ABA_ID,ABA_PREPAID_CLEARED_BALANCE,ABA_PREPAID_AVAILABLE_BALANCE,ABA_LAST_REPL
ICATED,ABA_LAST_UPDATED from <TARGET_SCHEMA>.<TARGET_TABLE> where aba_id=1;

ABA_ID ABA_PREPAID_CLEARED_BALANCE ABA_PREPAID_AVAILABLE_BALANCE ABA_LAST_
ABA_LAST_
---------- --------------------------- -----------------------------
--------- ---------
1 3 2 11-AUG-17
11-AUG-17

In the above output from the target, you could see that the previous update
(1st update) has been replicated now.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.