Connecting with Oracle: Finding and Setting a Configuration File Location

Last month’s "Connecting with Oracle" article
dealt with properly configuring the tnsnames.ora file. Using a
statistics-related analogy for a minute, you can have Type I and Type II errors
with respect to your tnsnames.ora file. You can have a "good" tnsnames.ora
file, but "reject" its use due to not knowing where it is. You can
have a "bad" tnsnames.ora file in a location you know about, and try
to "accept" its use. Both situations are bad, and fortunately, each
is easy to correct once armed with some knowledge about Oracle Net Services.

This article deals with knowing how to set a location for
your tnsnames.ora file and where to look for that location (having a "good"
file and "accepting" its use because you know where it is or how to
find it). The ubiquitous Oracle user name Scott just received a promotion: he
is a newly anointed junior DBA who must set up his company’s 100 computers to
use Oracle. Scott knows he has a good tnsnames.ora file, and he wants to use it
as a template for everyone else.

One huge misconception about using Oracle products that
occurs among other-than-RDBMS users (novice Forms developers in particular) has
to do with the proper configuration of the non-RDBMS Oracle product. This is
probably the number one problem or misconception you can easily find on
numerous web sites: "I can connect to my database when using SQL*Plus. I
just installed Forms & Reports and my forms can’t connect to the database.
What gives?"

Well, the guiding principle here is that pretty much every
Oracle product, database or otherwise, has its own set of "Oracle network"
configuration files. Each Oracle product has its own ORACLE_HOME. Within those
ORACLE_HOME locations is a directory named network (net80 on Windows for some
older products), and within the network directory, you will find a directory
named admin. This is where Oracle installs the Oracle Net configuration files,
including tnsnames.ora. Like many other things in Oracle, if Oracle puts it
there, Oracle expects to find it there, unless you happen to intervene. In this
case, intervention can be a good thing.

Where does Oracle expect to find the tnsnames.ora file? The
table below shows some of the possible locations.

Coming back to Scott and his 100 Oracle users, some of whom
use client version software, some of whom are Forms developers, some of whom
use UNIX boxes and some who use PCs, Scott has two options to get everyone on
the same sheet of music (using a properly configured tnsnames.ora file).
Probably the least efficient solution calls for Scott to go to each and every
machine and copy the tnsnames.ora file onto the machine in each ORACLE_HOME
location. That way, he knows everyone is on the same sheet of music. But what
happens when a database is added or deleted, or a host changes? Go back to each
machine and copy or edit the file? Send out a company email telling users how
to edit the file? "Scott, this is Trish. I’m doing WHAT with that
tns-whatever file?"

Fortunately, Scott has a simple solution at hand. It may
require a bit of work at first, but after the system is in place, changes are
extremely easy to implement. Scott’s solution is to use the TNS_ADMIN variable.
The TNS_ADMIN environment variable points to a directory on the file system.
The idea here is to set the variable to a shared network drive or directory,
whether it is UNIX or Windows based. The setting can be an absolute path, such
as F:sharedoracle (where F: is a mapped network drive),
/usr/local/shared/oracle (where /usr/local/shared is mounted to a remote server),
or a path that uses UNC naming (where you see the "\" in front of a
server or machine name) such as \server2shareoracle. You can find more
information about UNC naming in books about Windows 2000 administration (like
the MCSE 70-210 exam Microsoft Press book or search the web; http://compnetworking.about.com/library/glossary/bldef-unc.htm
is one such reference).

When Scott needs to add a new database (service identifier,
etc.) for employees to use, or change a host name, all he needs to do is edit
one file: the tnsnames.ora file found in the directory specified by the
TNS_ADMIN variable. The change is immediately available to everyone. UNIX users
can place the environment variable in logon or resource files (for example,
C-shell users can use "setenv TNS_ADMIN /some/location" in the .cshrc
file; other shells can have it exported, and so on). Where do Windows users
place or set the variable?

I mentioned the "R" word earlier, but only because
I wanted to be technically correct. Oracle’s documentation even mentions
placing the TNS_ADMIN variable in the "R" word location. But really,
what possible reason would you want to use the "Registry" for
something like that? There is no need to; and, in fact, you can find other
documents from Oracle that discourage the use of directly editing the registry.
Which, by the way, is good advice, and it happens to match Microsoft’s
admonition about directly editing the registry. So, the answer for Windows
users: set TNS_ADMIN as an environment variable, either at the system or user
level.

Suppose you have a user who uses SQL*Plus and Form Builder
from Forms 6i. Each product creates its own set of configuration files in their
respective ORACLE_HOME-network-admin directories (substitute / or for the hyphen,
depending on the platform). Simply "direct" both Oracle products to
use the tnsnames.ora file found in the directory specified by the TNS_ADMIN
environment variable. The solution to the "What gives?" question
posted on numerous web sites is that simple.

In closing, the other configuration file frequently used in
conjunction with the tnsnames.ora file (sqlnet.ora) can also be placed in the
same directory specified by the TNS_ADMIN environment variable. Whether you
choose to use sqlnet.ora is a separate issue; there is no general absolute
requirement to use it, but there are many cases where it is useful and
necessary.

As a DBA, you can save time for more important matters ("What
was that web site about awful plastic surgery, including some pictures?") by
striving for efficiency in areas that are easy to manage. For the most part,
Oracle networking is very easy to manage, and as pointed out in the Scott
scenario, efficiency is essential.

»


See All Articles by Columnist Steve Callan