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

Questions on this error seem to be a regular occurrence on Oracle related forums so I decided to add a post about it here. This post will demonstrate the error, how to look up the error and finally how to resolve the error.

From a client machine you attempt to make a connection to a remote database and receive the following error.

[oracle@ora2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 28 10:51:56 2010

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

SQL> connect hr@proddb
Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

We have received the error ORA-12154 when attempting to connect to the database proddb. Before speculating on what could be the cause of the error, you should first look up the error. Oracle provides a utility called oerr that can be used to provide details on Oracle Error codes. For more information on oerr see the post Using the OERR Utility to Aid in Error Investigation

Below we see the output of oerr ora 12154.

[oracle@ora2 ~]$ 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.
[oracle@prod ~]$ 

In short an ORA-12154 message simply means that TNS is unable to resolve the database using the naming method configured on the client. An ORA-12154 does not indicate a problem with the listener for the database.

The most common method of name resolution is local naming using a TNSNAMES.ORA file. You can determine the naming method used by looking at the SQLNET.ORA file found in ORACLE_CLIENT/network/admin.

The NAMES.DIRECTORY_PATH details the name resolution method to be used. In the case below database names will first be resolved using local naming (TNSNAMES) and then Easy Connect (EZCONNECT) if local naming fails.

[oracle@ora2 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

[oracle@ora2 ~]$ 

In this example local naming is used so databases need to be defined in the file TNSNAMES.ORA located in ORACLE_CLIENT/network/admin. Below are the contents of the TNSNAMES.ORA file in this example.

[oracle@ora2 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl10g)
    )
  )

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

[oracle@ora2 ~]$ 

Notice there is no entry for the database PRODDB which is the cause of the ORA-12154. In order to add an entry for PRODDB we will need to know the following information: the name of the server in which PRODDB resides, the port number the listener for PRODDB is listening (default 1521), and protocol used to connect (default TCP).

You can add the entry either by directly editing the ORACLE_CLIENT/network/admin/tnsname.ora file or you can use Oracle Net Manager to add the entry. This document will detail the steps to add an entry using Oracle Net Manager.

Oracle Net Manager is started by issuing netmgr on the command line. When Oracle Net Manager displays, expand the Local node and select the Service Naming node.

Click the green + sign to bring start the Net Service Name Wizard.

The Net Service Name is the name you wish to use to connect to the database. While it is common to have the Net Service Name match the database service name, it is not required for them to be the same. Enter the Net Service Name and click the Next button.

Next select the protocol to be used to connect to the database. TCP/IP is the most commonly used protocol. Select the protocol and click the Next button.

The name of the server hosting the database and the port number used by the listener are provided on this screen. In most cases you can use the default port of 1521. Provide the name of the server hosting the database and click the Next button to continue.

Here we provide the service name for the database. You can optionally choose the connection type to be made by clicking the drop down to the right of Connection Type. In most cases allowing the database to decide the connection type (Database Default) will be sufficient. Click the Next button to continue.

After providing all of the information needed for the TNSNAMES entry you can optionally test the configuration by clicking the Test button. The test attempts a connection as the user SCOTT with a password of TIGER.

On most systems the SCOTT account will be locked so such a test will provide a successful failure. Success in that a connection was successfully made to the database using supplied information, failure in that the account used was locked. If you would like to test with different credentials click the Change Login button and supply a valid user name password and click OK. Click the Test button to perform the test again.

After you satisfied with the test results click the Close button to complete the testing and to return back to wizard. Click the Finish button on the wizard.

Be sure to save the changes by clicking the File Menu and selecting Save Network Configuration. After saving your changes you can exist Oracle Net Manager by selecting File and then Exit.

If you would like to further validate that the entry was saved correctly you attempt a connection to the database.

[oracle@ora2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 28 13:59:38 2010

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

SQL> connect hr@proddb
Enter password: 
Connected.
SQL>