I just got done wrestling with multiple versions of the oracle client. We wanted to install an Asp.net app that uses the 11g ODP.net install on a server that already had a 10g client installed. While 11g and 10gR2 are supposed to have decent support for multiple oracle homes, the 10g client that was installed was only R1. To make matters worse, the app using the 10g client was an old asp app via an ODBC dsn. So rather than risking messing up the existing asp apps, I decided to give the xcopy deployment a shot.
Getting the actual software setup is pretty well documented with an article on Oracle's website. It really came down to just unzipping the package and running:
install.bat odp.net20 c:\oracle\product\1.1.0-xcopy-dep odac11_xcopy
However, my whole point in doing this was to avoid polluting the existing environment so a couple of items were out. First of all, the last step in the article is to add a couple of environment variables. Because I didn't want to interfer with the oracle home of an existing client install, i wanted to avoid this. However without it, I got a nice exception: "Oracle.DataAccess.Client.OracleException The provider is not compatible with the version of Oracle client"
The fix was to add the following to my Application_Start event in the global.asax:
Environment.SetEnvironmentVariable("PATH", @"c:\oracle\product\1.1.0-xcopy-dep;c:\oracle\product\1.1.0-xcopy-dep\bin;", EnvironmentVariableTarget.Process);
Environment.SetEnvironmentVariable("ORACLE_HOME", @"c:\oracle\product\1.1.0-xcopy-dep;", EnvironmentVariableTarget.Process);
This got me to an oracle error that I recognized: "Oracle.DataAccess.Client.OracleException ORA-12154: TNS:could not resolve the connect identifier specified." This is usally fixed by changing tnsnames.ora but in this case it didn't work. While it might be possible to use tnsnames with this xcopy deployment, I decided on a different route. I could have used the host/service name format in the connection string, but this oracle environment is setup with RAC (clustering). The oracle driver has a nice feature where the tns infomation can be inline in the connection string.
So this:"Data Source=MyTNSName;User Id=user;Password=pwd;"
became this (which is right out of my tnsnames.ora):
"Data Source= (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orahost1.company.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = orahost2.company.com)(PORT = 1522))
(LOAD_BALANCE = on)
(SERVER = DEDICATED)
(SERVICE_NAME = MyTNSService.company.com)
Bypassing Distributed Transactions and the OracleMTSRecoveryService
That nearly got everything working. The only thing left was a particular block of code that I had wrapped in a System.Transactions.TransactionScope block. This normally requires the OraceMTSRecoveryService. This is something that is installable with the xcopy deployment (run install.bat with no params for usage), but this would have overwritten an existing version of the service if it was already there. Instead I decided to force the transactions to local only (it was only a single call to the db so I knew I wouldn't need a distributed transaction). This could be done by adding "Promotable Transactions=local" to the connection string but I'm compiling against an older version of the driver (188.8.131.52 x64) in order to support both 64 and 32 bit with the same code and this option wasn't supported in this version. Instead I changed the following registry key:
After that everything seems to be working.
Consider using a config change instead of a registry change with:
<add name="PromotableTransaction" value="local"/>
Reason? I forgot to change the registry fix back and fought with "there was an error promoting the transaction to a distributed transaction oracle" for a day before I figured it out .
5/17/2011 - Update:
I've done this again with the 4.0 provider and tnsnames.ora worked fine. I think the first time I did this I didn't also update sqlnet.ora to use tnsnames with names.directory_path=(TNSNAMES,EZCONNECT).
Another thing I don't remember doing in the 2.0 version was using the xcopydir\odp.net\bin\4\oraprovcfg utility. I needed to put Oracle.DataAccess.dll in the GAC, but doing it manually didn't work. I had to run oraprovcfg /action:gac /providerpath:<fullpath to Oracle.DataAccess.dll>
Last, I've also discovered that ODP.net supports the following configuration that should be a replacement for setting the path and oracle_home environment variables in code:
<add key="DllPath" value="c:\oracle\product\1.1.0-xcopy-dep\BIN"/>
See the following for more info:http://docs.oracle.com/html/E10927_01/InstallODP.htm