Ora-00001 unique constraint applsys.ad_appl_top_u2 violated năm 2024

This prompted us to check if adgentns.pl has run properly and we found that adgentns.pl also has errored out with below error

java.sql.SQLException: 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

Troubleshooting:

We checked the index APPLSYS.FND_DATABASE_INSTANCES_U1 and found that it was for table APPLSYS.FND_DATABASES_INSTANCES. We also checked the columns on which the index APPLSYS.FND_DATABASE_INSTANCES_U1 was built

SQL> select index_owner,index_name,table_owner,table_name,column_name from dba_ind_columns where index_name='FND_DATABASE_INSTANCES_U1';

INDEX_OWNER INDEX_NAME COLUMN_NAME ----- ---- -- APPLSYS FND_DATABASE_INSTANCES_U1 DB_GUID APPLSYS FND_DATABASE_INSTANCES_U1 INSTANCE_NUMBER

This made us check the xml file on the second database node to to see if the instance number parameter (s_instNumber) was mentioned correctly, but we found that this parameter s_instNumber was having the same number as first database node.

Fix:

Once we made the corrections to the xml files on the database nodes to reflect the right value for s_instNumber, autoconfig ran successfully and also populated the apps.fnd_nodes table correctly

Afterthoughts:

All the xml files in the database nodes had s_instNumber set as first node because when earlier someone has performed the upgrade of database from 11.2.0.1 to 11.2.0.3 had copied the xml file from the first database node to other database nodes and just changed the host names only.

Have you gotten an “ORA-00001 unique constraint violated” error? Learn what has caused it and how to resolve it in this article.

ORA-00001 Cause

If you’ve tried to run an INSERT or UPDATE statement, you might have gotten this error:

ORA-00001 unique constraint violated

This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint.

There are a few solutions to the “ORA-00001 unique constraint violated” error:

1. Change your SQL so that the unique constraint is not violated.
2. Change the constraint to allow for duplicate values
3. Drop the constraint from the column.
4. Disable the unique constraint.

Solution 1: Modify your SQL

You can modify your SQL to ensure you’re not inserting a duplicate value.

If you’re using ID values for a primary key, it’s a good idea to use a sequence to generate these values. This way they are always unique.

You can use the sequence.nextval command to get the next value of the sequence.

So, instead of a query like this, which may not work if the employee_id value is already used:

INSERT INTO employee (employee_id, first_name, last_name)  
VALUES (231, 'John', 'Smith');

You can use this:

INSERT INTO employee (employee_id, first_name, last_name)  
VALUES (seq_emp_id.nextval, 'John', 'Smith');

Assuming the sequence is set up correctly, this should ensure that a unique value is used.’

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

sqlplus /nolog @/fin_app/PROD/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adadmrat.sql APPS appspw PROD PROD tabak finora /fin_app/PROD/inst/apps/PROD_tabak/temp/adadmat_sql.spool

SQL*Plus: Release 10.1.0.5.0 – Production on Thu Nov 17 21:25:00 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected.

++++++++++ Current data ++++++++++

APPL_TOP Names in AD_APPL_TOPS:

*PRESEEDED* GLOBAL ap6002bld ap6003bld finora rws60051app rws60052app tabak

8 rows selected.

++++++++++ Updating tables… ++++++++++

declare * ERROR at line 1: ORA-00001: unique constraint (APPLSYS.AD_APPL_TOPS_U2) violated ORA-06512: at line 16

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Error configuring snapshot information for the source node…cannot proceed with adding the node.

————————– references:

Please read http://oracle-magic.blogspot.com/2008/02/add-new-node-to-apps.html before reading the steps below,

How to resolve ora

The option(s) to resolve this Oracle error are:.

Drop the unique constraint..

Change the constraint to allow duplicate values..

Modify your SQL so that a duplicate value is not created..

What is update ora

There are a few solutions to the “ORA-00001 unique constraint violated” error:.

Change your SQL so that the unique constraint is not violated..

Change the constraint to allow for duplicate values..

Drop the constraint from the column..

Disable the unique constraint..

What does unique constraint violated mean?

During the Load process, if a user receives an error with "unique constraint (...) violated" in it, that means the user submitted an insert transaction that is a duplicate of data already in the database.

What is the underlying DBMS error Ora

The ORA-00001 message is triggered when a unique constraint has been violated. Essentially the user causes the error when trying to execute an INSERT or UPDATE statement that has generated a duplicate value in a restricted field.