OGG target database columns being updated one commit in arrears

(Doc ID 2340183.1)

Last updated on DECEMBER 18, 2017

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 ACCOUNT_BALANCE a number transformations are being performed like this...


"ABA_PREPAID_CLEARED_BALANCE =
@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 SOURCE.account_balance 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 SOURCE.account_balance
(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 source.account_balance;

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.EDAT_ACCOUNT_BALANC
E 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.account_balance set aba_val_1=3 where aba_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from source.account_balance;

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.EDAT_ACCOUNT_BALANC
E 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.account_balance set aba_val_1=5 where aba_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from source.account_balance;

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.EDAT_ACCOUNT_BALANC
E 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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms