Oracle Software Registry
For those Windows platform which are running Oracle, ORACLE_HOME, ORACLE_BASE and TNS_ADMIN are not set as environment variables. That is, you can find them nowhere in Environment Variables editor. In fact, they are registry parameters. You can call regedit to open a Registry Editor and then seek for the following path:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAMEWhere you can modify their values including TNS_ADMIN to suit your needs.
For example, we have installed Oracle 18c client, so we can check registry like below:
What is TNS_NAME?
TNS_ADMIN is a key parameter or environment variable that guides Oracle clients and tools to find all network configurations, such as tnsnames.ora and sqlnet.ora.
Usually, the value of TNS_ADMIN can be derived from ORACLE_HOME like this:
%ORACLE_HOME%\network\adminTherefore, it’s pretty normal if you found that TNS_ADMIN is not defined as a registry parameter, neither an environment variable. So don’t worry about the derived TNS_ADMIN if you have set ORACLE_HOME correctly.
How to Set TNS_ADMIN
You can set TNS_ADMIN either as:
1. Set TNS_ADMIN as a Registry Parameter
In some cases, we may need to create an entry for TNS_ADMIN to overwrite the derived location. For example, reusing the older version’s network files which are fully and well configured. It’s a very useful practice to collaborate multiple versions of Oracle clients together in Windows.
First of all, right click on the key folder and new a string value.
The new string value is created, but the value is empty so far.
Obviously, the value of the string has not been set yet, you have to right click on this entry to modify it.
Since we’d like to reuse the old tnsnames.ora (e.g. 12.2.0) instead of the new one (18.0.0 in this case), we set it to the old network admin path.
As you can see, its value is a little different from other key-value pairs. This is what we want.
Let’s do a test.
TNS Ping Utility for 64-bit Windows: Version 220.127.116.11.0 - Production on 24-MAR-2
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 10.10.10.10)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)
As you can see, we cooperate both releases (18.0.0 and 12.2.0) to tnsping a remote database. In which, tnpsing of release 18.0.0 used the old version’s network file to accomplish the test.
2. Set TNS_ADMIN as an Environment Variable
TNS_ADMIN environment variable is not defined if there already have registry parameters been used for preparing users’ environment.
In contrary, for those who use Oracle instant clients, there’re no registry parameters. This is because Oracle instant clients are no-installer or portable software, so it can be used right after unzipped.
Please note that, Oracle client and Oracle instant client are different, the former is an install-based and full-fledged software, the later is a portable and partial-functioned package.
In addition, tools like TOAD, SQL Developer or PL/SQL Developer won’t define TNS_ADMIN either. In such cases, we need to create a new environment variable for TNS_ADMIN to support all tools or utilities globally.
Right click on Computer -> Properties.
In Advanced tab, we click on Environment Variables.
Let’s new a user variable.
After finished, click on OK.
A new environment variable TNS_ADMIN is working now.
Let’s echo the environment variable for sure.
It points to the network admin folder for Oracle instance client 11.2.
Normally, Oracle clients will set the environment variable PATH automatically during installation. But if you plan to use instant client, you have to add Oracle binary path string to it by yourself.
Find out PATH and click on Edit.
Input the binary path of Oracle software.
Consequently, we can find out the location of utilities:
In the above test, we have learned the path of Oracle binary utilities. It’s from Oracle instant client 11.2.
More information about Oracle registry parameters, such as ORA_SID_AUTOSTART, ORA_SID_PFILE, SQLPATH, etc. Please follow the official documentation for 12c database on Windows:
Registry Parameters Overview