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 delved into here.

If sqlnet is able to determine the connect descriptor, the next step of the process is to pass that information to the OS’s network layer to be routed to the specified IP address. It is problems at this point that we will focus on in this post.

For this demonstration, I am using an Oracle 32bit 11.2.0.2 client on Windows 7 Pro, connecting to an Oracle 11.2.0.2 database on Windows 2008R2. Name resolution is through tnsames.ora, which looks like this:

The key information we are focusing on is the “HOST = orclsvr” on line 7. First, let’s make a good connection to prove that everything is working correctly, then we will break it:

Case #1
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 “orclsvr”. For your telephone you would have some sort of directory to tell you what Moe’s number is. In a corporate environment, you probably have a DNS server to tell the network stack that “orclsvr” is 192.168.111.10. 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 7 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.111.10 orclsvr

For those not familiar with this file, the format is

ipaddress alias1 alias2 .... aliasN

Note: it is a good habit to enter an empty line at the end of this file.

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 ‘orclsvr’, and by using the local hosts file, it was able to translate ‘orclsvr’ to ‘192.168.111.10’. Let’s fix it so that it can’t make that translation, and see what results. We will do that by removing the entry for ‘orclsvr from the hosts file:

127.0.0.1 localhost
192.168.111.10 fubar

Then test:
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 mismatch 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.111.10), 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.
This particular error is exactly analogous to “ORA-12154: TNS:could not resolve the connect identifier specified”. With that error, sqlnet couldn’t find a tnsnames entry for “myorcl” to translate to a connect descriptor; with this error, the OS network layer couldn’t find a hosts entry for ‘orclsvr’ to translate to an IP address.

Case #2
For our next trial, let’s restore the proper alias to the hosts file, but equate it to a bogus IP address.

Then tests:
Of course, having a correct IP address in hosts, but the target server down, is really no different that an incorrect IP address to start with.

Case #4
I would like to do one more test to serve as the lead-in to the next few posts, dealing with problems on the db server rather than the client. For this test, the db server is up, but the listener has been shut down.

and repeating the tests:

Along with the previous posts in this series, this covers the TNS and network configurations that can be controlled completely on the client side. We have touched on a couple of server-side issues. Future posts will dig into that side of the equation.