(This is a revised version of an earlier article. At that time I was using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.)
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 in this article I will focus on locating the configuration files 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 discussing the actual configuration files involved, I’d like to take a short side road for background. In my previous article (“Help, I Can’t Connect to My Database”) I tried to drive home the distinction between client and server processes. Here, I want you to clearly understand that the networking component of Oracle is separate and distinct from both the database itself and the client. The Oracle Transparent Network Substrate (TNS) is a group of executables that occupy the session layer of the standard OSI network model. As such, all network communication between a client and an Oracle database ultimately rely on the standard network stack.
In this article I want to discuss 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 Database Net Services Reference. While there are no significant version dependencies, the 11g version is here, and the 12c version is here. One would also be well advised to spend some time in the Net Services Administrator’s Guide (11g) or Net Services Adminstrator’s Guide (12c), 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:\app\oracle\product\11.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 are lines 8 and 9
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”. This information comes from the tnsnames.ora file, and 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 in lines 13-14 that sqlnet.ora was found at the ORACLE_HOME we just specified in the environment variable, overriding the specification from the registry.
We also see a new value for SERVICE_NAME at line 18, indicating the location of tnsnames.ora is also dependent on the new ORACLE_HOME.
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.
At this point you may be asking if you should use the TNS_ADMIN setting or not. 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 require both 32-bit and 64-bit clients) you are faced with maintaining two separate sets of files. There are various ways to approach this problem, but the simplest is to simply pick one location, 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.
Further reading
I would urge the reader to spend some time with Google to read up on “OSI Network Model”, keeping in mind that the TNS modules occupy the session layer of the model. One should also spend some time getting familiar with Part One of the Database Net Services Administrator’s Guide. The 11.2 version of that document is here, and the 12.1 version is here.
Charles Hooper has a nice blog article that relates. See Finding a New Home for a Client on Windows.
Postscript:
In reviewing and editing this article for re-publication, I realized that all of the demonstrations were dealing with starting sqlplus from the command line at an already started cmd.exe. I never addressed starting sqlplus or any other client from the context of the Windows “start” menu. Doing so would simply add several more multiplying factors of complexity which, even in review, I see no point in trying to unravel. This just comes back to one of my basic philosophies: “Live by the GUI, die by the GUI”. Perhaps some day when I’m bored and out of ideas I’ll explore this.
Next up: ora-12154 and tns-03505
Film at eleven.
Ed Stevens, DBA 