This is a revised version of an article about ORA-12545 that I originally published while 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.
Introduction to ORA-12545
Continuing our discussion of “Why can’t I connect to my database”, I want to focus on “ORA-12545: Connect failed because target host or object does not exist”.
To recap what we’ve covered so far, when an oracle client requests a connection to a database, it has to provide a “connect identifier”, which sqlnet then translates to a “connect descriptor”. The connect descriptor specifies the transport protocol (usually tcp), the ip address of the database server, the port being used by the listener, and the service name the database has registered with the listener. This name resolution is usually done by looking up the connect identifier in the client’s tnsnames.ora file, though there are other methods available as well. Failure to find an entry from which to derive the connect descriptor will result in an ORA-12154: TNS:could not resolve the connect identifier specified, which I explained here.
Once the connect descriptor is determined, that information becomes part of the packet that is passed to the transport layer of the standard OSI network stack and is thus used for standard network routing. This is important to understand. At this point, the routing of the packet to its destination is handled entirely by standard network transport layers.
The Setup
For this demonstration, I am using an Oracle 10.2.0.4 client on Windows XP, connecting to an Oracle 10.2.0.4 database on Oracle Enterprise Linux 5. Name resolution is through tnsames.ora, which looks like this:
fred =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
Listing 1.
(By the way, I like to use “fred” as an alias name in demonstrations because it should be obvious that it is a totally artificial name that doesn’t have any inherent relationship to anything. As in the above tnsnames example, in a production system I would use the service name (vlnxora1) as the connect identifier, but in demonstrations I want the distinction to be crystal clear.)
The key information we are focusing on is the “HOST = vmlnx01” on line 4.
First, let’s make a good connection to prove that everything is working correctly, then we will break it.
C:\>sqlplus scott/tiger@fred SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 09:45:17 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>
Listing 2.
The Demonstration
We know that in order for a message to be routed across a network, we need an ip address. This is like placing a telephone call. If I want to call Moe, I can’t just pick up my phone and dial “Moe”. The telephone company switchboards don’t know anything about Moe. There has to be some mechanism to translate “Moe” to 1-555-123-4567. Likewise the network routers don’t know anything about “vmlnx01”. For your telephone you would have some sort of directory to tell you (or your smart phone!) what Moe’s number is. In a corporate environment, you probably have a DNS server to tell the network stack that “vmlnx01” is 192.168.160.101. The other mechanism, and the one that trumps a DNS lookup, is a file on the client, named simply “hosts”. On unix, this will be at /etc/hosts. On my Windows XP machine it is at C:\WINDOWS\system32\drivers\etc\hosts. Given Microsoft’s propensity for reshuffling the deck with each new release of Windows, I can’t promise that is where the file should be located on your machine!
My hosts file looks like this:
127.0.0.1 localhost 192.168.160.101 vmlnx01 vmlnx01.vmdomain
Listing 3.
For those not familiar with this file, the format is
ipaddress alias1 alias2 .... aliasN
Listing 4.
All hosts files should have the same first line, equating ip address 127.0.0.1 to the alias “localhost”. All other entries typically have two aliases, one with the unqualified server name, the other with the fully qualified servername.domain. That is by convention and for everyone’s convenience, but the fact is these are just aliases for the ip address, and like any alias can actually be anything you want. I will demonstrate that after taking care of the business at hand.
At this point, we know that we told the network to route our request to ‘vmlnx01’, and by using the local hosts file, it was able to translate ‘vmlnx01’ to ‘192.168.160.101’. Let’s set it so that it can’t make that translation, and see what results. We will do that by removing the entry for vmlnx01 from the hosts file:
127.0.0.1 localhost 192.168.160.101 fubar.vmdomain fubar
Listing 5.
Then test:
C:\>sqlplus scott/tiger@fred SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 10:13:45 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ERROR: ORA-12545: Connect failed because target host or object does not exist
Listing 6.
And we have our error. Please notice that we did NOT touch our tnsnames.ora file, which we’ve already proven to be good. This error is simply oracle reporting what the OS network returned. Of course, since it resulted from a mis-match between the HOST parameter in tnsnames and the entries in the hosts file, the proper fix is could be in either file. It is often suggested to avoid this problem by hard-coding the IP address in the tnsnames (HOST=192.168.160.101), but I consider that to be a hack taken by those who do not understand how net name resolution works. It is certainly poor practice to hard-code an IP address any place an alias can be used. Just think of the problems caused by hard-coded IP addresses when the network administrator restructures the net.
Conclusion
This particular error is exactly analogous to “ORA-12154: TNS:could not resolve the connect identifier specified”. With ORA-12154, sqlnet couldn’t find a way to translate “fred” to a complete connect descriptor. With ORA-12545, the OS network layer couldn’t find a way to translate ‘vmlnx01’ to an ip address.
“Film at eleven . . .”
Ed Stevens, DBA 