Oracle ClientWindows Platform

TNS_ADMIN, How and Why

TNS_ADMIN in Oracle Software Registry

For those Windows platform which are running Oracle server or client(s), you should know some facts of Oracle important parameters, 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 them in the following path:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_HOME_NAME
Where you can modify their values including TNS_ADMIN to suit your needs.

For example, we have installed Oracle 18c client (not instant client), so we can check registry like below:

Oracle Software Registry - ORACLE_HOME
Oracle Software Registry – ORACLE_HOME

Be careful, Oracle client is different from Oracle instant client in many ways, you should have known that.

Basics of TNS_ADMIN

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.

If you didn’t see TNS_ADMIN in your registry or environment variable editor, it might be normal as I explained below.

Why TNS_NAME is Not Defined?

Usually, the value of TNS_ADMIN can be derived from ORACLE_HOME like this:

%ORACLE_HOME%\network\admin
Therefore, 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. That’s why TNS_ADMIN environment variable is not defined.

At times, you may want to explicitly set TNS_ADMIN instead of the derived one for some reasons. You can do it by the following ways.

How to Set TNS_ADMIN

You can set TNS_ADMIN either as:

  1. A registry parameter or
  2. An environment variable

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 one Windows.

First of all, right click on the key folder and new a string value.

Oracle Software Registry - New a String Value
Oracle Software Registry – New a String Value

We created the new string value, but the value is empty so far.

Oracle Software Registry - TNS_ADMIN Created
Oracle Software Registry – TNS_ADMIN Created

Obviously, the value of the string has not been set yet, you have to right click on this entry to modify it.

Oracle Software Registry - Modify TNS_ADMIN
Oracle Software Registry – Modify TNS_ADMIN

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.

Oracle Software Registry - Input a New Value for TNS_ADMIN
Oracle Software Registry – Input a New Value for TNS_ADMIN

As you can see, its value is a little different from other key-value pairs. This is what we want.

Oracle Software Registry - TNS_ADMIN Modified
Oracle Software Registry – TNS_ADMIN Modified

Let’s do a test.

C:\Users\ed>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 24-MAR-2
018 18:55:52

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:
C:\app\client\ed\product\12.2.0\client_1\network\admin\sqlnet.ora


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 is not defined as environment variable if there already have registry parameter been used for user’s environment.

In contrast, 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.

Windows 7 - Advanced system settings
Windows 7 – Advanced system settings

In Advanced tab, we click on Environment Variables.

Windows 7 - Environment Variables
Windows 7 – Environment Variables

Let’s new a user variable.

Windows 7 - Environment Variables - New
Windows 7 – Environment Variables – New

After finished, click on OK.

Windows 7 - Add a New Environment Variable TNS_ADMIN
Windows 7 – Add a New Environment Variable TNS_ADMIN

A new environment variable TNS_ADMIN is working now.

Windows 7 - TNS_ADMIN Environment Variable Entry
Windows 7 – TNS_ADMIN Environment Variable Entry

Let’s echo the environment variable for sure.

C:\Users\ed>echo %TNS_ADMIN%
D:\Oracle\instant_112\network\admin

It points to the network admin folder for Oracle instance client 11.2.

What About %PATH% ?

Normally, Oracle clients will add their binary path strings to the environment variable PATH automatically during installation. But if you’re using instant client, you have to add Oracle binary path string to PATH by yourself.

Find out PATH and click on Edit.

Set Environment Variable - Edit PATH
Set Environment Variable – Edit PATH

Input the binary path of Oracle software.

Set Environment Variable - Input Value for PATH
Set Environment Variable – Input Value for PATH
Set Environment Variable - PATH Result
Set Environment Variable – PATH Result

Consequently, we can find out the location of utilities:

C:\Users\ed>where sqlplus
D:\Oracle\instant_112\sqlplus.exe

C:\Users\ed>sqlplus scott/tiger@orcl
...

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

Leave a Reply

Your email address will not be published. Required fields are marked *