Udostępnij za pośrednictwem

Good Old Connectivity Issue with Oracle in x64 Environment

  • Artykuł

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

  • Anonymous
    February 03, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/02/04/good-old-connectivity-issue/

  • Anonymous
    January 06, 2010
    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

  • Anonymous
    January 10, 2010
    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.

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

  • Anonymous
    November 26, 2011
    The comment has been removed