Tuesday, October 27, 2009

ORA-12154: TNS:could not resolve the connect identifier specified

Background:
We were in the middle of a project and we wanted to have restricted access to our database environment. We have ldap name resolution method implemented in the organisation and this means that anyone who has his or her ldap.ora setup correctly will be able to connect to any database across the organization, provide they have the login credentials. We wanted to restrict individuals from accessing our database, where as development team had a requirement to ensure that db links in remote databases were able to connect to the database undergoing project patching at all times.
We disabled the ldap entries by deleting them and provided the tnsnames entries for the remote databases which would connect to our database over db link. However, once the ldap entries were removed, the remote database failed to connect to our database with the following error:
ORA-12154: TNS:could not resolve the connect identifier specified

Correct tnsentries were made in $ORACLE_HOME/network/admin/tnsnames.ora.

Fix:
Detailed troubleshooting revealed that the environment was not sourced properly while starting the remote database. The $TNS_ADMIN variable was pointing to /var/opt/oracle location where as it should have been pointing to $ORACLE_HOME/network/admin. Since we could not bounce the remote database in between, we temporarily put the entry in /var/opt/oracle/tnsnames.ora in remote database and then the db link started working.
This situation would not arise if environment was sourced properly before starting up the database, which would then point to the correct $TNS_ADMIN.
-Aravind Kamath Posral

No comments: