ORA-00001: unique constraint (APPLSYS.FND_DATABASE_INSTANCES_U1) violated when running Autoconfig

Hi,

Issue:
Sometime back we had a activity to migrate the Front-end/Concurrent Managers hosts only to VMs and the database nodes remained the same.
As part of this activity we ran autoconfig on the database nodes, when running on the second database node we found that the apps.fnd_nodes table was not getting populated with the entries for the second node.
On checking the adconfig.log at location $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDHHMI>, we found that the autoconfig failed to run two scripts afdbprf.sh and adcrobj.sh due to error "ORA-12154: TNS:could not resolve the connect identifier specified"

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.

On closely examining the autoconfig logs run during the upgrade activity we found the same error that we had got when adgentns.pl had run.
But autoconfig had completed sucessfully with no errors as they had not truncated the apps.fnd_nodes table because the FE and database nodes has remained the same during upgrade.

-- Tanveer