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:
Oracle GoldenGate - Version 11.2.1.0.20 and laterInformation in this document applies to any platform.
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
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 |
Cause |
Solution |
References |