Home > Oracle, RDBMS > Resolving Oracle networking problems – basic client TNS (sqlnet) configuration.

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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: