Archive

Archive for September, 2012

Resolving Oracle networking problems – ORA-12545.

The original version of the below article was created by Ed Stevens and could be find here.

Continuing our discussion of resolving Oracle networking problems, 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 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:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

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.

C:\>type C:\WINDOWS\system32\drivers\etc\hosts
127.0.0.1 localhost
10.10.111.10 orclsvr

Then make some tests:

A slightly different error, sqlnet simply reporting what was returned by the OS network layer – compare to the result of the ‘ping’ command.

Case #3
One more test. We will restore the hosts to its pristine condition, then shutdown the db server and test.

C:\>type C:\WINDOWS\system32\drivers\etc\hosts
127.0.0.1 localhost
192.168.111.10 orclsvr

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.

Resolving Oracle networking problems – TNSPING: what it is, what it isn’t.

The original version of the below article was created by Ed Stevens and could be find here.

Before continuing our exploration of various sqlnet connection errors, let’s take a quick look at the oracle utility ‘tnsping’. We’ll see what it does, what it doesn’t do, and bust a few myths along the way.
The tnsping utility is used to determine if a service on an Oracle Net network can be reached. A complete description of its use is found in the Net Services Administrators Guide, located with the rest of the Oracle documentation at tahiti.oracle.com. TNSPING serves, for sqlnet, much the same purpose as does ‘ping’ for the OS.

Let’s take a look at a simple case. Given this entry in my tnsnames.ora:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

let’s run some tests.

C:\>tnsping myorcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 13-SEP-2
012 11:32:22

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = orclsvr)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)

C:\>

And we think, “Aha! I can connect!” – well, maybe. Maybe not. I’ll get to that later. First, let’s look at what all we can learn from what we see here.
The first thing we learn, from line 3, is that we are running Oracle client 11.2.0.2. Note that this is the version of the client, not the database. They do not have to be the same, and this tells us only about the client.
From lines 8 and 9, we see that the network parameter file (sqlnet.ora) used by this client is at C:\oracle\11.2.0\CLIENT\network\admin. This is also a very strong indication that the tnsnames.ora file is also located in that directory. However, there are other influences on the location of tnsnames.ora – see my previous post on that subject, here.
From line 11, we see that we are using the TNSNAMES adapter to resolve the alias (the alias was ‘myorcl’). This adapter was chosen based on the value of the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.
Lines 12 and 13 show us how sqlnet resolved the alias, or “connect identifier”, into a connect descriptor. We see that the request was routed using the tcp network protocol (PROTOCOL = TCP), to server orclsvr (HOST = orclsvr), and placed at that server on port 1521. This information was gathered from the tnsnames.ora entry for ‘myorcl’, shown above.
And from line 14 we see that the response time was 10 milliseconds. Well, not quite – according to the Net Administrators Guide, tnsping “displays an estimate of the round trip time”.

We also see that it requested a connection to (SERVICE_NAME = orcl). Or did it? What does the listener show?

Hmm – “The listener supports no services”. How can this be? Wasn’t our tnsping successful? What does a real connection request do?

Of course the listener “does not currently know of service requested”. At the moment, the listener does not know of any services at all. By the way, I made sure the listener did not know of any services. My listener relies on dynamic database registration, and I had stopped the database before starting this test. Not only does the listener not know of any services, there is no database running at all. I could just as easily specified SERVICE_NAME=fubar, or SERVICE_NAME=btzflx and received the same result.

And if the listener is not running at all? Let’s stop the listener:

then test tnsping again:

As we would expect, tnsping reports that there is no listener.

While tnsping is a very useful tool for diagnosing a variety of connection problems, we must be very clear about one important point: it tells us absolutely nothing about the state of a database instance. This is not a shortcoming of tnsping. We just need to understand that it is a tool for diagnosing sqlnet issues, and sqlnet is not the database.

Resolving Oracle networking problems – ORA-12154 and TNS-03505 errors.

2012-09-13 1 comment

The original version of the below article was created by Ed Stevens and could be find here.

Continuing the discussion of resolving Oracle networking problems, 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.

There are actually several mechanisms for connecting to an Oracle database, but by far the most common is via the Oracle Call Interface (OCI), passing the connect request via Oracle’s Transparent Network Substrate (TNS), commonly referred to as “sqlnet”.

When I got this error for the first time (and a couple times more), I’ve started to check 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@orclsvr ~]$ 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?

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. The only reason this file exists on the database server is because the server can also run client processes.

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. Let’s first look at a good configuration, then we’ll start taking it apart. My database is running on an Windows 2008R2 server, while my client (Oracle 32bit) is running on my laptop, under Windows 7 Pro 64bit. My tnsnames.ora looks like this:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Make a connection to the database, just to show that everything is in order.

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 valid one)…

# sqlnet.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(LDAP)

… and observe the behavior:

In this example, we told sqlnet to use LDAP naming services to resolve the connect identifier, but LDAP is not available in my test environment, thus it was not able to resolve “myorcl”. If you want to use local naming, you must specify such in the NAMES.DIRECTORY_PATH parameter in the client’s sqlnet.ora file. Let’s fix the problem, then move on:

# sqlnet.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)

Can’t locate tnsames.ora
The entire subject of locating tnsnames.ora was covered here. 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\11.2.0\CLIENT\NETWORK\ADMIN

C:\oracle\11.2.0\CLIENT\NETWORK\ADMIN>ren tnsnames.ora tnsnames.sav

C:\oracle\11.2.0\CLIENT\NETWORK\ADMIN>dir tnsnames.ora
	 Volume in drive C is SYSTEM.
	 Volume Serial Number is 08AF-B8C9

	 Directory of C:\oracle\11.2.0\CLIENT\NETWORK\ADMIN

         File Not Found

C:\oracle\11.2.0\CLIENT\NETWORK\ADMIN>

and observe the result:

Of course, we fix it by providing a (valid) tnsnames.ora file:

C:\oracle\11.2.0\CLIENT\NETWORK\ADMIN>ren tnsnames.sav tnsnames.ora

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 the 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 ‘myorcl’. If we provide a different connect identifier it will, of course fail – again with ORA-12154:

But there is another factor that comes into play at this point. There is an optional parameter that can be set in sqlnet.ora that we have not yet discussed. Before looking in tnsnames.ora, sqlnet will check 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:

# sqlnet.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DEFAULT_DOMAIN=world.com

and observe the result:

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:

# tnsnames.ora Network Configuration File: c:\oracle\11.2.0\CLIENT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
myorcl.world.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

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:

and using tnsping:

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@myorcl

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@myorcl

In this case, sqlnet will take the ‘P’ as the password, and take ‘ssW0rd@myorcl’ as the connect descriptor. By now, you can guess the result.

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 side 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.

Resolving Oracle networking problems – basic client TNS (sqlnet) configuration.

The original version of the below article was created by Ed Stevens and could be find here.

Continuing the discussion of resolving Oracle networking problems, I want to dig deeper into each of the potential errors in order of their possible occurrence. That will be a rather lengthy journey, so I want to start with the configuration of TNS on the client side of the connection. Since I work mostly with Windows OS, all examples here will be from a Windows perspective.

Before plunging into the details of connecting to a database, we need to know the key configuration files used by TNS on the client, and how to locate them – or more accurately, how TNS locates them. There are two of these files, named “sqlnet.ora” and “tnsnames.ora”. Technically, sqlnet.ora is not required, because every essential parameter it can contain has a default value. So sqlnet.ora may not even exist on your system, but I consider it best practice to create it with key information – even if it is default values – just to remove any ambiguity when troubleshooting a problem. Full documentation of every parameter in sqlnet.ora can be found in the Net Services Reference, found with the rest of the Oracle documentation set at tahiti.oracle.com. One would also be well advised to spend some time in the Net Services Administrator’s Guide, paying attention to how the sqlnet architecture relates to the standard network OSI stack.

Of course, the TNS software needs to know how to locate these files, and has two methods of doing so. The first is to look in the default location, which is $ORACLE_HOME/network/admin. So if you have

ORACLE_HOME=c:\oracle\11.2.0\CLIENT

your sqlnet config files will be located in c:\oracle\11.2.0\CLIENT\network\admin. If you have performed multiple installations of Oracle, you have probably created multiple ORACLE_HOME directories and so would have multiple copies of the sqlnet config files – one for each ORACLE_HOME.

This default location of ORACLE_HOME\network\admin can be overridden by use of the environment variable TNS_ADMIN. For example, if you have

TNS_ADMIN=c:\oranet

TNS will try to find the configuration files in c:\oranet. Only if they are not found there will it look in the default location.

So how does TNS even determine the value of ORACLE_HOME and/or TNS_ADMIN? In a proper operating system (any *nix system) it would simply check the environment variables assigned to that process. But since we’re dealing here with Windows, we also have to check the registry. So let’s look at some examples to see the effect of various settings. We can check the result by using the Oracle utility ‘tnsping’. I won’t go into the details of tnsping here. Suffice it to say that one of the things it does (almost as an aside) is report where it found sqlnet.ora. It also shows the address information it got from tnsnames.ora. We can use those two bits of information to see exactly which copy of a file it is using.

Here’s the setup. I have a laptop running Windows 7 Pro 64-bit. On it I have installed a standard Oracle 11.2 client, with ORACLE_HOME established at c:\oracle\11.2.0\CLIENT. This is my working client setup that I use every day at the office. For this demo, I’m going to simulate a second ORACLE_HOME by creating c:\oracle\11.2.0\CLIENT2, and two directories outside of ORACLE_HOME, c:\oranet\admin3 and c:\oranet\admin4.
First, let’s look at my current setup. Checking the registry (HKLM\SOFTWARE\ORACLE – on 32-bit, HKLM\SOFTWARE\Wow6432Node\ORACLE – on 64-bit) we see that ORACLE_HOME is set to c:\oracle\11.2.0\CLIENT:

And checking the environment at the command line, we see there is NOT a setting for ORACLE_HOME, leaving the registry as the only influencing factor:

Now, let’s see what tnsping says about where it finds the config files:

The key thing to observe here are the lines:

Used parameter files:
C:\oracle\11.2.0\CLIENT\network\admin\sqlnet.ora

This tells us which sqlnet.ora file was found and used. We see that in this case, TNS was looking for its configuration files exactly where we expected – %ORACLE_HOME%\network\admin. Also notice “SERVICE_NAME = orcl”. In my setup, that service name is unique to the tnsnames file at that location. So we can know that both the sqlnet.ora and tnsnames.ora files were located at the same place.

Now let’s try some overrides. We’ll set ORACLE_HOME as an environment variable at the command prompt and try it:

Now we see that ORACLE_HOME in the registry (CLIENT) was overridden by the value in the environment – CLIENT2. Don’t worry about the TNS-12557 error. That is a result of the fact that ORACLE_HOME now points to a directory that has no binaries – remember I just set it up as a test for locating sqlnet.ora and tnsnames.ora. It is not a fully functional ORACLE_HOME.

Next let’s introduce the TNS_ADMIN variable. Just like ORACLE_HOME, it can be set in the registry, or in the command processor environment. For our next test, we’ll use the registry. Here I’ve added TNS_ADMIN to HKLM\SOFTWARE\Wow6432Node\ORACLE:

Testing again:

We see that the setting of TNS_ADMIN in the registry overrides ORACLE_HOME in both the registry and the command processor environment.

As one last test, we will add TNS_ADMIN to the command processor environment. This is on top of all of our previous settings:

And we see that once again the environment setting overrides the registry, and TNS_ADMIN overrides ORACLE_HOME.

The above demonstrations reveal the various settings that control how TNS locates its configuration file (sqlnet.ora) and name resolution file (tnsnames.ora) on a Windows client. It should also be noted that the same rules apply in a *nix environment, except that there is no registry – in a Unix environment, all of the settings are controlled by the environment variables. So Unix will have far fewer variables to consider when trying to troubleshoot a connection problem.

I started to do some further testing to see how TNS would handle a situation where one of the files was not in the preferred location. Needless to say, with two files to be located, two variables to indicate the location (ORACLE_HOME and TNS_ADMIN) and two places those variables could be defined (registry and session environment), the possible combinations explode geometrically. Life is too short.

This still leaves the question of whether or not to introduce the TNS_ADMIN setting. The answer, like almost everytime is… it depends. If you have a simple client installation with a single ORACLE_HOME, there is no reason to set TNS_ADMIN. However, if you have multiple client products, each with its own ORACLE_HOME (say, you install the base client, then install SQL Devloper) you are faced with maintaining two separate tnsnames files. There are various ways to approach this problem, but the simplest is to simply pick one, and set TNS_ADMIN to point to it. As I demonstrated above, if using TNS_ADMIN, the files don’t even have to be in any ORACLE_HOME directory. I’d even argue that this is preferable, as it makes it very clear to anyone examining the system that there is no implied linkage between your TNS config files and any particular ORACLE_HOME.

Resolving Oracle networking problems – general introduction.

The original version of the below article was created by Ed Stevens and could be find here.

Some of the most frequently problems I have to deal in my work are resolving Oracle networking problems. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) specific error messages and riding off in all directions at once. A computer will always do exactly what it is told. The problem comes in that we often don’t really know everything we’re telling it. 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, the database instance, the database server process, or the physical computer on which any of these execute. In terms of network routing it all comes back to a specific box with a specific IP address. The second process 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 physical computer, but for our purposes it means the process. And this process could be running on any computer, including (understand this) the same computer that is acting as the server. In this case, it is still a client and the fact that it is running on the server computer is totally coincidental and irrelevant.

So let’s say you are using sqlplus. You issue this statement to start it and connect to your database:

C:\> sqlplus scott/tiger@myorcl

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@myorcl) do with as it sees fit. And what sqlplus sees fit is to ask TNS to make a connection to “myorcl”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “myorcl”. 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 (for example: c:\oracle\11.2.0\CLIENT\network\admin).

Let’s make it easy and suppose our tnsnames file has this entry:

myorcl =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
		)
		(CONNECT_DATA =
			(SERVICE_NAME = orcl)
		)
	)

TNS will look in your tnsnames.ora for an entry called ‘myorcl’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = orclsvr) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = orcl). 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 “myorcl” to an actual destination “orcl”. 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.

Where is (HOST = orclsvr) on the network? When the request gets passed from TNS to the standard network stack, the name ‘orclsvr’ will get resolved to an IP address, either via a local “hosts” file, via DNS, or possibly other less used mechanisms. You can also hard-code the IP address (HOST = 192.168.111.10) in the tnsnames.ora.

Once the Ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) on the designated host/IP address. Hopefully, there is an Oracle database listener on “orclsvr” configured to listen on the specified port, and that listener knows about SERVICE_NAME = orcl. 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. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

Before running sqlplus (or the other application which connects to the database), you could test communication between the client and the listener. We will use tnsping to complete this step. It’s a common misconception that tnsping tests connectivity to the instance or database. In actual fact, it only tests connectivity to the listener. Here, we will use it to prove that:
a) the tnsnames.ora has the correct hostname and port
b) that there is a listener listening on the specified host and port.

You can issue tnsping like this:

C:\> tnsping myorcl

If it is successful you will see something like this:

If not, here are some common errors, and some suggestions for fixing them:
First, there may not be an entry for ‘myorcl’ 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 ‘myorcl’ 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 orcl) you don’t look for problems at the telephone switchboard.

Maybe the entry for ‘myorcl’ was found, but ‘orclsvr’ couldn’t be resolved to an IP address (say there was no entry for ‘orclsvr’ in the local hosts file). This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe there was an entry for “orclsvr” in the local hosts file, but it specified a bad IP address. This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe the IP was good, but there is no listener running: “ORA-12541: TNS:no listener“.

Maybe the IP was good, there is a listener at orclsvr, but it is listening on a different port => “ORA-12560: TNS:protocol adapter error“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, but doesn’t know about SERVICE_NAME = orcl => “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, knows about SERVICE_NAME = orcl, but you have other application running on the same port as the listener. In that case you don’t get any errors – tnsping just hangs for a long time…

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 (not the client) 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 NONROUTABLE ip 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.