Where's razor in Ora

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.

So, 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 like this:

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 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. "