Introduction: – While cloning, we just faced the error as ORA-00001 unique constraint violated. In autoconfig log showing as java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated ORA-06512: at “APPS.FND_APP_SYSTEM”, line 507 ORA-06512: at “APPS.FND_NET_SERVICES”, line 951 ORA-06512: at line 1.
Why ORA-00001 unique constraint APPLSYS.FND_DATABASE_INSTANCES_U1 violated?
After looking through many autoconfig logs, we came to the conclusion that the instance name is not correct in FND_DATABASE_INSTANCES.
You can see the error as below in autoconfig logs.
Starting AutoConfig at Tue Oct 4 17:52:09 2022
Using adconfig.pl version 120.32.12020000.6
Classpath : :/prddev/oracle/19.3.0/jdbc/lib/ojdbc8.jar:/prddev/oracle/19.3.0/appsutil/java/xmlparserv2.jar:/prddev/oracle/19.3.0/appsutil/java:/prddev/oracle/19.3.0/jlib/netcfg.jar:/prddev/oracle/19.3.0/jlib/ldapjclnt19.jar
===========================================================================
Starting Utility to Report Version Conflicts at Tue Oct 04 17:52:11 EDT 2022
Using VersionConflictListGenerator.java version 120.4
All driver files processed.
No version conflict encountered.
===========================================================================
Using Context file : /prddev/oracle/19.3.0/appsutil/PRDDEV_prddevsrv.xml
Attempting to create a back up of the Context file
Created back up file of name :
/prddev/oracle/19.3.0/appsutil/out/PRDDEV_prddevsrv/10041752/PRDDEV_prddevsrv.xml
===========================================================================
Starting synchronization of file system Context file and its templates with those in the database
Database connection : Established
------------------------------------------------
##########################################################################
Generate Tns Names
##########################################################################
Classpath : :/prddev/oracle/19.3.0/jdbc/lib/ojdbc8.jar:/prddev/oracle/19.3.0/appsutil/java/xmlparserv2.jar:/prddev/oracle/19.3.0/appsutil/java:/prddev/oracle/19.3.0/jlib/netcfg.jar:/prddev/oracle/19.3.0/jlib/ldapjclnt19.jar
Loading ORACLE_HOME environment from /prddev/oracle/19.3.0
Logfile: /prddev/oracle/19.3.0/appsutil/log/PRDDEV_prddevsrv/10041752/NetServiceHandler.log
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated
ORA-06512: at "APPS.FND_APP_SYSTEM", line 507
ORA-06512: at "APPS.FND_NET_SERVICES", line 951
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout
at oracle.apps.ad.tools.configuration.NetServiceHandler.main(NetServiceHandler.java:3588)
Caused by: Error : 1, Position : 0, Sql = begin FND_NET_SERVICES.register_dbnode( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20, :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 ); end; , OriginalSql = begin FND_NET_SERVICES.register_dbnode( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20, :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 ); end; , Error Msg = ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated
ORA-06512: at "APPS.FND_APP_SYSTEM", line 507
ORA-06512: at "APPS.FND_NET_SERVICES", line 951
ORA-06512: at line 1
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 20 more
adgentns.pl exiting with status 0
ERRORCODE = 0 ERRORCODE_END
SCRIPT RETURNED:
Script Name : txkCfgUtlfileDir.pl
Script Version : 120.0.12020000.15
Started : Tue Oct 4 17:52:23 EDT 2022
Log File : /prddev/oracle/19.3.0/appsutil/log/TXK_UTIL_DIR_Tue_Oct_4_17_52_23_2022/txkCfgUtlfileDir.log
Context file: /prddev/oracle/19.3.0/appsutil/PRDDEV_prddevsrv.xml exists.
Completed : Tue Oct 4 17:52:24 EDT 2022
ERROR DESCRIPTION:
(*******FATAL ERROR*******
PROGRAM : (/prddev/oracle/19.3.0/appsutil/bin/txkCfgUtlfileDir.pl)
TIME : Tue Oct 4 17:52:24 2022
FUNCTION: main::validateAppsSchemaCredentials [ Level 1 ]
ERRORMSG: Invalid APPS database user credentials.
)
ERRORCODE = 1 ERRORCODE_END
.end std out.
The environment settings are as follows ...
ORACLE_HOME : /prddev/oracle/19.3.0
ORACLE_SID : PRDDEV
PATH : /prddev/oracle/19.3.0/perl/bin:/prddev/oracle/19.3.0/perl/bin:/prddev/oracle/19.3.0/bin:/usr/bin:/usr/sbin:/prddev/oracle/19.3.0/appsutil/jre/bin:/usr/ccs/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/prddev/.local/bin:/home/prddev/bin:/home/prddev/.local/bin:/home/prddev/bin:/prddev/rmanagent/bin:.
Library Path : /prddev/oracle/19.3.0/lib:/usr/X11R6/lib:/usr/openwin/lib:/prddev/oracle/19.3.0/lib:/usr/dt/lib:/prddev/oracle/19.3.0/ctx/lib
Executable : /prddev/oracle/19.3.0/bin/sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 4 17:52:30 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
ERRORCODE = 1 ERRORCODE_END
.end std out.
How to fix ORA-00001 unique constraint violated?
To resolve ora-0001 unique error, we need to update the table FND_DATABASE_INSTANCES. Before updating it, make sure backup the table.
Solution:-
select *from FND_DATABASE_INSTANCES;
EA39736E329C3374E053011ECC0A925C PRDDEV 0 PRDDEV EA39736E329E3374E053011ECC0A925C EA39736E329F3374E053011ECC0A925C EA39736E32A33374E053011ECC0A925C 1 04-OCT-22 1 04-OCT-22 0 EA39736E329A3374E053011ECC0A925C prddevsrv EA39736E32A03374E053011ECC0A925C EA39736E32A13374E053011ECC0A925C
Instance name is incorrect.
update the correct instance name:-
create table FND_DATABASE_INSTANCES_bkp as select *from FND_DATABASE_INSTANCES;
update FND_DATABASE_INSTANCES set instance_name='prddevc';
commit;
We will run autoconfig again to check whether issue has been fixed or not.
[prddev@prddevsrv PRDDEV_prddevsrv]$ ./adautocfg.sh
Enter the APPS user password:
The log file for this session is located at: /prddev/oracle/19.3.0/appsutil/log/PRDDEV_prddevsrv/10041802/adconfig.log
AutoConfig is configuring the Database environment...
AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /prddev/oracle/19.3.0
Classpath : :/prddev/oracle/19.3.0/jdbc/lib/ojdbc8.jar:/prddev/oracle/19.3.0/appsutil/java/xmlparserv2.jar:/prddev/oracle/19.3.0/appsutil/java:/prddev/oracle/19.3.0/jlib/netcfg.jar:/prddev/oracle/19.3.0/jlib/ldapjclnt19.jar
Using Context file : /prddev/oracle/19.3.0/appsutil/PRDDEV_prddevsrv.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Updating rdbms version in Context file to db19
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...
AutoConfig completed successfully.We can see here autoconfig successfully completed without any error. If you are facing the same issue, you may just follow the steps mentioned above to resolve the autoconfig error ora-00001 unique contraint error.
Useful Post: –
R12 Autoconfig failed on jtfictx.sh INSTE8_PRF 1 – ORA-29874