Usage of TNS_ADMIN environment, Unable to user TNS_ADMIN environment Options

[indranildey]

As per my knowledge goes TNS_ADMIN environment variable is for indicating directory for Net80 configuration files. But in my system it is not working. I had tried with sqlplusw.exe and plus80w.exe. I am using win 2000 prof. (client).
I created a separate TESTING folder in my C drive and kept the tnsnames.ora file in that folder. After that I created a batch file like this

@echo off
TNS_ADMIN = C:\TESTING
C:\Oracle\Dev60\BIN\ plus80w.exe

But still it is reading the tnsname.ora file from C:\Oracle\Dev60\NET80 folder

I also tried with

@echo off
TNS_ADMIN = C:\TESTING
C:\Oracle\Ora92\bin\ sqlplusw.exe

But still it is reading the tnsname.ora file from C:\Oracle\Ora92\network\admin folder

So, please guide us how to user TNS_ADMIN environment variable.

[dave]

not a windows guys

but try

set TNS_ADMIN=C:\Testing

[Laks]

Hi Indranildey
Dave is right..The syntax for env. variable setting is
set TNS_ADMIN=...
If you need to set this env. variable permanently then,
Try setting the TNS_ADMIN variable as a system environment variable and then try .
Or if you have access to registry try setting TNS_ADMIN for the desired ORACLE_HOME.(But i would advise you to try the first one as it is safer).

Also make sure that your registry doesn't have a TNS_ADMIN setting already for the ORACLE_HOME.

Hope this solves your problem..

Cheers
Laks

[donnieb]

Actually, you want your TNS_ADMIN to point to:

ORACLE_HOME\network\admin

Not just to ORACLE_HOME.

[indranildey]

Thanks for your advice. I had made the changes in batch file but still it is reading the tnsname.ora file from C:\Oracle\Dev60\NET80 folder.

@echo off
SET TNS_ADMIN = C:\TESTING
C:\Oracle\Dev60\BIN\ plus80w.exe

Is there any other way to do the same.

[Laks]

Did you check if there is anything defined in the registry for TNS_ADMIN for the respective Oracle Homes?
If TNS_ADMIN is defined in registry then it would override i guess?

[indranildey]

No there is nothing like TNS_ADMIN in registry.

[Laks]

Please refer 114085.1 metalink note which would help you in your problem. Follow it systematically and hope it addresses your problem

[Laks]

Here is another note from Metalink(111942.1) which would solve your problem in TNS_ADMIN resolution :

TNS_ADMIN Resolution Order:

On Windows NT/2000, the precedence in which Oracle Net
Configuration files are resolved is:

1. Oracle Net files in present working directory (PWD/CWD)
2. TNS_ADMIN defined sessionally or by user-defined script
3. TNS_ADMIN defined as a global environment variable
4. TNS_ADMIN defined in the registry
5. Oracle Net files in %ORACLE_HOME/network|net80\admin
(Oracle default location)


Setting the TNS_ADMIN environment variable

From Oracle9i (9.x) onwards, Oracle Net Services is the current name for the
Oracle Networking product, formerly known as; SQL*Net in Oracle7 (7.x), Net8
in Oracle8 (8.0.x) and Net8i in Oracle8 (8.1.x). For consistency, the term
Oracle Net is used thoughout and refers to all Oracle Net product versions.

The TNS_ADMIN parameter/environment variable is used to explicity define the
directory location for Oracle Net to resolve where to find its configuration
files. TNS_ADMIN over-rides the default Oracle Net location.

For example, if TNS_ADMIN is set to ORACLE_HOME\test\admin, Oracle Net looks
for its configuration files in ORACLE_HOME\test\admin to make a database
connection or start other Oracle Networking products such as; the Listener,
Connection Manager, Names Server, etc.

TNS_ADMIN can be defined in the following ways:

=====================================================================
Platform Description
---------------------------------------------------------------------

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.

Setting TNS_ADMIN in HKEY_LOCAL_MACHINE\Software\Oracle[\Home[X]]
key over-rides the default Oracle Net location for products only
installed into that ORACLE_HOME.

By default, each time you install different Oracle versions, the
default location to which Oracle Net files are generated is the
ORACLE_HOME\network|net80\admin directory. Over time, these two (or
more) sets of configuration files may start to contain differing
information and entries.

In this case, you may wish to set TNS_ADMIN in the environment rather
than as a Home-specific registry entry (see below). This consolidates,
centralises and aids administration of Oracle Net configuration
files. However, be aware of syntactical differences and parameter
behavioural changes between different SQL*Net/Net8/Net8i/Oracle Net
versions.

Be aware that some Oracle product combinations may require, or be
better suited to, maintaining Home-specific versions of Oracle Net
configuration files. Examples include; Oracle Applications, Oracle
9iAS, Oracle Enterprise Manager (OEM), etc. If using combinations
of these products, exercise care so not to accidentally modify
the other ORACLE_HOMEs TNS_ADMIN location e.g. making a global change
that adversely affects Oracle Net connectivity of other Oracle
versions.

Environment:

TNS_ADMIN may also be defined as a system-wide or global variable
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
files.

9. Click OK to close window.

This setting will force all installed Oracle applications run or
launched from this server to use the same set of configuration files.

Note: Again, care should be taken when setting global variables,
particularly for multiple ORACLE_HOME environments. This action
will modify other ORACLE_HOME's TNS_ADMIN locations and may
adversely affect their ability to connect.

Session:

TNS_ADMIN may also be defined for a given Command Prompt session.
For example:

C:\> set tns_admin=c:\temp

In this case, the value of TNS_ADMIN is only valid for the life of
the Command Prompt session. Once terminated, the value is lost.




Hope it helps..
Laks

[balkrishan_mitta...]

HI MR. ID,

for that you need to set the environment variable.
to be able to set it from command line you may need to install setx utility provided by windows.

download file from following link

http://www.microsoft.com/windows2000/techi...ting/setx-o.asp

install it.

then write at command prompt

SETX TNS_ADMIN C:\ID (WHAT EVER IS YOUR DIR)

AND THEN SEE IT WILL WORK.

THANKS

HOPE THIS HELPS