GoldenGate

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UPDATEDELETE - Updating a Live Data

Laxman Sharma4 days ago

Hello Members,

We have a source table whose directory structure is as below.

Name Null? Type
----------------------------- -------- --------------------
EMPLOYEEID NUMBER(10) >>>>>>>>> Primary Column
FIRSTNAME VARCHAR2(50 CHAR)
LASTNAME VARCHAR2(50 CHAR)
MOBILE NUMBER(10)

We have a target table whose directory structure is as below.

Name Null? Type
----------------------------- -------- --------------------
EMPLOYEEID NUMBER(10) >>>>>>> Not a primary column
FIRSTNAME VARCHAR2(50 CHAR)
LASTNAME VARCHAR2(50 CHAR)
MOBILE NUMBER(10)
LastOperation CHAR(1 CHAR)
SynchTimestamp TIMESTAMP(6)
SEQ_TEST NOT NULL NUMBER

Target table had 3 extra column which is derived on the target side.

The below is my extract parameter

EXTRACT *****
SETENV (ORACLE_HOME = "/u02/app/oracle/product/19.0.0.0/dbhome_1")
SETENV (TNS_ADMIN = "/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/*****")
useridalias ****
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
WARNLONGTRANS 1HOURS, CHECKINTERVAL 30MINUTES
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00 ON SUNDAY
encrypttrail AES256 keyname key1
exttrail /oragg/gghome_1/dirdat/ABC/cn
MAP OWNER.TEST;

Below is my replicat file

REPLICAT ******
SETENV (ORACLE_SID='******')
SETENV (ORACLE_HOME="/u02/app/oracle/product/19.0.0.0/dbhome_4")
SETENV (TNS_ADMIN ='/u02/app/oracle/product/19.0.0.0/dbhome_4/network/admin/****')
useridalias *****
decrypttrail AES256 KEYNAME key1
UPDATEDELETES
REPERROR (DEFAULT, EXCEPTION)
map OWNER.TEST, Target OWNER.TEST,colmap(usedefaults,SEQ_TEST=@COLSTAT(MISSING), "LastOperation" = @CASE (@GETENV('GGHEADER', 'OPTYPE'), 'INSERT','I', 'DELETE', 'D','TRUNCATE','T', 'U'), "SynchTimestamp" = @GETENV('GGHEADER', 'COMMITTIMESTAMP')), KEYCOLS(EMPLOYEEID) ;

Target Side has 3 extra column will be populated accordingly as per parameter in replicat.

Part 1: -

When i insert 1 row on source , it gets replicated to target. LastOperation is marked as I SyncTimestamp is marked as commit timestamp and SEQ_TEST is the identity column which is generated via a sequence.
When i update the same row , same row on target gets updated and OperationType is now changed to U and commit timestamp is changed.
When i delete the same row , same row is not deleted on target , infact OperationType is marked as D and with the Latest Timestamp.
Scenario 1 working fine until now.

Part 2 (Continuation of same row)
Now when i insert a same row on the source , it gets added on the source and target. On the Target the OperationType is marked as I and now for the same data we have 2 row One with OperationType D and other with OperationType I.

Now here the problem starts.

When i update the same row on source. Replicat is updating a row which is having a flag D and not updating the latest data which is with OperationType I.
Same behaviour is observed for Delete also.

This problem is only arising when we are inserting a same primary key on source which was previosly deleted and on the target side that row is present and marked as D.

Do we have any suggesstion how to force Goldengate to always update the live data on the target rather than updating the old data.

Example
==========

Source
======

SQL> INSERT INTO OWNER.TABLE_NAME (EMPLOYEEID, FIRSTNAME, LASTNAME, MOBILE) VALUES (81, '*****', '****', 123456789);

1 row created.

SQL> commit;

Commit complete.

SQL>

Target
========

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE L SynchTimestamp SEQ_TEST
---------- -------------------- -------------------- ---------- - ------------------------------ ----------
81 ***** **** 123456789 I 13-MAR-26 05.21.26.001494 PM 117

SQL>

Source
========

SQL> update OWNER.TABLE_NAME set FIRSTNAME='WWWWWW' where EMPLOYEEID=81;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE
---------- -------------------------------------------------- -------------------------------------------------- ----------
81 WWWWWW **** 123456789

Target
=========

EMPLOYEEID FIRSTNAME LASTNAME MOBILE L SynchTimestamp SEQ_TEST
---------- -------------------- -------------------- ---------- - ------------------------------ ----------
81 WWWWWW **** 123456789 U 13-MAR-26 05.22.42.001470 PM 117

Source : -
========

SQL> delete from OWNER.TABLE_NAME where EMPLOYEEID=81;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

no rows selected

SQL>

target :
==========

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE L SynchTimestamp SEQ_TEST
---------- -------------------- -------------------- ---------- - ------------------------------ ----------
81 WWWWWW **** 123456789 D 13-MAR-26 05.23.49.001464 PM 117

All good until now and working as expected.

part 2: -

source : -
==========

SQL> INSERT INTO OWNER.TABLE_NAME (EMPLOYEEID, FIRSTNAME, LASTNAME, MOBILE) VALUES (81, '*****', '****', 123456789);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE
---------- -------------------------------------------------- -------------------------------------------------- ----------
81 ***** **** 123456789

target :
=========

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE L SynchTimestamp SEQ_TEST
---------- -------------------- -------------------- ---------- - ------------------------------ ----------
81 WWWWWW **** 123456789 D 13-MAR-26 05.23.49.001464 PM 117
81 ***** **** 123456789 I 13-MAR-26 05.25.00.001540 PM 118

SQL>

Everything is as expected until now

Source : -
=============

SQL> update OWNER.TABLE_NAME set MOBILE=99999999 where EMPLOYEEID=81;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

EMPLOYEEID FIRSTNAME LASTNAME MOBILE
---------- -------------------------------------------------- -------------------------------------------------- ----------
81 ***** **** 99999999

SQL>

target : -
===========

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

problem it has updated the old data and not the new data. now the data with LastOperation D is no longer available.

Same problem with Delete also

Source
==========

SQL> delete from OWNER.TABLE_NAME where EMPLOYEEID=81;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

no rows selected

SQL>

Target
========

SQL> select * from OWNER.TABLE_NAME where EMPLOYEEID=81;

SQL>

problem again updating the old data and not the new live data which is with SEQ_TEST 118.

is their any way we can force goldengate to update the latest live data?

Added 4 days ago

#updatedeletes

0 comments

20 views