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:
- Absent Connect Method
- Missing tnsnames.ora File
- Connect Identifier Mismatch
- Searching for Wrong Domain
- 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
In practice, we usually add an entry by copy-paste from another entry, but sometimes, we 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.
Best explanation..thanks
My pleasure!
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)
Thanks for your input.
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.
Thanks for your information.
Aaa thanks! Mine had a missing bracket 🙂
My pleasure!