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

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

Shell Prompt>sqlplus scott@orcl
.....................
.....................
Enter password: *****
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

When you try to connect to a remote database from your client machine, you provide a
connect identifier in your connection command, which get resolved into a connect descriptor
depending on the parameter's value in Oracle Net profile (SQLNET.ORA)

connect identifier can be in the form of Net Service Name or Easy Connect or other,
which depends upon the value of NAMES.DIRECTORY_PATH parameter
in the Oracle Net profile (SQLNET.ORA)

  @[<net_service_name> | [//]Host[:Port]/<service_name>]

    <net_service_name> is a simple name for a service that resolves
    to a connect descriptor.

    Example: Connect to database using Net Service Name and the
             database net service name is ORCL.

       sqlplus myusername/mypassword@ORCL

    Host specifies the host name or IP address of the database
    server computer.

    Port specifies the listening port on the database server.

    <service_name> specifies the service name of the database you
    want to access.

    Example: Connect to database using Easy Connect and the
             Service name is ORCL.

       sqlplus myusername/mypassword@Database_Host/ORCL



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

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.


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

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 problem still persists, try to re-create TNS entry in tnsnames.ora

You can find these files in ORACLE_HOME/NETWORK/ADMIN Directory.
Basically this file(s) are to be reside on the client machine as they are required by the
client process to resolve the connect_identifier to connect descriptor.

Sample sqlnet.ora
~~~~~~~~~~~~~~~~~~~~~~~~
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Sample TNS Entry in tnsnames.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

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.


One more Point...........

There is an optional parameter that can be set in sqlnet.ora and that is NAMES.DEFAULT_DOMAIN. Before looking in tnsnames.ora,
sqlnet will check to see if the parameter NAMES.DEFAULT_DOMAIN has been set.
If it is then it’s value will be appended to the connect_identifier supplied
by the user – before searching tnsames.ora for the result.

(SQLNET.ORA)
NAMES.DEFAULT_DOMAIN=mydomain.com

So either remove this from sqlnet.ora OR add the NAMES.DEFAULT_DOMAIN's to connect descriptor
in tnsnames.ora


Sample TNS Entry in tnsnames.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~~

orcl.mydomain.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )