ORA-12154

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

What causes this error?

An ORA-12154 error occurs when a user attempts to logon to an Oracle database. The tnsnames.ora file was not found or has a syntax error. The Oracle communications software (SQL*Net or Net8) did not recognize the database connect descriptor. For GUI utilities, this is the name entered in the third field of the ORACLE logon box.

How to fix it

Incorrect Syntax

A syntax error anywhere in tnsnames.ora can cause this message. Check for unmatched parentheses or stray characters. For example, if host string=oracle; database IP address=192.168.100.1 and sid=ss then it should look like this:

oracle.world =
 (DESCRIPTION =
  (ADDRESS =
   (PROTOCOL = TCP)
   (Host = 192.168.100.1)
   (Port = 1521)
  )
  (CONNECT_DATA = (SID = ss)
 )
)

Missing, wrong location, or bad tnsnames.ora

Ensure that you do have a tnsnames.ora file defined on the client and in the tnsnames.ora is the descriptor alias that you are specifying in the connect string. Some users have their own copies of this file stored on their local hard disks. Others use a shared version kept in an OID or Oracle Names Server. Also ensure that the file can be accessed (chmod 644).

The tnsnames.ora can be in one of 3 locations. Oracle first checks to see if TNS_ADMIN is set and looks in the directory the environment variable is pointing to. Secondly it checks /etc Finally it checks $ORACLE_HOME/network/admin.

The database name isn't in the tnsnames.ora file

Check to see that the database name exists in one of the TNSNAMES.ORA files, and add it if necessary.

If you are using Oracle Names

Use the Names Control Utility STATUS or PING commands to see if a Names Server is available. For example, enter the following:

namesctl status names_server_name

Use the Names Control QUERY command to find out if the service name and address are stored on a Names Server.

For example, enter the following:

namesctl query service_name *