Locating Oracle’s Network Configuration Files

(This is a revised version of an article I originally published at edstevensdba.wordpress.com.  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.

Please like us:

2 Comments


  1. // Reply

    Your article is not bad, however you should mention a few topics.
    Nowadays we have to deal with 32-bit and 64-bit Environment. Oracle Client can be 32 bit or 64 bit. Thus the Registry hive for a 32-bit Oracle Client at 64-bit Windows is “HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN”

    Then you mention, you get all your findings with SQL*Plus. Some time ago I also started such investigations, but I gave up. The way how tnsnames.ora file is located vary and can be different for any application or driver. For example, ODP.NET Managed Driver (Oracle Data Provider for .NET) does not use the Registry entries at all. See also this post: http://stackoverflow.com/questions/28280883/determining-locatation-of-relevant-tnsnames-ora-file/28283924#28283924

    In order to make any application working I recommend following:

    1) Define location of tnsnames.ora by Environment variable “TNS_ADMIN”
    2) Define location of tnsnames.ora by Registry key “HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN” and/or “HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN”
    3) In case your location is different to “%ORACLE_HOME%network\admin” then create a symbolic link at this location pointing to your tnsnames.ora file


    1. // Reply

      “Your article is not bad, however”

      Sounds like “damnation by faint praise” 😉

      The intent of the article was to illustrate the fundamental concepts and provide an introduction to some follow-on articles. I would hope the reader would be able to take those fundamentals, read the follow-on articles, and extrapolate to their specific environment.

      Hardly a week goes by that I don’t have to deal with 32-bit/64-bit issues on Windows clients, but those questions don’t change the fundamentals. Trying to specifically address all the possible permutations of 32-bit vs. 64-bit, plus registry vs. system environment variables, plus tns_admin, plus use of symbolic links, plus use of ifile directives would quickly mushroom into a small book in and of itself.

      I also did not address the fact that jdbc-thin clients don’t even use the tns client software and thus do not use tnsnames at all. Nor did I address the use of ldap instead of tnsnames, nor the use of hard-coded connection string specification.

      It appears from the SO article you reference that you yourself said

      “Obviously there is no fix search, it varies for different providers/drivers. Maybe it also depends on the Oracle version.”

      I’m quite certain that at some point it can vary by Oracle version, as I have noticed over the years (I started with Oracle 7.3 on Windows 3.11) that the reference to TNS_ADMIN in the registry seemed to move from HLKM\sofware\oracle to HKLM\software\oracle\KEY_OraClientWhatever, though that seems to have stabilized since 9i so shouldn’t be an issue for anyone who is staying reasonably current. Who knows, perhaps it can also be in HKLM\software\oracle\\OLEDB or in HKLM\software\oracle\ODP.NET, again with different scope of influence. Throw in the same possibilities under HKLM\SOFTWARE\Wow6432Node and the possible permutations become almost endless. But the overview I gave seems to suffice for virtually every case I’ve ever encountered.

      I used sqlplus as my demonstration target for several reasons, but the biggest reason is that, again, my intent was to provide an overview of the fundamental process, not to plumb every possibility.

      I did devote an entire section to the TNS_ADMIN variable, but again chose not to explore every possible permutation. For a multi-home environment, such as a mixed 32-bit/64-bit Windows installation, I agree with your recommendation of TNS_ADMIN to point to a central tnsnames.ora. This was pointed out in the final paragraph under “Conclusion”, just before “Further reading”. And in that I pointed out that when using TNS_ADMIN, one does not even need to put tnsnames in any ORACLE_HOME at all; that, indeed, it might be preferable to deliberately put it in a ‘neutral’ directory. And that being the case, there is no need to create any symbolic links, which would be just another unnecessary layer of complexity – one more “moving part” to have to follow-up on.

Leave a Reply

Your email address will not be published. Required fields are marked *