Oracle ODP.net xcopy deployment for asp.net.

by Brandon 7. April 2010 01:28

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);

Inline TNSNAMES

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)
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = MyTNSService.company.com)
            (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))
          )
        )
      ;
      User Id=user;Password=pwd;"

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 (2.111.6.20 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:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.111.7.20]
"PromotableTransaction"="local"

After that everything seems to be working.

P.S. 

Consider using a config change instead of a registry change with:
  <oracle.dataaccess.client>
    <settings>
      <add name="PromotableTransaction" value="local"/>
    </settings>
  </oracle.dataaccess.client>

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 Embarassed.

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:


<configuration>
  <oracle.dataaccess.client>
    <add key="DllPath" value="c:\oracle\product\1.1.0-xcopy-dep\BIN"/>
  </oracle.dataaccess.client>
</configuration>

 See the following for more info:http://docs.oracle.com/html/E10927_01/InstallODP.htm

Comments

4/12/2011 1:24:15 AM #

Sam

You rock!  I'm developing an ASP.NET 4.0 app using the 11g version of ODP.NET and I encounter the same problem.  This solves the issue with having multiple versions of Oracle client in a host machine. Btw, I only needed to include the environment variables in Application_Start in global.asax as follows:

void Application_Start(object sender, EventArgs e) {
            // Workaround on Oracle issue
            // We need to specify here 2 environment variables
            Environment.SetEnvironmentVariable("PATH", @"C:\oracle\product\11.2.0\client_1;C:\oracle\product\11.2.0\client_1\bin;", EnvironmentVariableTarget.Process);
            Environment.SetEnvironmentVariable("ORACLE_HOME", @"C:\oracle\product\11.2.0\client_1;", EnvironmentVariableTarget.Process);

Then the full connect string in web.config, as follows:
"Data Source=  (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = orahost1.company.com)(PORT = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = orahost2.company.com)(PORT = 1522))
          (LOAD_BALANCE = on)
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = MyTNSService.company.com)
            (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))
          )
        )
      ;
      User Id=user;Password=pwd;"

Thank you so much!

Sam United States |

About the authors

Three brothers (yes we're really brothers) all writing and architecting .net solutions.  Our original inspiration for this blog was simply to give the same useful advice we receive from other blogs on a very regular basis.  Lets face it, thanks to the Internet and its contributors, we're a long way away from the days of shifting through a 900 page reference book to find that one line of code.  But lately we've also noticed the shift in the view of application development.   Where once we were the heros of optimization and efficiency, we're now seen a bunch of budget blowing goof-offs that nobody trusts.  We've got some ideas to change that and we'll try to share those thoughts between the short snips of code.