Benthic Software Blog Post Jan 18, 2014

How the Oracle Client finds TNSNames.ora

Summary:

Most Oracle Client installs use a TNSNames.ora file. This allows users to enter a database alias in client programs to connect to an Oracle database. A common problem is when the Oracle Client either can't find the TNSNames.ora file or is using a different one than was intended.

Default location:

The default location of the TNSNames.ora file is "ORACLE_HOME\network\admin\tnsnames.ora", where ORACLE_HOME is the directory which contains your primary Oracle Home installation. This could be something like "C:\Oracle12\network\admin\tnsnames.ora".

Forcing the Oracle Client to use a particular TNSNames.ora file:

If you use TNSNames.ora it is recommended to use either an environment variable or a registry setting to force the location of the file. This setting is called TNS_ADMIN and is set to the directory/path of the tnsnames.ora file (but should not contain the filename, just the path to it.)

TNS_ADMIN can be set in a number of different ways, but Oracle will use the following precedence:

  1. Local session environment variable.
  2. Global environment variable.
  3. User registry setting under HKey_Current_User\Software\Oracle (or individual Oracle home registry entry.)
  4. System registry setting under HKey_Local_Machine\Software\Oracle (or individual Oracle Home registry entry.)

Possible Problems:

One possible problem (and this comes up surprisingly often!) is that the Oracle Client will first check if the current application directory contains tnsnames.ora. The current application directory can be initially set in the startup icon options for a program or programs often keep track of the last used document directory. It is a good idea to not keep copies of tnsnames.ora in default program directories or document directories so as not to have the Oracle Client accidentally use it instead of the intended tnsnames.ora file.