TNS_ADMIN, How and Why

There're 4 sections we'll talk about TNS_ADMIN in this post.

1. TNS_ADMIN in Oracle Software Registry
2. Basics of TNS_ADMIN
3. How to Define TNS_ADMIN
4. What About %PATH% ?

TNS_ADMIN in Oracle Software Registry

For Windows platform which are running Oracle server or client, important information of Oracle product like ORACLE_HOME and ORACLE_BASE are not saved as environment variables.

In fact, they are saved as registries. To seek them, you can open the Registry Editor, then navigate to the following path:

Where you can modify their values including TNS_ADMIN to meet your needs. For example, we have installed Oracle 18c client (not instant client), so we can check registry like below:

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_ADMIN?

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_ADMIN Environment Variable is Not Defined?

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

%TNS_ADMIN% = %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.

Further reading: Where is ORACLE_HOME location?

Database connection tools like Toad for Oracle may throw error ORA-12170 about connection.

As I said earilier, TNS_ADMIN can be derived from ORACLE_HOME as long as they are in the same product destination, so it's all right to see the "TNS_ADMIN environment variables is not defined" if the second check item is passed.

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

How to Define TNS_ADMIN

You can set TNS_ADMIN either as:

1. A registry parameter or
2. An environment variable

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.

We created the new string value, 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.

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.

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 out-of-box package, they can be used right after unzipped. So they have no way to set registry parameters.

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.

Please open environment variable editor directly, then create a variable.

After finished, click on OK.

A new environment variable TNS_ADMIN is working now.

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.

Input the binary path of Oracle software.

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:
Configuration Parameters and the Registry