Determining location of relevant tnsnames.ora file

Just based on your paths you have two installed clients as you suspect (Toad and dbforge are tools, not clients so your terminology is a bit off). One 32-bit, the other 64-bit. It appears that Toad is 32-bit based on its installation path, but execute it and go to Help|Support Bundle. You'll see the top header will be "APPLICATION INFORMATION (32-bit)" or "APPLICATION INFORMATION (64-bit)" just to confirm. Toad 11.6 was the first to introduce a 64-bit version.

Toad will only see the Oracle client that is for the same platform as it. So your 64-bit client is irrelevant for Toad's sake. The C:\Windows\TNS appears to be a folder used for TNS_ADMIN folder given its odd location and the fact that Toad sees it. At command prompt execute SET TNS_ADMIN and see if it reports "TNS_ADMIN=C:\Windows\TNS" If it does, then all Tools should be using that tnsnames.ora. That's a global override if you will that points to the folder containing your net configuration files. If you don't have TNS_ADMIN set as an environment variable then look for it in your Oracle root registry: HKEY_LOCAL_MACHINE\Software\Oracle.

If you use a common set of connections for all of your tools I'd delete all of your tnsnames.ora files. I'd also relocate that C:\Windows\TNS folder to somewhere more appropriate like C:\Oracle\Admin and create your tnsnames.ora, sqlnet.ora, and ldap.ora (if applicable) there. Create a TNS_ADMIN environment variable pointing to that location.


In case you are using Visual Studio v2017 here is tnsNames File:

32 bits installation:

c:\program files (x86)\oracle developer tools for vs2017\network\admin\tnsnames.ora

64 bits installation:

c:\program files\oracle developer tools for vs2017\network\admin\tnsnames.ora


According Oracle these locations are searched for tnsnames.ora, resp. sqlnet.ora and ldap.ora:

  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)

However, I am not sure whether each application/driver follows this list. I got this list from Oracle Document 111942.1 referring to Oracle 9i, so it might be outdated.

In Database Net Services Administrator's Guide the order is

  1. TNS_ADMIN defined by environment variable
  2. TNS_ADMIN defined in the registry (if TNS_ADMIN environment variable is not present)
  3. %ORACLE_HOME%/network/admin directory (if TNS_ADMIN environment variable is not present)

I would recommend to define an environment variable for TNS_ADMIN and use only one tnsnames.ora file. In order to be on the safe side, check also your registry values.

If your files are not located in %ORACLE_HOME%\network\admin, I recommend to create a symbolic link for it - just to be on the very safe side, e.g. mklink /d %ORACLE_HOME%\network\admin c:\Oracle\common\settings\admin

Another note, you don't have to "play" with your tnsnames.ora file. With Process Monitor from Microsoft Sysinternals you can monitor each file access, i.e. the filter would be Path contains tnsnames

Update

When I run a test on my machine I get following order:

  1. Environment variable TNS_ADMIN
  2. Registry Key HKEY_CURRENT_USER\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

  3. Registry Key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN, resp. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

    -> Only if TNS_ADMIN Environment variable is not set.

  4. %ORACLE_HOME%\network\admin
  5. Current directory (which can be different to directory where your application is located)
  6. Folder where your application is located

Update 2

Obviously there is no fix search, it varies for different providers/drivers. Maybe it also depends on the Oracle version.

For example, the Oracle HTTP Server reads TNS_ADMIN setting from opmn.xml config file.

Another example, for ODP.NET Managed Driver (Oracle.ManagedDataAccess) beta version, I found this order at Oracle Managed and TNS Names :

  1. data source alias in the 'dataSources' section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.
  4. data source alias in the tnsnames.ora file present at %TNS_ADMIN%
    (where %TNS_ADMIN% is an environment variable setting).
  5. data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin
    (where %ORACLE_HOME% is an environment variable setting).

In official documentation (12c Release 4 (12.1.0.2.4)) it says:

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.

However, based on some tests I made with ODP.NET Managed Driver (4.121.2.0) it takes %ORACLE_HOME%\network\admin and TNS_ADMIN Environment variable into account. Locks like the documentation is not 100% correct.

Tags:

Oracle

Toad

Sqlplus

Recent Posts