integrated replicat does not insert missing Pkupdate with HANDLECOLLISIONS (Doc ID 2086401.1)

Last updated on NOVEMBER 29, 2016

Applies to:

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

in replicat parameter file:
map scott.tab7, TARGET scott.tab8

Symptoms

Following test case shows the symptom.

please note, it happens only when HANDLECOLLISIONS is in use.  The parameter is not recommended for general usage.

With HANDLECOLLISIONS, when a PKUpdate record hits ora-1403 error (the row does not exists in target table), the update will be changed to insert.  The problem here is that the insert does not happen in this scenario with integrated replicat.

 

Example:

=================

Source table: tab7
target table: tab8.


in replicat parameter file:
map scott.tab7, TARGET scott.tab8;

create table scott.tab7 ( col1 number primary key, col2 number not null,col3
number,col4 number );
GGSCI (pnautiya_us-linux as ggs_owner@ggsource) 15> add trandata
scott.tab7,allcols
Logging of supplemental redo data enabled for table SCOTT.TAB7.
TRANDATA for scheduling columns has been added on table 'SCOTT.TAB7'.TRANDATA
for all columns has been added on table 'SCOTT.TAB7'.
GGSCI (pnautiya_us-linux as ggs_owner@ggsource) 16> info trandata scott.tab7
Logging of supplemental redo log data is enabled for table SCOTT.TAB7.
Columns supplementally logged for table SCOTT.TAB7: ALL.

Integrated Replicat
=========================
1) Before setting up replicat
=================================
Source has 2 record
Target has 0 record

2) After setting up replicat
==================================
Source has 2 record
Target has 0 record
SQL> select * from tab7;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2

SQL> update tab7 set col1=3 where col1=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from tab8;
no rows selected <============================ by IR, the missing
PKupadet was not inserted

Classic Replicat
======================
1) Before setting up replicat
=================================
Source has 2 record
Target has 0 record

2) After setting up replicat
==================================
Source has 2 record
Target has 1 record
Source
============
SQL> select * from tab7;
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
3 1 1 1
2 2 2 2

SQL> update tab7 set col1=4 where col1=2;
1 row updated.
SQL> commit;
Commit complete.

Target
===============
SQL> select * from tab8;
COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
4 2 2 2 <======== by CR, the missing
Pkupdate was inserted.

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