Info : >> Steps To Clean Nonexistent Nodes Or IP Addresses From FND_NODES

After completing the ADCLONE process, or implementing Autoconfig, the FND system tables have incorrect values. The OAM Dashboard reflects the target machine name and the source machine name although the source machine is no longer being used. In some cases, there are IP addresses from nonexistent machines in FND_NODES.

The workflow agent listeners fail to start reflecting the following errors posted within the log file:

oracle.apps.fnd.cp.gsc.SvcComponentContainerException:
oracle.apps.fnd.wf.common.ContextFactoryException: Error instantiating AppsContext ->
AOLJ_JAVA_EXCEPTION (MESSAGE=ProfileCache: getLocalJDBCConnection() == null)
AOLJ_JAVA_EXCEPTION (MESSAGE=Not able to create new database connection: FND SQL_PLSQL_ERROR in
FND_SECURITY_PKG.FND_ENCRYPTED_PWD
SECURITY_INVALID_DBC_PARAMETER (PARAMETER=GUEST_USER_PWD)
(FILE=/kirang/vol01/oracle/GINNI/GINNIappl/fnd/11.5.0/secure/GINNI_kiranginni/GINNI.dbc)
(ROUTINE=AppsConnectionManager.makeGuestConnection)
ORA-01422: exact fetch returns more than requested number of rows
SQL_PLSQL_ERROR (ROUTINE=FND_SECURITY_PKG.FND_ENCRYPTED_PWD) (ERRNO=-1422) (REASON=ORA-01422:
exact fetch returns more than requested number of rows)
at oracle.apps.fnd.cp.gsc.SvcComponentContainer.getNewWorkflowContext(SvcComponentContainer.java:1321)

Could not start Service Component Container because an unexpected RuntimeException or other
Throwable occurred -> oracle.apps.fnd.cache.CacheException
oracle.apps.fnd.cache.CacheException
at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:228)
at oracle.apps.fnd.profiles.Profiles.getProfileOption(Profiles.java:1464)
at oracle.apps.fnd.profiles.Profiles.getProfile(Profiles.java:333)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfileFromDB(ExtendedProfileStore.java:210)

Cause
The Nodes in the OAM Dashboard are retrieved from the FND_NODES table. Further investigation showed that the FND_NODES table had both node names and node_modes 'O' as activated.

ie:
SQL> select node_name "Node Name", node_mode "Mode", support_cp "C",
support_web "W", support_admin "A", support_forms "F"
from FND_NODES;

NODE_NAME N S S S S
------------------------------ - - - - -
TARGET O Y Y Y Y
SOURCE O Y Y Y Y

Solution

Do not directly Delete from FND_NODES table because the support_' columns will be affected and will not incorporate the correct information throughout in OAM. As documented in OAM.G Documentation, Note: 226826.1 About Oracle Applications Manager Mini-Pack 11i.OAM.G, the data cleanup issues with cloning that surface in the OAM UI have been fixed in the latest cloning patches.

1. Retrieve the latest patch for the cloning tools:

2. After applying the above patch to the system that is to be cleaned, run the following in SQL*Plus. Note that this will delete all data from system tables such as FND_NODES, FND_OAM_CONTEXT_FILES, etc. The correct information for the current system will be repopulated when AutoConfig is run.

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

3. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers, to repopulate the required system tables.