Question: I am having trouble locating my
tnsnames.ora file on my Windows server. tnsnames.ora file exist in
the Linux machine under
But in my windows machine there is no tnsnames.ora under
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!
You looked for your tnsnames.ora file at
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
C:> set TNS_ADMIN=$PATH;ORACLE_HOME\network\admin
According to the docs, the precedence in which Oracle Net
Configuration files are resolved is:
- Oracle Net files in present working directory (PWD/CWD)
- TNS_ADMIN set for each session session or by a user-defined script
- TNS_ADMIN set as a global environment variable
- TNS_ADMIN as defined in the registry
- 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
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
"TNS_ADMIN may also be defined as a system-wide or
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
9. Click OK to close window."