ORA-12154 TNS Connection Identifier Solution

Have you gotten an “ORA-12154: TNS:could not resolve the connect identifier specified” error? Learn what causes it and how to resolve it in this article.

ORA-12154 Cause

If you attempt to access or log on to an Oracle database, you might get this error:

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

This means that the tnsnames.ora file was not found or has an error within it.

 

ORA-12154 Solution

There are a few steps you can take to resolve this ORA-12154 error.

 

Check that the tnsnames.ora file exists

There is a tnsnames.ora on both the client and server systems. It’s located in the ORACLE_HOME/network/admin directory. I’ve written a guide to the TNSNAMES file here which has more information.

ORACLE_HOME is where your Oracle database is installed on the server, or on your own computer if you’re using Oracle Express.

For example, in my installed version of Oracle Express, my ORACLE_HOME is:

C:\oraclexe\app\oracle\product\11.2.0\server

If I open the network then admin folders, I will see a tnsnames.ora file.

If it exists in this folder, then you need to check that it has no errors (see the next step).

If it doesn’t exist, then you can create one.

To do this:

1. Create a new file in this folder and call it tnsnames.ora.
2. Open the file in a text editor and add the information in this format:

The syntax of the tnsnames.ora file is:

<addressname> =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
  )
(CONNECT_DATA =
  (SERVICE_NAME = <service_name>)
)
)

 

The example in my Oracle Express instance is:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

So, just copy and paste this into your new tnsnames.ora file, make changes as necessary, and save it.

Try your connection again (the one where you got the error) and see if it works.

Check that TNSNAMES.ORA has no syntax errors

If the file exists, open it and see that there are no syntax errors.

Using the example above:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Check that the brackets are all in the right place, there are no quotes in there, no missing lines or anything unexpected.

 

Check that TNSNAMES.ORA has your service name in it

To be able to connect to your database, the tnsnames.ora file needs to have your service name in it.

Open the tnsnames.ora file and add it in there if it does not exist, using the examples above.

 

Check that TNSNAMES.ORA has read permission

Sometimes, the file can exist and be syntactically correct, but doesn’t have any permissions.

If other users or processes cannot read the file, you’ll get the ORA-12154 error.

So, check that the file can be read by other users by applying read permissions to it.

 

Run the TNSPING Utility

Oracle includes a tnsping utility for checking that the TNSNAMES is OK.

You can find this by going to ORACLE_HOME/bin/tnsping.exe

For example:

C:\oraclexe\app\oracle\product\11.2.0\server\bin

If you’re on Windows, you can open the Command Prompt and CD to this directory.

Then, run tnsping xe (or your service name you want to check)

This should show if it is OK or not.

 

TNSADMIN Environment Variable is Missing

If you’re connecting on Windows, this error can sometimes happen if the TNSADMIN environment variable is missing.

To check this:

1. Go to Start > Control Panel
2. Open System
3. Click “Advanced system settings”
4. Click Environment Variables
5. Add a new system variable called TNSADMIN with a value of ORACLE HOME\network\admin

This is often not needed, but if you’ve tried everything else, and are still getting the ORA-12154 error, you can try adding the TNSADMIN environment variable.

 

So, there are a few solutions to the “ORA-12154: TNS:could not resolve the connect identifier specified” error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!