Determining location of relevant tnsnames.ora file

Question

I installed both the 32 and 64-bit Oracle 11g drivers. I search my PC looking for files with the name "tnsnames.ora" and found 3 in the following locations:

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 3rd location of the tnsnames.ora file surprises me.

I have the following Oracle clients installed on my PC:

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

Based on the location of each program (Program Files (x86) vs. c:\Program Files), This suggests to me that the Toad, a 32 bit program, should use the 32 bit driver and dbForge should use the 64 bit driver.

dbForge seems to use either the tnsnames.ora file in either location #2 or #3. I know this by systematically renaming all but one of the tns files and then checking to see if the connection names read from the file are available when trying to create a new connection from with the app.

However, TOAD seems to only recognize the tnsnames.ora file in location #3 and it did not recognize the tnsnames.ora file in location 2 at all! (Being that it was a 32 bit program, I did not expect it to recognize the tns file in location 2 and that was the case). TO summarize the TOAD test for the sake of hopeful clarity, TOAD only recognized the tns file in location 3.

Other colleagues do not have a tns file in location 3 on their machines. I'm not sure why I do. When I run Toad, it shows the following 2 Home, with the 32 bit Home as being the active one.

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 doesn't 32 bit TOAD use the tns in location #1 instead of only using the one in location #3?

Q3: If I leave on the tns file in location 3, both dbForge and TOAD work but I'd like to know why so I can accurately understand how to move tns info 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 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.