Troubleshooting a linked server (error 7302 Could not create an instance of OLE DB provider ‘OraOleDB’)

I spent the last two days looking on internet for a solution on a linked server error know as ‘7302’, there are a lot of hits and the same 3 or 4 solutions keep coming up, I tried them all but no success. To save you from going through endless solutions I am going to sum up all the possible solutions, ending with the most valuable solution, well at least it was the best fix for me.

Here are links to linked server articles I wrote before:

building a linked server
Creating jobs and queries with linked server

Now, the dreading 7302 error Could not create an instance of OLE DB provider ‘OraOleDB’
First a solution that saved our asses last time, but this won’t always do the trick! “After installing a new service pack to the mssql server in 2011, the linked server stopped working, giving an 7302 error ‘Could not create an instance of OLE DB provider ‘MSDAORA’. If your LS stops working after it has been working fine for months and all the tips above are working, i found a quick work around to get your Linked server to work again, just simply check the dynamic parameter box and save and then uncheck it again.”

But this time we didn’t get away with it this easy, almost the same error, different solution needed

once again this error occurs after a series of windows updates we performed, so we started undoing the updates, but ofcourse the error stayed.

On the internet they advise you to change the DCOM security of the MSDAINITIALIZE (Acces Permissions was on Use default, change it to customize, if not already, change the other 2 in customize as well.)

I added the domain user to the permission section. But this solution didn’t work in my case. secondly we opened the Registry and look for: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829} and change the owner to administrator, but in my case administrator was already the owner, so no luck here. (source: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx)

I also checked the Environmental Variables to see if the PATH still contained oracle (e.g. D:/ORACLE/BIN;D:/Oracle/Client;)

Then i tried reconfiguring the Oracle ODAC install in cmd:

D:\oracle>configure
D:\oracle>configure all orahome

but still no luck…

Sometimes restoring your dll files from the day before the error occurs does the trick, but again..nothing.

So, my final solution, re-install the oracle client, this time, with a standard oracle tree that contains some tools to check network and database connection, tnsping and sqlplus.

(re)installing oracle environment:

i used two oracle installations

– Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows x64 (64-bit) this installs the standard oracle home and tools like tnsping and sqlplus

– 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64
for a non heavy client for LS to depent on.

first install the oracle 11.2.0.1.0 client and tools.

edit the tns names ora file to your database:

Secondly we reinstalled the ODAC again, now the OraOleDB drivers are available again. Everything is configured for the LS again. MSSQL needs to reload the OraOLEDB file in, for it will keep the old, corrupt file in use, we need to restart the mssql servers.

So if you walked the same path like me, just reinstall oraclient environment. Good luck!

Special thanks to B. Kooiman for helping with the oracle install!

How do I completely uninstall a Oracle server

Uninstalling Oracle is not a daily event.

well, maybe if you are an oracle consultant of course.  But for me it was the first time, I am more an MSSQL person, with some Oracle knowledge. So, I will guide you through a very decent uninstall which is quite effective and save for the network.

First we stop all the Oracle services, next use the OUI to uninstall your Oracle product. You have to select all products that need to be removed. In our case, we just remove everything.

Second, we need to manually remove all the registry keys

HKEY_LOCAL_MACHINE\SOFTWARE
HKEY_LOCAL_MACHINE\SOFTWARE\Classes remove keys beginning with Ora/ORCL
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\ApplicatioHKEY_LOCAL_MACHINE\SOFTWARE\ODBC remove all keys related with the “Oracle ODBC Driver”

Now we need to remove Oracle from your environment Variables and remove references under ‘PATH’. e.g.“I:\ora9idbcl\bin;I:\oradev6\BIN;D:\oracle\product\10.2.0\BIN”

Delete the oracle icons from StartMenu/Programs and ProgramFiles and remove any oracle related .ini files left behind in the Windows directory.

Then you reboot your computer.

Last steps are to remove the last bits of oracle left on your server, after this reboot you can start cleaning up or archive all Oracle files, backups etc.

Maybe you have a windows task running, in our case a task for a hotbackup has to be stopped and some analyze tasks.

Just run a search on ‘ora’ on the server to see if any vital oracle thingies have been left behind and you’re done!