June 2, 2010 at 5:05 pm
Specs:
Windows Server 2003 R2 x64 SP2
SQL Server 2008 10.0.2531 (64 bit)
Visual Studio 2008 v9.0.30729.1 SP
.NET Framework v 3.5 SP1
We have SSIS packages that pull data from Oracle databases (9i, 10g) and insert into our SQL Server. I have installed the Oracle 11g R2 64 bit and 32 bit clients (Administrator) on the SQL Server machine. The packages run just fine in 64 bit mode (through the Job Agent), but when I try to run the packages in 32 bit mode, they fail with the following error:
Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion()
From what I've read online, you should be able to install 32 bit and 64 bit clients side by side and both should work. I've tried installing 32 bit followed by 64 bit, and vice versa. I've rebooted, restarted the SQL Server, restarted IIS, etc. Nothing seems to be working. Has anyone been able to resolve this type of issue?
Also, the clients were installed in the following directories:
Oracle\product\11.2.0\client_32
Oracle\product\11.2.0\client_64
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
June 3, 2010 at 11:17 am
I'm not an expert but I understand you can run 32 bit client in a 64 bit O/S IF you enable 64 bit mode via Enable32bitAppOnWin64
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 7, 2010 at 1:22 pm
This is an IIS 6.0 setting, correct? Can you give me a brief how-to in making this change?
Also, I should not that the ADO.NET driver (provided by Microsoft) is the one that displays the above error message. The OLE DB driver (provided by the Oracle Client) acts as if the 32 bit Oracle Client was never installed and shows this error:
The requested OLE DB provider OraOLEDB.Oracle.1 is not registered.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
June 9, 2010 at 4:44 pm
I finally figured it out! The 11g R1 32 bit client succeeded where the R2 client failed. I cleared out the existing installations, installed the 32 bit 11g R1 Oracle Client followed by the 64 bit 11g R2 Oracle Client. I considered doing R1 for both, but R1 requires a separate ODAC download to get the 64 bit OLE DB driver.
One thing to add: the R1 installer lets you rename your HOME, while R2 doesn't. If you don't rename HOME_1 to something else during the R1 installation, R2 uses HOME_1 by default and will overwrite the existing folder groups in your Start menu.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
June 10, 2010 at 7:41 pm
Fantastic feedback - people will benefit of it for a long time. Great job! 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 17, 2010 at 4:54 am
I am trying to get the 64 bit drivers.. the links given in the oracle site points to the failsafe versions of 64bit drivers.. where to get the 64bit ODAC drivers from? any help would be appreciated..
__________________________________________________________
Yuvaraj
October 20, 2010 at 12:29 pm
Here's the main downloads page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
The second section contains the Client download (win64_11gR2_client.zip).
If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
October 23, 2010 at 1:21 pm
Dirt McStain (10/20/2010)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
The second section contains the Client download (win64_11gR2_client.zip).
If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html%5B/quote%5D
With appreciation to your response.. I feel i need to explain more about my scenario here
Installed:
Windows 2008 R2 OS
SQL Server 2008 R2
Oracle 11g R2 ODAC components (32 bit)
Oracle 11g R2 Client (x64)
My Source and Destination database is Oracle DB. I need to use SSIS for the ETL operation.
I have found 4 types of providers for Oracle connectivity..
1.MS OLE DB provider for Oracle
2.MS provider for Oracle by Attunity
3.Oracle provider for OLE DB
4.Oracle Data Provider for .Net
out of the above 1 & 2 are ruled out.. since (1) is available only for 32 bit runtime environment and (2) is only for SS Enterprise edition customers..
(3) works fine under both the 32 & 64 bit environment.
(4) ODAC works fine under 32 bit runtime environment but not under 64 bit..
I wanted to use ODAC..since it outperforms in the above list.. When I installed my 32 bit drivers, i found
* Oracle Data Provider for .Net
* Oracle Data Access Components
in the list of installed items..
but when i installed 64 bit drivers from oracle. I find only
*Oracle Data Provider for .Net
in the list of installed items..
my package still doesn't run under the 64 bit when i choose ADO source..
Can someone explain what the issue might be here.. I beleive it's cos * Oracle Data Access Components is not mentioned in the installed items during 64-bit client installation..
Yuv
__________________________________________________________
Yuvaraj
October 25, 2010 at 4:23 am
Dirt McStain (10/20/2010)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
If you click on see all next to Microsoft Windows (x64), it'll take you to this page:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
The second section contains the Client download (win64_11gR2_client.zip).
If you want the ODAC 64 drivers for an old version of the client, then they are found here:
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html%5B/quote%5D
The link has ODAC 32 drivers.
October 26, 2010 at 10:01 pm
i don't have it in front of me, but there is a 64-bit ADO.NET driver. I don't remember what it comes up as in the menu, but it's there and it works in 64 bit without any problems for me. When I was testing out different versions of Oracle client, I remember that I ran into some problems. Installing Client 11g R1 32-bit alongside the 11g R2 64-bit driver cleared up all of the issues. The biggest problem is that you need the 32 bit driver while developing in BIDS but the jobs run in 64-bit mode. Installing the R2 32-bit driver caused a lot of problems, so I ended up using R1 instead.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
October 27, 2010 at 12:19 am
Dirt McStain (10/26/2010)
i don't have it in front of me, but there is a 64-bit ADO.NET driver. I don't remember what it comes up as in the menu, but it's there and it works in 64 bit without any problems for me. When I was testing out different versions of Oracle client, I remember that I ran into some problems. Installing Client 11g R1 32-bit alongside the 11g R2 64-bit driver cleared up all of the issues. The biggest problem is that you need the 32 bit driver while developing in BIDS but the jobs run in 64-bit mode. Installing the R2 32-bit driver caused a lot of problems, so I ended up using R1 instead.
Now i have a hope that 64 bit (Ado.Net) drivers exist for 11g Oracle.. I did an exhaustive search and downloaded all that i found with x64 from the oracle site. If you can leak out your installation procedure and post installation steps it could help me to find where i went wrong.
June 11, 2013 at 10:11 am
This is an old thread, but I'm sure many guys still suffer the dual Oracle client installation.
I managed to install both R2 versions of the Oracle Client without one installation messing the "client1" groups on the start menu and registry keys created by the other installation.
The trick is edit the oraparam.ini file found in your installation files at winXX_11gR2_client\client\install\. There all you need to edit is the key DEFAULT_HOME_NAME. Note the installer will weirdly add a "1" at the end, whatever you put there, so that's why I left the underscore at the end of each. This is how I left mine:
For 64 bit install:
DEFAULT_HOME_NAME=Ora11Home64_
For 32 bit install:
DEFAULT_HOME_NAME=Ora11Home32_
After the installation this will create two folders at the Start menu:
Oracle - Ora11Home64_1
Oracle - Ora11Home32_1
And in the registry you'll end with:
HKLM / Software / Oracle / KEY_Ora11Home64_1
HKLM / Software / Wow6432Node / Oracle / KEY_Ora11Home32_1
When you create ODBC or similar objects you'll get then the proper naming referring if it's using the 32 or the 64 bit driver.
Supposedly you can feed the ORACLE_HOME name trough the command line parameters when running setup.exe, but got sick of trying to find the way to make it work and the installer never liking it so editing the INI just makes the trick.
BTW - If anyone is going trough the dual install I highly recommend downloading the separate uninstaller so you can get rid of everything easily without going trough that annoying installer.
Good luck!
http://docs.oracle.com/cd/E11882_01/em.112/e12255/oui2_manage_oracle_homes.htm#CJAGCIAC
http://docs.oracle.com/cd/E11882_01/em.112/e12255/c_oui_appendix.htm#OUICG378
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply