Good Old Connectivity Issue with Oracle in x64 Environment


When you try to develop an SSIS package in x64 Environment in BIDS that connects to a Oracle Database, you will invariabely run into connectivity issues if the installed Oracle Client tools version is 10.2.0.1 (x86). You will obviously need to install the 32-BIT version of Oracle Client to communicate with BIDS which is a 32-BIT application !


Subject:


ORA-12154 or ORA-6413 Running 32-bit Oracle Software on 64-bit Windows OS.


 


Applies to:


Oracle Net Services - Version: 8.1.7.0 to 10.2.0.2


Oracle Data Provider for .NET - Version: 8.1.7.0 to 10.2.0.2


Oracle Objects for OLE - Version: 8.1.7.0 to 10.2.0.2


Oracle Provider for OLE DB - Version: 8.1.7.0 to 10.2.0.2


Oracle ODBC Driver - Version: 8.1.7.0 to 10.2.0.2


Microsoft Windows XP (64-bit Itanium)


Microsoft Windows Server 2003 (64-bit Itanium)


Microsoft Windows XP (64-bit AMD64 and Intel EM64T)


Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)


 


Symptoms:



You are attempting to connect to the Oracle database from a Windows platform using one of the following programmatic interfaces


· ODBC


· OLEDB


· OO4O


· ODP.NET


 


After installing 32-bit Oracle client software on a 64-bit Windows operating system (OS) you receive one of the following errors:


ORA-12154: TNS:could not resolve the connect identifier specified


or


ORA-6413: Connection not open.


 


Cause:


64-bit Microsoft OS's install 32-bit applications into the following location


"C:\Program Files (x86)\..."


rather than the typical location of


"C:\Program Files\..."


This causes an existing networking bug to occur where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.


The following bug has been filed to correct this behavior:


Bug 3807408 <https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3807408>


 


NOTE: You will be able to view this bug details in the Metalinks site only if you have a Metalink login ID which comes with a support agreement with Oracle


 


Additional Information:


The reason you receive an ORA-12154 vs. an ORA-6413 is generally due to which programmatic interface you have chosen to use to connect to Oracle.


The ORA-12154 is the typical error seen when connecting with up-to-date interfaces using the latest version of the Oracle Call Interface (OCI):


· Oracle ODBC Driver


· Oracle Provider for OLE DB


· Oracle Objects for OLE


· Oracle Data Provider for .NET (ODP.NET)


· Microsoft's .NET Managed Provider for Oracle


The ORA-6413 is typical of using older interfaces which make legacy API calls such as Oracle's OCI Version 7 API:


· Microsoft ODBC Driver for Oracle


· Microsoft OLE DB Provider for Oracle


 


Solution:


To resolve this problem try the following solution:


 


The Best possible solution is to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0) rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error. If it is absolutely necessary to stick to the 10G version of the client (it shouldn't be), refer to the following: 


 


For 32-BIT Oracle Client:


1. Install 10.2.0.2 upgrade - You need to select the correct Oracle Home path of previous 10.2.0.1 install


 


                This install might fail at copying gacutil.exe...


               


                To Fix this search for gacutil* in C drive and you will find gacutil.exe anc gacutil.exe.config files in some directory


 


                Copy them to C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin


 


                Some times you can't find gacutil.exe then copy this file from some other server


 


2. Rerun the previous install after copying gacutil files - Install will be successful, After that you need to install a patch(5383042) for 10.2.0.2


 


3. To install the patch for 10.2.0.2:


               Create folder Patches in  D:\oracle\product\10.2.0\client_1


 


                Copy folder 5383042 from D:\Oracle32bit\Patchfor10202\p5383042_10202_WINNT TO D:\oracle\product\10.2.0\client_1\Patches


               


                Go to command prompt


 


                cd D:\oracle\product\10.2.0\client_1


 


                set oracle_home=D:\oracle\product\10.2.0\client_1


 


                cd D:\oracle\product\10.2.0\client_1\Patches\5383042>


 


                D:\oracle\product\10.2.0\client_1\OPatch\opatch apply


 


                The above command calls opatch.bat and you are in the patch folder so "apply" will apply the current patch folder you are in


 


                It will prompt you for y/n ...say y


 


 


                Create folder Patches in  D:\oracle\product\10.2.0\client_1


 


                Copy folder 5383042 from D:\Oracle32bit\Patchfor10202\p5383042_10202_WINNT TO D:\oracle\product\10.2.0\client_1\Patches


               


                Go to command prompt


 


                cd D:\oracle\product\10.2.0\client_1


 


                set oracle_home=D:\oracle\product\10.2.0\client_1


 


                cd D:\oracle\product\10.2.0\client_1\Patches\5383042>


 


                D:\oracle\product\10.2.0\client_1\OPatch\opatch apply


 


                The above command calls opatch.bat and you are in the patch folder so "apply" will apply the current patch folder you are in


 


                It will prompt you for y/n ...say y


 


                To check if the patch has been applied


               


                D:\oracle\product\10.2.0\client_1\OPatch lsinventory


 


For 64-BIT Oracle Client:



1. Install 64bit 10.2.02 upgrade - Select the correct Oracle Home.


 


2. Install 64bit 10.2.03 upgrade - Select the correct Oracle Home.


 


3. Copy TNSNAMES folder from other servers to D drive.


 



4. Create an Environment variable TNS_ADMIN to path D:\TNSNAMES


 


However, It is strongly recommended to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0) rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error. 


 


NOTE: This will allow you to create and run the package successfully from BIDS, but not as a Sql Job (in a x64 version of Sql Server). The reason being x64 version of Sql Server will always invoke x64 version of the DTEXEC.exe to run a job which will cause it to fail. We can confirm that by running the job successfully from command line using the 32-BIT DTEXEC.exe. (Running it from the C:\Program Files (x86) directory). So, to workaround this problem, we can choose one of the following solutions:


 


Solution 1:


Run the Job under the cmdExec subsystem and modify the command line so that the 32-BIT DTEXEC.exe is invoked to execute the job.


 


Phase 1 Update Dec 12, 2009:


Note: In Sql Server 2008 onwards you can still run the Job under Integration Services subsystem and use 32-BIT DTEXEC.exe. You need to go to "Execution options" in the Job Step properties and check "Use 32-BIT Runtime"


 


Solution 2:


Install the 64-BIT Oracle Client tools side by side with the 32-BIT one (Make sure you either apply the patch if you install 10G version or STRONGLY recommended to use the 11G Release 1 version)


 


Solution 3:


Modify the following Registry key to run the job under the Integration Services subsystem but to invoke the 32-BIT DTEXEC.exe to execute the package:


 HKLM ->SOFTWARE->MICROSOFT->MSDTS->SETUP->DTSPATH. Change the value of the DEFAULT key to point to the 32-BIT DTEXEC.exe. Typically, under Default installation, that path will be C:\Program Files (x86)\Microsoft SQL Server\90\DTS\


NOTE: You will need to restart the Sql Server Agent Service for the registry change to take effect.


 


Cheers,


Debs !!

Comments (5)
  1. jopicarra says:

    Can I use the instant client? I’ve seen some posts telling only full client would work.

    My Oracle tools are for W2003 64bits but the odbc driver is SQLORA32. The Oracle database is 32bits (linux 32bits). Do I need to have de 64 driver too?

    Thankx

  2. Jopicarra,

    Instant client wont work, you have to have the full client.

    Your second question: It depends on how you access Oracle.If you only use 32 bit apps to connect/access Oracle you wont need to have 64 bit drivers.

  3. OmarInnova says:

    Congratulations, very clear ducument ! It shows how well you know and handle the issue. Tks a lot.

  4. Okay, I have a 10g Express edition on my Windows 7 box. I am using Visual Studio 2010. I have downloaded ODAC 11, both the 32-bit and 64-bit versions. No matter what I do I get either the ORA-6413 "connection not open" error or the "BadImageFormatException". I have tried about every permutation of my ASP.NET project configuration that I can think of. Absolutely none of this works. Interestingly, in a Visual C# 2008 Express project earlier this year I ran into the same problem. I set the project to build to a 32-bit solution. No problem thereafter. If I try to do the same with my Visual Studio 2010 IDE, I still get these stupid errors which are causing me to lose my mind.

Comments are closed.

Skip to main content