Database upgrade to 12.2.0.1 of a 12.1.0.2 cloned database will generate the “ORA-00001:Unique Constraint” error.  “ORA-00001” occurred when updates the sys.I_DAM_LAST_ARCH_TS$ table at PDB level. See below screen shot.

Unique_LI

The following code failing is generating the unique constraint error.


declare
dbid number;
pdbguid varchar2(33);
begin
select dbid into dbid from v$containers
where name = SYS_CONTEXT('USERENV', 'CON_NAME');
select guid into pdbguid from v$containers
where name = SYS_CONTEXT('USERENV', 'CON_NAME');

execute immediate 'update sys.dam_last_arch_ts$ ' ||
'set database_id = :dbid, container_guid = :pdbguid'
using dbid, pdbguid;
commit;
end;
/
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_DAM_LAST_ARCH_TS$) violated

The problem is occurred due to two rows in the sys.dam_last_arch_ts$ table. The database is cloned from other environment and then upgraded. The dbid is changed but original id is still there.

select database_id, container_guid, LAST_ARCHIVE_TIMESTAMP from
sys.dam_last_arch_ts$;

DATABASE_ID CONTAINER_GUID LAST_ARCHIVE_TIMESTAMP
----------- --------------------------------- ---------------------------------------------------------------------------
0 00000000000000000000000000000000 29-SEP-16 10.17.56.000000 AM
1720804891 3EA5250D9C0A3BA2E0530708030A3CB4 15-APR-17 10.17.56.000000 AM

SQL> select DBID from v$database;

DBID
----------
2912915347

So work around is to remove the rows where the value of DBID does not match in v$database or apply “p25717371_122010_Generic.zip” patch. It’s now published Bug: 25717371.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s