(This is a revised version of an article I originally published at edstevensdba.wordpress.com)
Some of the most frequently asked questions on the Oracle Technology Network (OTN) forums deal with problems trying to connect to the database. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) the very specific error messages and riding off in all directions at once. So let me try to explain a little about how Oracle handles a request to “connect me to my database” and actually locates a database running on a machine on the other side of the planet (or even on the very machine from which the request originated!)
Before digging in, let’s talk about a very simple concept that an amazing number of people struggle with. For purposes of the current discussion there are two “entities”, or processes, involved. First there is the server process. Depending on one’s semantic precision and the context in which the term is used, the “server” could refer to the database server process, or the computer on which that process executes. The second entity is the client process. That is the process that is requesting the connection to (and services from) the database. Again, depending on one’s semantic precision and context, the term “client” could refer to a process or a computer on which the process executes. For our purposes both “client” and “server” mean the process. These processes could be running on any two separate computers, or (and understand this) they could be running on the very same computer. The important thing is the distinction between the two processes.
So let’s take a quick walk down the path from the client to the server. We will dig deeper in future posts. For our purposes, we will use the most common Oracle client program of all: sqlplus. At a command line you issue this statement to start it and connect to your database:
C:> sqlplus scott/tiger@larry
Of course, the first thing that will happen really has nothing to do with Oracle. First, the OS must locate an executable called ‘sqlplus’, load it, and pass it the rest of the command line (scott/tiger@larry) do with as it sees fit. And what sqlplus sees fit is to pass a request to Oralce’s network layer (TNS, Transparent Network Substrate) to make a connection to “larry”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “larry”. By default it will do this by looking in a file called tnsnames.ora. Since we are still at the client making the request, this file must be found on the client machine. By default it will be found in $ORACLE_HOME/network/admin.
Let’s make it easy and suppose our tnsnames file has this entry:
larry = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = curley) ) )
TNS will look in tnsnames.ora for an entry called ‘larry’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley). Notice where it got this information from the entry in the tnsnames file. (This entry is known as the “connect identifier”.) Also notice that what is going on here is the resolution of an alias (“larry”) to an actual destination. In this respect the tnsnames.ora file serves the same purpose for sqlnet as the OS’s “hosts” file serves for the standard network stack. Or for a less technical analogy, it serves the same purpose as your telephone directory, where the the name of “larry” would be associated with the routing information (telephone number) needed by the telephone network.
All network routing is done by IP address, but all we have provided here is a host name of “myhost” Where is “myhost”) on the network? When the request gets passed from TNS to the standard network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file or a DNS server. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora but for ease of maintenance this is not recommended.
Once the ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) at the specified ip address. Hopefully, there is an Oracle database listener process at that address and configured to listen on the specified port, and that listener knows about SERVICE_NAME=curley. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to that server process will be on a different port, selected by the listener and communicated back to the client. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.
What can go wrong?
First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified”. I’ll expand on the various reasons “larry” may not have been found at a later date, but make no mistake, if you receive a ORA-12154, it is an absolute certainty your request never got past this point. You are wasting your time trying to solve this by looking at your listener. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory – aka “tnsnames.ora” – or can’t find the party you are looking for listed in it – no entry for larry) you don’t look for problems at the telephone switchboard.
Maybe the entry for “larry” was found, but “myhost” couldn’t be resolved to an IP address (neither the local hosts file nor the DNS server had an entry for “myhost”). This will result in “ORA-12545: Connect failed because target host or object does not exist”.
Maybe there was an entry for “myserver” in the local hosts file or the DNS server, but it specified an IP address that does not exist on the network or is otherwise unreachable. This will result in “ORA-12170: TNS:Connect timeout occurred”.
Maybe the IP that is reachable on the network, but there is no listener running on that machine. “ORA-12541: TNS:no listener”
Maybe the IP was good, there is a listener at that address, but it is listening on a different port. Again, “ORA-12541: TNS:no listener”
Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME=curley. “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”
Ok, that is how we get *from* the client connection request *to* the listener. What about the listener’s part of all this?
The listener is very simple. It runs on the server machine and it’s job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue.
The listener is configured with the listener.ora file, but if that file doesn’t exist, the listener is quite capable of starting up with all default values. One common mistake with the listener configuration is to specify “HOST=localhost” or “HOST=127.0.01”. This is a very special ip address, known as the “local loopback” address. LOCALHOST and ip address 127.0.0.1 always mean “this machine on which I am sitting”. So, *all* computers are known as “localhost” or “127.0.0.1”. If you specify this address in your listener configuration, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file, the request would be routed to the machine on which the requesting client resides. Probably not what you want.
From here I have a few ideas for future posts, each focusing on potential complications at each step of the process. Another post will use actual examples to deconstruct (break) a working TNS connection request, to demonstrate and prove the various factors.
“Film at eleven”.
Ed Stevens, DBA 