Locating the corresponding tnsnames.ora file

Question

I installed the 32-bit and 64-bit Oracle 11g drivers. I search my computer to search for files named "tnsnames.ora" and found 3 in the following places:

1. C:\Oracle\product\11203_32bit\CLIENT_1\NETWORK\ADMIN 2. C:\Oracle\product\11203_64bit\CLIENT_1\NETWORK\ADMIN 3. C:\Windows\TNS 

The existence of the third place in the tnsnames.ora file surprises me.

The following Oracle clients are installed on my computer:

 "C:\Program Files (x86)\Quest Software\Toad for Oracle 11.6\Toad.exe" "C:\Program Files\Devart\dbForge Studio Express for Oracle\dbforgeoracle.exe" 

Depending on the location of each program (Program Files (x86) and c: \ Program Files), this means that Toad, a 32-bit program, should use a 32-bit driver, and dbForge should use 64 bits.

dbForge seems to use the tnsnames.ora file anywhere # 2 or # 3. I know this by systematically renaming everything except one of the tns files, and then checking to see if the connection names from the file are available when you try to create a new connection with using the application.

However, TOAD seems to only recognize the tnsnames.ora file at location # 3 and does not recognize the tnsnames.ora file at location 2 at all! (Since it was a 32-bit program, I did not expect it to recognize the tns file in location 2, and that was so). To summarize the TOAD test for reliable clarity, TOAD only recognized the tns file at location 3.

Other colleagues do not have a tns file at location 3 on their machines. I am not sure why I am doing this. When I launch Toad, it shows the following 2 Home, with the 32-bit Home as active.

 OraClient11g_home1 (11.2.0.3) ORACLE_HOME:C:\app\C39293\product\11.2.0\client_1 ORACLE_HOME_NAME:OraClient11g_home1 ORACLE_HOME_KEY:HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1 ORACLE_SID: NLS_LANG:AMERICAN_AMERICA.WE8MSWIN1252 SQLPATH: LOCAL: Client DLL:C:\app\C39293\product\11.2.0\client_1\oci.dll TNSNames.ora: SQLNet.ora: LDAP.ora: Login.sql: GLogin.sql: In system PATH:No Home is valid:No OraClient11g_home1_32bit (11.2.0.3) ORACLE_HOME:c:\oracle\product\11203_32bit\CLIENT_1 ORACLE_HOME_NAME:OraClient11g_home1_32bit ORACLE_HOME_KEY:HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1_32bit ORACLE_SID: NLS_LANG:AMERICAN_AMERICA.WE8MSWIN1252 SQLPATH:c:\oracle\product\11203_32bit\CLIENT_1\dbs LOCAL: Client DLL:c:\oracle\product\11203_32bit\CLIENT_1\bin\oci.dll TNSNames.ora: SQLNet.ora: LDAP.ora: Login.sql: GLogin.sql:c:\oracle\product\11203_32bit\CLIENT_1\sqlplus\admin\glogin.sql In system PATH:Yes 

Q1: Is OraClient11g_home1 my 64-bit home, or do I have two Oracle clients installed?

Q2: Why does the 32-bit TOAD not use tns at location # 1 instead of using only the one located at location # 3?

Q3: If I stay in the tns file in 3rd place, both dbForge and TOAD work, but I would like to know why I can understand exactly how to transfer tns information from one machine to another.

Answer 1

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

Toad will only see the Oracle client, which is designed for the same platform as it. Thus, your 64-bit client is not related to Toad. C: \ Windows \ TNS looks like the folder used for the TNS_ADMIN folder, given its odd location and the fact that Toad sees this. At the command prompt, execute SET TNS_ADMIN and see if it will report "TNS_ADMIN = C: \ Windows \ TNS". If so, then all tools should use this tnsnames.ora. This is a global override if you point to the folder containing your network configuration files. If you do not have TNS_ADMIN defined as an environment variable, find it in the Oracle root registry: HKEY_LOCAL_MACHINE \ Software \ Oracle.

If you use a common connection set for all your tools, I would delete all your tnsnames.ora files. I would also move the C: \ Windows \ TNS folder to something more suitable, like C: \ Oracle \ Admin, and create your tnsnames.ora, sqlnet.ora and ldap.ora (if applicable). Create the TNS_ADMIN environment variable pointing to this location.

Answer 2

According to Oracle, these search locations are performed by tnsnames.ora , respectively. sqlnet.ora and ldap.ora :

• Oracle Net Files in the Current Working Directory (PWD / CWD)
• TNS_ADMIN defined by session or user-defined script
• TNS_ADMIN , defined as a global environment variable
• TNS_ADMIN defined in the registry
• Oracle Net Files in %ORACLE_HOME/network|net80\admin (Oracle default location)

However, I am not sure if each application / driver should follow this list. I got this list from Oracle Document 111942.1 , citing Oracle 9i, so it may be deprecated.

I would recommend defining the environment variable for TNS_ADMIN and using only one tnsnames.ora file. To be safe, also check your registry values.

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

One more note: you do not need to “play” with your tnsnames.ora file. Using Process Monitor from Microsoft Sysinternals you can control every access to the file, i.e. the filter will be Path contains tnsnames

Update

When I run the test on my machine, I get the following order:

• TNS_ADMIN environment TNS_ADMIN
• Registry Key HKEY_CURRENT_USER\SOFTWARE\ORACLE\KEY_{Oracle_Home_Name}\TNS_ADMIN

• 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 the TNS_ADMIN Environment variable TNS_ADMIN not set.

• %ORACLE_HOME%\network\admin
• Current directory (which may differ from the directory in which your application is located)
• The folder where your application is located.

Update 2

Obviously, the fix search does not exist; it differs for different providers / drivers. Perhaps this also depends on the version of Oracle.

For example, for the version of ODP.NET Managed Driver (Oracle.ManagedDataAccess) beta strong> I found this order in Oracle Managed and TNS names :

• in the "dataSources" section of the <oracle.manageddataaccess.client> section in the .NET configuration file (i.e. machine.config , web.config , user.config ).
• The data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET configuration file.
• The alias for the data source in the tnsnames.ora file, which is present in the same directory as .exe .
• data source alias in the tnsnames.ora file present in %TNS_ADMIN%
  (where %TNS_ADMIN% is the environment parameter).
• data source alias in the tnsnames.ora file present in %ORACLE_HOME%\network\admin
  (where %ORACLE_HOME% is the environment setting).

The official documentation (12c Release 4) says:

• in the dataSources section of the <oracle.manageddataaccess.client> section in the .NET configuration file (i.e. machine.config , web.config , user.config ).).
• The data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET configuration file. The location may consist of absolute or relative directory paths.
• The alias for the data source in the tnsnames.ora file, which is present in the same directory as .exe .

However, based on some tests that I did with the ODP.NET managed driver (4.121.2.0), it takes into account the %ORACLE_HOME%\network\admin and TNS_ADMIN environment TNS_ADMIN . Locks like documentation are not 100% correct.