I am experiencing an odd problem with my systems and hope someone here can shed some light on my issues. So, here are the details...
SQL Server 2005 Std Ed. SP4 x64
One production cluster, one test cluster and one single server dev instance all on Windows 2003 Server, x64, all VMWare Guests.
Oracle Client 10.2.0 x64
All three instances use the Oracle client to connect via Linked Server to the same Oracle database.
Everything seems to work properly using the linked server until we start to get Error 7302 - "Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked Server "" ". There doesn't seem to be anything that precedes this issue. One day it works, the next it doesn't.
I get the error message if I try to execute a query, or if I right-click the linked server and choose Test.
The provider oraOLEDB.Oracle provider still shows in the list and Allow In Process is checked.
No changes to TNSNames.ora or SQLnet.ora.
TNSPing is successful using the name I am connecting to.
I can connect from the instance using SQLPlus and the same login the linked server uses.
Creating a new linked server with the same information fails with the same error message.
Appears to affect all Oracle Linked servers.
Restart SQL. Every time this happens, I have to restart the SQL Server services. After restart everything works properly for a long period of time until the problem recurs.
Of course, this is not an optimal solution as it requires some downtime and customers notice, but so far it is the only way I have found to get the link working again.
I have been poking around in master to see what catalogs might be helpful and have found sys.dm_os_loaded_modules. If search this table while I am experiencing the issue, there are no entries for Oracle (company = 'Oracle Corporation'). If I search after I restart the database services I see entries for Oracle, some 34 of them.
where company = 'Oracle Corporation'
order by description
base_address file_version product_version debug patched prerelease private_build special_build language company description name
0x000000000B440000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle CORE Library C:\oracle\product\10.2.0\client_1\bin\oracore10.dll
0x000000000B6B0000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle CORE UTS PT Library C:\oracle\product\10.2.0\client_1\bin\orauts.dll
0x000000000C410000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle Internet Directory Client Library C:\oracle\product\10.2.0\client_1\bin\oraldapclnt10.dll
0x000000000C230000 10.2:0.1 0.0:0.0 NULL NULL NULL NULL NULL 67699888 Oracle Corporation Oracle Net ORAN10 DLL C:\oracle\product\10.2.0\client_1\bin\oran10.dll
So, what gives? Is there a known time where modules that have been loaded into SQL process space are pushed out? Does this happen if the system is experiencing heavy memory presssure? Is it possible to reload these modules without restarting SQL?