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

Problem Description:
--------------------------------------------
Whenever you try to connect your database it returns error ORA-12154.
SQL> conn prod/prod@jupi
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Cause of The Problem:
------------------------------------
The cause of the problem is depends on the which naming method you are using. Suppose if you use tnsnames.ora for naming method then there might be problem in it. I look for oerr command on my unix machine and got,

-bash-3.00$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// - If you are using local naming (TNSNAMES.ORA file):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// - If you are using directory naming:
// - Verify that "LDAP" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Verify that the LDAP directory server is up and that it is
// accessible.
// - Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// - Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// - If you are using easy connect naming:
// - Verify that "EZCONNECT" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Make sure the host, port and service name specified
// are correct.
// - Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.

Solution of The Problem:
-----------------------------------------------
Actually above solution already depicted what to do if you get the problem. My suggest after getting ORA-12154 immediately go as the steps mentioned below.

Step 1: Look for tnsnames.ora and sqlnet.ora.
-----------------------------------------------------------------------
Look for your tnsnames.ora and sqlnet.ora file that you are using. On unix the default location is $ORACLE_HOME/network/admin. You better avoid to use tnsnames.ora. By using easy naming service you can easily avoid this error. Easy naming service is discussed on See the solution part of this post

Step 2: Check both file
---------------------------------------------------------------------------
•After locating both file open it with any viewer like on windows with notepad and on unix with less or more or cat.

•Check within the files whether any illegal character or any unnecessary space exists. If have then remove that.

•Check whether the exact entry exist on the tnsnames.ora that you used in connection identifer. Like if you use conn a/a@jupi then within tnsnames.ora search for only jupi alias.

•You can check your whether there is error or not in the tnsnames alias inside tnsnames.ora by using tnsping. In order to check alias jupi we issue,

bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:25:58

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

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = ((ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
TNS-12533: TNS:illegal ADDRESS parameters
So there is illegal ADDRESS parameter which we see an extra ( before ADDRESS parameter.

If our tnsalias was good, then it would result below with how many miliseconds.
bash-3.00$ tnsping jupi

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 12-OCT-2008 03:28:26

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

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = neptune)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ARJU)))
OK (10 msec)

•Note that with name alias checking tnsnames also tell us which location parameter files it used. Here the location is /oracle/app/oracle/product/10.2.0/db_1/network/admin. So /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora location is used in order to resolve name. However if your tnsnames.ora is in other location then you have to set TNS_ADMIN variable.

•If you see tnsnames.ora is most likely accurate, echo the TNS_ADMIN environment variable.

% echo $TNS_ADMIN

•If nothing is returned, try to set the TNS_ADMIN environment variable to explicitly point to the location of the TNSNAMES.ORA file.

In C Shell, the syntax is:
% setenv TNS_ADMIN full_path_of_tnsnames.ora_file

In K Shell or bash, the syntax is:
% export TNS_ADMIN=full_path_of_tnsnames.ora_file

In windows the syntax is:
set TNS_ADMIN=full_path_of_tnsnames.ora_file

•Now try and see whether error remains. If still you get error then in the SQLNET.ORA file, add the parameter AUTOMATIC_IPC = OFF. If AUTOMATIC_IPC is already set to ON, then change the value to OFF. And try to connect. If still you get error then check the permission of tnsnames.ora and sqlnet.ora. For workaround you can set chmod 777 of these files and try to connect. At this point it is expected you have solved your problem.

Related Documents
ORA-12560: TNS:protocol adapter error on windows machine while starting oracle
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Startup fails with oracle error ORA-00119, ORA-00132