GoldenGate REPLICAT abend ORA-00001: unique constraint violated on transient PKUpdate in 11.2.0.4 (Doc ID 1928263.1)

Last updated on AUGUST 01, 2020

Applies to:

Symptoms

When doing transient PKUpdate, customer uses the suggested HANDLETPKUPDATE parameter.
the replicat also contains parameter: DBOPTIONS DEFERREFCONST
the replicat hits ora-1.

first test shows that this could be reproduced at sql level:

Following is the test case to reproduce this issue in SQL*Plus:
============
drop user <USERNAME> cascade;
grant connect, resource, unlimited tablespace, dba to <USERNAME> identified by <PASSWORD>;
alter system set enable_goldengate_replication=true;
exec dbms_goldengate_auth.grant_admin_privilege('<USERNAME>');

connect <USERNAME>/<PASSWORD>
alter session set constraints=immediate;
alter session set events '26743 trace name context forever, level 2';
exec dbms_xstream_gg.set_gg_session_flags(1);

create table t1 (i int primary key deferrable);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
commit;
update /*+ RESTRICT_ALL_REF_CONS */ t1 set i = i + 1 where i = 1;
===================

Please note that, following test case works as expected in v11204.
Only difference is that, we didn't specify the hint in UPDATE dml.
=========
drop user <USERNAME> cascade;
grant connect, resource, unlimited tablespace, dba to <USERNAME> identified by <PASSWORD>;
alter system set enable_goldengate_replication=true;
exec dbms_goldengate_auth.grant_admin_privilege('<USERNAME>');

connect <USERNAME>/<PASSWORD>
alter session set constraints=immediate;
alter session set events '26743 trace name context forever, level 2';
exec dbms_xstream_gg.set_gg_session_flags(1);

create table t1 (i int primary key deferrable);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
commit;
update t1 set i = i + 1 where i = 1;
========= 

Cause

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