Oracle Windows tnsnames.ora location

 

Question:  I am having trouble locating my tnsnames.ora file on my Windows server.  tnsnames.ora file exist in the Linux machine under

/home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

But in my windows machine there is no tnsnames.ora under \oracle\product\10.2.0\client_1\network\admin

How can I locate my tnsnames.ora file, or create a tnsnames.ora file?

Answer:  Remember, there is only one tnsnames.ora per server, no matter how many instances reside on it! 

When finding the tnsnames.ora  location in Windows, look for the TNS_ADMIN registry entry.  If TNS_ADMIN is set then you should be able to use a network alias, not the full "(DESCRIPTION..." connect string.  The TNS_ADMIN parameter tells Oracle clients where to find the tnsnames.ora file.

Also, make sure that TNS_ADMIN is set in your DOS $PATH variable:

C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin

 

You looked for your tnsnames.ora file at /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora.

But, the db_1 is the $ORACLE_SID!

Traditionally, the tnsnames.ora is in $ORACLE_HOME/network/admin with a soft link to the ORACLE_SID location.

In windows, look for the tns_admin registry entry. If  tns_admin is set then you should be able to use a network alias, not the full "(DESCRIPTION..." connect string. The TNS_ADMIN parm tells Oracle clients where to find the tnsnames.ora file.

Also, make sure that  tns_admin is set in your DOS $PATH variable:

C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin

According to the docs, the precedence in which Oracle Net
Configuration files are resolved is:

1.  Oracle Net files in present working directory (PWD/CWD)
2.  TNS_ADMIN set for each session session or by a user-defined script
3.  TNS_ADMIN set as a global environment variable
4.  TNS_ADMIN as defined in the registry
5.  Oracle Net files in %ORACLE_HOME/network/admin
   (Oracle default location)
   

For more details, see this related thread on managing the tnsnames.ora file on Windows, where the Oracle documentation notes how to set tns_admin in the Windows registry:

"Access the Windows registry i.e. click: Start > Run, then enter 'Regedit.exe' (or 'Regedt32.exe').

Add the TNS_ADMIN environment variable to the HKEY_LOCAL_MACHINE\Software\Oracle[\Home[X]] key by right mouse clicking, then selecting: New > Key > String Value.

Note: [X] is the Home Id for the ORACLE_HOME being modified.

If you are unsure where to set TNS_ADMIN in the registry, refer to the ORACLE_HOME\bin\oracle.key file which provides the location of the registry key used by executables within that home."

It also notes a procedure for setting tns_admin globally in everyone's path:.

"TNS_ADMIN may also be defined as a system-wide or global variable
within the operating system. To do so:

1. Logon as Administrator.
2. Click Start > Settings > Control Panel
3. Double click the SYSTEM icon
4. From System Properties, select Environment tab.

The Environment tab lists System environment variables defined by
Windows NT - these are the same no matter which user is logged on
to the computer. When logged as as a member of the Administrator
group, you can modify, add or delete values.

5. Highlight OS in the list of Systems Variables

In the Variable field you should see: OS and in the Value field,
the value: Windows_NT

6. Change the Variable from OS to TNS_ADMIN.
7. Change the Value from Windows_NT to the complete path were the
configuration files will reside.

For example: c:\orant\net80\admin

8. Click Set, then apply.

In the System Variable table you should now see the TNS_ADMIN
variable with the path to the location of Oracle Net configuration
files.

9. Click OK to close window."

---