Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 13, 2003

Connecting with Oracle: Finding and Setting a Configuration File Location

By Steve Callan

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.

UNIX platforms

Windows platforms




No counterpart

A .tnsnames file in your home directory

(this is not documented very well and is infrequently used)

No counterpart

In the directory specified by the TNS_ADMIN environment variable

In the directory specified by the TNS_ADMIN environment variable or registry setting

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:\shared\oracle (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 \\server2\share\oracle. 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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM