May 2, 2013
This will be my last blog on the linked server to an Oracle database.
Linked server is a great solution for easy datatransfer, but the downsite is, that linked servers and windows updates (security) don't mix very well. Every time you have security updates your linked server connection with oracle gets corrupted, to be precise, the linked server provider OraOLEDB.Oracle is.
In my last 3 blogs on linked server, we've given you multiple solutions.
-Troubleshooting a linked server
This last blog, will show you the easiest way to solve the issue, to the re-install of the oracle client.
In our case, we have 2 installations and 2 homes:
- Oracle Db 11g Release 2 Client (18.104.22.168.0) for Microsoft Windows x64 (Oracle11gR2-Client-x64)
- 64-bit ODAC 11.2 Release 5 (22.214.171.124.20) Xcopy for Windows x64 (ODAC1120320_x64)
De Oracle 11.2.0 Client software:
De Oracle 126.96.36.199.20 ODAC software:
We've learned along the way, since this isn't the first time, and troubleshooting got us nowhere, we do it drastically, we're gonna reinstall the ODAC server in order to have the corrupt oraOLEDB11.dll reinstalled.
We currently have 2 Oracle trees, Home1 for the client in order to have sqlplus to test connections and home2 is the ODAC installation, used for the linked server. So we start with reinstalling ODAC to see if this fixes the oracle connection.
Now we open the setup exe from the ODAC1120320_64 and choose to deinstall the ODAC products from home2 and press remove...
Now, reinstall ODAC
To see what we have installed in both dirs:
After this, we restart the server and the linked server works.
If you still experience problems, the ODAC install also comes with a script to update ASP.NET providers:
@@InstallOracleASPNETCommon.sql @@InstallOracleMembership.sql @@InstallOraclePersonalization.sql @@InstallOracleProfile.sql @@InstallOracleRoles.sql @@InstallOracleSiteMap.sql @@InstallOracleWebEvents.sql
-- Install OracleSessionState Provider -- For Oracle 10gR1(10.1.0.2) database and higher, run InstallOracleSessionState.sql -- For Oracle 9iR2(9.2.x) database, run InstallOracleSessionState92.sql instead
I hope these linked server blogs have been easy and back to basic tutorials and you found them useful, please let us know if they've been helpful to you.