Installing Oracle 11g 32bit & 64bit clients would be a resolution of the famous 64bit OraOLEDB issue.
Accessing an Oracle DB over a 64 bit system was a pain for many SQL Server DBAs. The purpose of this document is to explain how to fix issues running a SSIS package, Linked Server and SQL Agent which are using Oracle OLEDB under 64bit SQL Server.
The Following solution is based on Oracle 11g and above versions. You can download Oracle 11g client and above versions from http://www.oracle.com/technology/software/products/database/index.html
Oracle Client & OLEDB component Installation steps in 64bit Windows
- If you have old Oracle clients, copy tnsnames.ora and sqlnet.ora files for backup purpose. (They are under %ORACLE_HOME%/network/ADMIN/.)
- Uninstall & Delete previous version of Oracle client include installed directory. If you have or ever installed Oracle client, uninstall them and reboot the server. Then you can delete old Oracle directory. If you don’t reboot, you cannot delete them and also reset below registry keys to their default values :
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
- HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
- OracleOciLib = oci_temp.dll
- OracleSqlLib = SQLLib80.dll
- OracleXaLib = xa80.dll
- Put setup.exe file(s) on short file name folder path!
- Run setup.exe of Oracle 11g Client in 32bit SQL Server running machine. You have to install 32bit first (In Oracle Client Setup 11.2 is a BUG so you have to install 32bit first !!).
- Select Custom under Select Installation Type and choose below products :
- SQL Plus
- Oracle Call Interface
- Oracle Net
- Oracle Connection Manager
- Oracle ODBC Driver
- Oracle Services For Microsoft Transaction Server
- Oracle Administration Assistant For Windows
- Oracle Counters for Windows Performance Monitor
- Oracle Provider for OLE DB
- Oracle Data Provider for .Net
- Oracle Providers for ASP.Net
- After installation, restore TNSNAMES.ORA backup file under the new location of %ORACLE_HOME%/network/admin folder.
- Now Restart the server and Repeat steps 4 to 5 with 64bit Oracle 11g Client setup file.
Oracle Base directory is same as the 32bit, but Path is supposed to be different from the previous one mentioned in step 5.
i.e) If Oracle 32bit Client path was C:/Oracle/product/11.1/client_32, Then 64bit Client path could be placed under C:/Oracle/product/11.1/client_64. - You will see an error message OracleMTSRecoveryService already exists during the 64bit client installation. You can Ignore it.
- Restart the server
- Modify the system registry settings as follows. And then reboot server! (You don’t need to apply below step for Oracle 18c drivers)
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
- HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
- OracleOciLib =dll
- OracleSqlLib = dll (old: SQLLib80.dll)
- OracleXaLib = dll (old: xa80.dll)
- Test sqlplus.exe or tnsping.exe in command console.
- Install Oracle Release 2 64-bit ODAC software (Client path could be C:/Oracle/product/11.1/client_odac64)
- Restart the server
- Open SQL Server Management Studio under Providers node inside Linked Server, Set the Allow InProcess option for one of your providers in the list (Right Click Provider Options, and then click to select the Allow InProcess check box, this will apply for all other providers)
- After creating a Linked Server, if you encountered with this error : ORA-12154 or SQL Error 7303 you should remove Allow InProcess checkbox option
Congratulations! Now you can access Oracle OLEDB & ODBC provider in BIDS (Business Intelligence Development Studio) & SSMS both places.
PS:
In SSIS for using new 32 & 64 bit drivers of Oracle Provider for OLED DB instead of deprecated 32 bit driver of Microsoft OLE DB provider for oracle, install ODAC 32, after installing all above drivers
Database administration is an exciting job combining knowledge, art and experience; you must live with it to love it, despite all the difficulties.
Currently I'm working as a Microsoft SQL Server DBA and BI consultant.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family.