Skip to content
Home ยป Oracle ยป How to Resolve ORA-12154: TNS:could not resolve the connect identifier specified

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

ORA-12154

ORA-12154 means that your connection tools (e.g. sqlplus) cannot use TNSNAMES naming method for some reason.

This reminds me that there're same error patterns in TNS-03505: Failed to resolve name. In that post, I used tnsping to test the connectivity to the listener.

In fact, all symptoms in ORA-12154 will be found in TNS-03505. The difference is, ORA-12154 will be seen in sqlplus or other connection tools, TNS-03505 will be seen in tnsping or listener logs.

There're several possible causes that may throw ORA-12154:

  1. Absent Connect Method
  2. Missing tnsnames.ora File
  3. Connect Identifier Mismatch
  4. Searching for Wrong Domain
  5. Missing Parenthesis

Let's do some tests to reproduce the error by sqlplus.

Due to Absent Connect Method

There could be one of connect methods is missing from your settings.

Local Naming Method is Absent

First of all, we set TNS_ADMIN environment variable explicitly.

C:\Users\Ed>set TNS_ADMIN=C:\app\client\Administrator\product\12.2.0\client_1\network\admin

Check the content of sqlnet.ora. There's no TNSNAMES naming method.

C:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (EZCONNECT)

Then we tried to connect to the database.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:02:33 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

We saw ORA-12154 in sqlplus. This is because we have no TNSNAMES naming method to support our connection.

Easy Connect Method is Absent

For the same reason, if you are going to use Easy Connect method to connect to the database, you have to make sure that EZCONNECT is in the list of NAMES.DIRECTORY_PATH. Otherwise, you will get ORA-12154 like this:

SQL> conn hr/[email protected]:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution to ORA-12154

Let's make sure TNSNAMES and EZCONNECT naming method are added back to NAMES.DIRECTORY_PATH.

C:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Then we tried to connect to the database again.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:05:24 2019

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

SQL> conn hr/hr@ORCL
Connected.
SQL> conn hr/[email protected]:1521/orcl
Connected.

ORA-12154 was solved.

Due to Missing tnsnames.ora File

I deliberately deleted tnsnames.ora for testing the effect.

C:\Users\Ed>dir /w %TNS_ADMIN%\tnsnames.ora
 Volume in drive C has no label.
 Volume Serial Number is C4BB-3A0E

 Directory of C:\app\client\Administrator\product\12.2.0\client_1\network\admin

File Not Found

Then we tried to connect to the database.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:12:14 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

This is because sqlplus found no file to lookup the connect identifier.

Solution to ORA-12154

Let's create a new tnsnames.ora or restore the original one.

C:\Users\Ed>dir /w %TNS_ADMIN%\tnsnames.ora
 Volume in drive C has no label.
 Volume Serial Number is C4BB-3A0E

 Directory of C:\app\client\Administrator\product\12.2.0\client_1\network\admin

tnsnames.ora
               1 File(s)            388 bytes
               0 Dir(s)  179,702,697,984 bytes free

Then we tried to connect to the database again.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:20:41 2019

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

SQL> conn hr/hr@ORCL
Connected.

Due to Connect Identifier Mismatch

I deliberately changed the connect identifier while connecting.

SQL> conn hr/hr@ORCL123
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution to ORA-12154

We should use the correct connect identifier to connect to the database.

SQL> conn hr/hr@ORCL
Connected.

It told us that we should use a matched connect identifier which also exists in tnsnames.ora.

Due to Searching for Wrong Domain

Some database environments which have been set the default domain for search may result ORA-12154 if some connect descriptors are wrongly configured.

C:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = example.com

Then we tried to connect to the database.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:31:50 2019

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

SQL> conn hr/hr@ORCL
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Solution to ORA-12154

In such situation, we can turn it off by commenting out NAMES.DEFAULT_DOMAIN:

C:\Users\Ed>type %TNS_ADMIN%\sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
#NAMES.DEFAULT_DOMAIN = example.com

Then we tried to connect to the database again.

C:\Users\Ed>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 7 19:33:55 2019

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

SQL> conn hr/hr@ORCL
Connected.

We connected.

Due to Missing Parenthesis

Sometimes, you may not notice that there's one parenthesis is missing from the connect descriptor, usually it's the right side one. Let's take an entry in tnsnames.ora for example.

ERPAPP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPAPP)
      (SERVER = DEDICATED)
    )
 

Can you tell the problem in the entry? Yes, I missed one parenthesis, a right round bracket. Even though the connect identifier is correct, but its connect descriptor is wrong, which will cause ORA-12154 eventually.

Solution to ORA-12154

To correct the structure of connect descriptor, we should make them paired:

ERPAPP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPAPP)
      (SERVER = DEDICATED)
    )
  )

Now the connect identifier is fine.

8 thoughts on “How to Resolve ORA-12154: TNS:could not resolve the connect identifier specified”

  1. One more cause that I found out, especially on Linux for this error message is that Oracle may also not be able to authenticate you if your SQLNET.ORA does not specify the right authentication protocols. In this case, I had to specify SQLNET.AUTHENTICATION_PROTOCOL=(ALL,NONE)

  2. Also, while other clients (such as Oracle SQL Developer) may handle a password with an @ character well, sqlplus does not. I guess it messes up the username/password@connect_identifier notation somehow. In the end you get an ORA-12154 which is totally misleading.

Leave a Reply

Your email address will not be published. Required fields are marked *