ODP.NET and OLEDB with Multiple Oracle Homes

Question

For legacy reasons, our .NET 4.0 application currently uses both the Oracle OLEDB and ODP.NET providers to connect to an Oracle instance. We have standardized on the 11.2.0.3.0 Oracle client. Both data providers work as expected when one Oracle client is installed.

Issues have been reported on computers that already had the 11.2.0.1.0 client installed. A second client, 11.2.0.3.0, was installed for our application. The installation looks like this:

c:\oracle
    \product
      \11.2.0
        \client_1      <-- (existing) 11.2.0.1.0
          \bin         <-- OraOLEDB11.dll registered here
          \network
            \admin     <-- TNSNAMES does NOT contain ORACLESVR
        \client_2      <-- (new) 11.2.0.3.0
          \bin  
          \network
            \admin     <-- TNSNAMES contains ORACLESVR

Due to a bug in the 11.2.0.3.0 installer, the OLEDB driver is not registered in the second home, meaning the 11.2.0.1.0 driver remains registered.

This leads to some interesting/odd behavior that I cannot explain:

if the "ODP.NET from 11.2.0.3.0" part of the application is used first, both providers can connect, meaning the "OLEDB from 11.2.0.1.0" is using the tnsnames.ora from the _2 home.
if the "OLEDB from 11.2.0.1.0" part of the application is used first, neither provider will connect, presumably because both are using the tnsnames.ora from the _1 home.

So, once the Oracle home is determined for the application, both clients attempt to use that home, causing complete success or complete failure.

To work around this, we can do things like: register the 11.2.0.3.0 OLEDB provider, add the TNS_ADMIN environment variable, or add ORACLESVR the tnsnames.ora from the _1 home.

However, I want to know WHY this is happening? I cannot find, in the Oracle documentation for each provider, how the tnsnames.ora file is located when two clients are present and TNS_ADMIN is not specified.

How does one provider affect the other?

Wernfried Domscheit · Answer 1 · 2015-02-04T18:11:32.717

OLEDB is based on Microsoft COM and each DLL you register has to have a distinct name. Thus you cannot register OraOLEDB11.dll several times from different locations, i.e. OLEDB can be installed only once (per architecture).

In case you try to install several OLEDB on one machine the Oracle Installer creates a mess. It is always a challenge to have several Oracle Clients installed.

These locations are searched for tnsnames.ora, resp. sqlnet.ora:

current path (associated with the running client application)
Environment variable TNS_ADMIN defined for the session
Environment variable TNS_ADMIN defined for the system
Windows Registry Key HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN (for x64) or HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN (for x86)
%ORACLE_HOME%\network\admin

I got this list from Oracle Metalink 111942.1 (referring to Oracle 9.x and Windows NT/2000)

I don't know whether it applies for each individual Oracle Provider/Driver you may use. When you trace your application with Process Monitor you can get a different order.

ODP.NET and OLEDB with Multiple Oracle Homes

1 Answers1