TNS Name

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

ORA-12154 means that your connection tools including 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 Local Naming 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.

1. ORA-12154 due to Absent Local Naming Method

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.

Solution

Let’s add TNSNAMESNAMES.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.

The problem was solved.

Easy Connect Naming Method

For the same reason, if you are going to use Easy Connect Naming 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/hr@ora11g.example.com:1521/orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

2. ORA-12154 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

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.

3. ORA-12154 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

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.

4. ORA-12154 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

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.

5. ORA-12154 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.42.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPAPP)
      (SERVER = DEDICATED)
    )
 

Can you tell the problem in the entry? Yes, I missed one parenthesis. Even though the connect identifier is correct, but its connect descriptor is wrong, which will cause ORA-12154 eventually. The correct one should be like this:

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

Now the connect identifier is fine.

Leave a Reply

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