Help! I can’t connect to my database (part duex)

Basic Client TNS (sqlnet) configuration

Continuing the discussion of “Why can’t I connect to my database?”, 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 the vast majority of connection problems are presented by people using Windows as the client, all examples here will be from a Windows perspective. What a shame! Unix is actually much simpler …

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\product\10.2.0\client_1

your sqlnet config files will be located in C:\oracle\product\10.2.0\client_1\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 XP-Pro.  On it I have installed a standard Oracle 10.2 client, with ORACLE_HOME established at C:\oracle\product\10.2.0\client_1.  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\product\10.2.0\client_2\network\, and two directories outside of ORACLE_HOME, c:\oranet\admin_3 and c:\oranet\admin_4.

First, let’s look at my current setup.  Checking the registry (HKLM\software\oracle) we see that ORACLE_HOME is set to c:\oracle\product\10.2\client_1

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.

C:\> echo %ORACLE_HOME%
%ORACLE_HOME%

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

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-FEB-2
011 18:27:09

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

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

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

C:\>

The key thing to observe here is the lines

Used parameter files:
C:\oracle\product\10.2.0\client_1\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 in line 13, “SERVICE_NAME =  vlnxora1″.  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:

C:\>set ORACLE_HOME=C:\oracle\product\10.2.0\client_2

C:\>echo %ORACLE_HOME%
C:\oracle\product\10.2.0\client_2

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-2
011 10:40:52

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

Used parameter files:
C:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora2)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

Now we see that ORACLE_HOME in the registry (client_1) was overridden by the value in the environment – client_2.  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.

TNS_ADMIN

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\oracle

Testing again:

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-
011 10:53:31

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

Used parameter files:
c:\oranet\admin_3\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora3)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

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:

C:\>set TNS_ADMIN=C:\oranet\admin_4

C:\>echo %TNS_ADMIN%
C:\oranet\admin_4

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-2
011 10:57:12

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

Used parameter files:
C:\oranet\admin_4\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora4)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

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

Conclusion

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 most everything 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.

Next up:  ora-12154 and tns-03505

Film at eleven.

Advertisement