This is a revised version of an article I originally created using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.
Continuing the discussion of “why can’t I connect to my database?”, I’d like to focus on one particular error – ora-12154. For this discussion and demonstration we will use Oracle’s sqlplus command processor as our client, but the principles apply to any client program that uses sqlnet to connect to the database.
Hardly a day goes by that someone doesn’t post a problem on OTN, reporting they are getting an ora-12154 trying to connect to their database. And invariably they are sent on a wild goose chase, checking everything that can go wrong anywhere along the network stack. And yet, ora-12154 means one thing, and one thing only: The client side of sqlnet could not find the specified connect identifier. Period. And the official description of the error, returned by the Oracle utility “oerr” spells it out:
[oracle@vmlnx01 ~]$ oerr ora 12154 12154, 00000, "TNS:could not resolve the connect identifier specified" // *Cause: A connection to a database or other service was requested using // a connect identifier, and the connect identifier specified could not // be resolved into a connect descriptor using one of the naming methods // configured. For example, if the type of connect identifier used was a // net service name then the net service name could not be found in a // naming method repository, or the repository could not be // located or reached.
Or more succinctly: “A connection … was requested using a connect identifier (which) could not be resolved into a connect descriptor.” So what is the connect identifier, and how does it get resolved to a connect descriptor? Let’s take the classic, textbook example of a simple client connection request:
c:> sqlplus scott/tiger@orcl
By the rules Oracle uses to parse the command line, the “@” symbol is used to mark the beginning of the connect identifier. So in this example, the connect identifier is “orcl”, and an ora-12154 means an entry for “orcl” could not be resolved to a “connect descriptor” – the IP address, port number, and service name necessary to properly route the request across the network to the Oracle listener. There are several methods available to make this name resolution, but by far the most common is the use of “local naming” – the tnsnames.ora file. This file serves no other purpose, so you should have it fixed firmly in your mind that it is used only by the client process. It is the client’s “telephone directory”. You may see a tnsnames.ora file on the machine hosting your database, but it is still only used by client processes that happen to run on that machine.
With this preliminary information out of the way, let’s dig in and see how many ways we can create (and conversely, fix) an ora-12154. We will first look at a good configuration, then we’ll start taking it apart. My database is running on an Oracle Enterprise Linux server, while my client is running on my laptop, under Windows XP-Pro. My tnsnames.ora looks like this:
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
mytestdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
Make a connection to the database, just to show that everything is in order.
C:\>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:14:38 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Right out of the textbook! So what could go wrong, to generate an ora-12154?
Wrong naming method
The first thing sqlnet has to do is determine what naming method is being used – exactly how it is to resolve the connect identifier to an IP address, port, and service name. To do this, it looks in the file ‘sqlnet.ora’ (on the client machine, of course!) for the parameter NAMES.DIRECTORY_PATH. If we intend to use local naming, we must set this parameter appropriately:
NAMES.DIRECTORY_PATH=(TNSNAMES)
(Note: the string ‘TNSNAMES’ as a value for this parameter is not the name of the file. It is the name of the method to be used for name resolution. The name of the file used for this method is always tnsnames.ora. )
Let’s set it to some other value (albeit a potentially valid one) . . .
C:>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo rk\admin\sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (LDAP)
. . . and observe the behavior
C:>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:27:46 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ora-12154: TNS:could not resolve the connect identifier specified
In this example, we told sqlnet to use LDAP naming services to resolve the connect identifier. However, LDAP is not available in my test environment, thus it was not able to resolve “mytestdb”. Lesson: If you want to use local naming, you must specify such in the SQLNET.AUTHENTICATION_SERVICES parameter in the client’s sqlnet.ora file. Let’s fix the problem, then move on:
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo rk\admin\sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES) C:\>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:52:33 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Can’t locate tnsames.ora
The entire subject of locating tnsnames.ora was covered in “Locating Oracle’s Network Configuration Files” . For now, I will create the simplest means of not being able to locate the file – I’ll rename it to something else . . .
C:\>cd C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.ora tnsnames.sav C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>dir tnsnames.ora Volume in drive C has no label. Volume Serial Number is 04C2-AD70 Directory of C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN File Not Found C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>
. . . and observe the result:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:57:14 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ora-12154: TNS:could not resolve the connect identifier specified
Of course, we fix it by providing a (valid) tnsnames.ora file:
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.sav tnsnames.ora C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:58:37 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Can’t find connect descriptor in tnsnames.ora
We’ve told sqlnet to use tnsnames, and we’ve made sure sqlnet can find tnsnames.ora. But what if the connect identifier we provide cannot be found in tnsnames.ora? And more importantly, what can cause it to not be found?
Of course, the simplest reason would be that we just gave it the wrong string. In our example file (shown above), we had a single entry, with the connect identifier of ‘mytestdb’. If we provide a different connect identifier it will, of course fail – again with ora-12154:
C:\>sqlplus scott/tiger@fubar SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:05:48 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ora-12154: TNS:could not resolve the connect identifier specified
But there is another factor that comes into play at this point. There is an optional configuration parameter that we have not yet discussed. Before looking in tnsnames.ora, sqlnet will check sqlnet.ora to see if the parameter NAMES.DEFAULT_DOMAIN has been set. If it has, it’s value will be appended to the connect descriptor supplied by the user – before searching tnsames.ora for the result. Let’s add that parameter to our existing sqlnet.ora . . .
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo rk\admin\sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES) NAMES.DEFAULT_DOMAIN=acme.com
. . . and observe the result
C:\>sqlplus scott/tiger@mytestdb SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:13:42 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ora-12154: TNS:could not resolve the connect identifier specified
There are two ways to fix this error. Obviously, we can revert back to our previous configuration by removing the NAMES.DEFAULT_DOMAIN parameter from sqlnet.ora. The other would be to add the domain name to the entry in tnsnames.ora.
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
#===========================
mytestdb.acme.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
C:\>sqlplus scott/tiger@mytestdb
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:30:57 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
tns-03505
That pretty well covers every means of creating an ora-12154 when using local naming. What about the tns-03505 error? Actually, tns-3505 is exactly the same error as ora-12154, with all the same causes and resolutions. The only difference I’ve been able to discover is that tns-03505 is produced when using the ‘tnsping’ utility. Why Oracle chose to code tnsping this way I don’t know, and I’ve been unable to find definitive documentation on this difference but my own testing bears it out.
Using sqlplus . . .
C:\>sqlplus scott/tiger@fubar SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:44:33 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ora-12154: TNS:could not resolve the connect identifier specified Enter user-name:
And using tnsping . . .
C:\>tnsping fubar TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 26-FEB-2 011 16:44:42 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora tns-03505: Failed to resolve name C:\>
(Thanks to Sybrand Bakker for pointing out this distinction).
I will discuss uses and mis-uses of ‘tnsping’ in another post.
This’n’that
There is one often overlooked ‘gotcha’ in all this. Remember that when sqlnet is parsing out the connect string, it uses the ‘@’ sign as a meta-character to mark the beginning of the connect descriptor. So if we have this:
c:>sqlplus scott/tiger@orcl
everything is just fine. But suppose there is an ‘@’ sign someplace unexpected. What happens if someone constructs a complex password that includes it?
c:> sqlplus scott/P@ssW0rd@orcl
In this case, sqlnet will take the ‘P’ as the password, and take ‘ssW0rd@orcl’ as the connect descriptor. By now, you can guess the result.
Conclusion
In this article I have attempted to demonstrate every possible cause of an ora-12154 error, as relates to use of tnsnames.ora, for address resolution of a connect descriptor. There is one overriding factor here that should be obvious from the very description of the error, regardless of the naming method used: ora-12154 is a client problem. It has absolutely nothing to do with anything on the server. It has nothing to do with the listener. It has nothing to do with the database. If you are faced with a ora-12154, you can monkey around with your database and/or listener “until the cows come home” and you will not solve your problem. With ORA-12154 your request never left the client process.
Stay tuned for more discussion of sqlnet connection problems.
Ed Stevens, DBA 