October 24, 2012 at 2:17 pm
Hello all,
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...
Environment:
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.
Problem:
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.
Troubleshooting:
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.
Current Solution:
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.
Further Troubleshooting:
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.
My query
select *
from sys.dm_os_loaded_modules
where company = 'Oracle Corporation'
order by description
Sample results
base_addressfile_versionproduct_versiondebugpatchedprereleaseprivate_buildspecial_buildlanguagecompanydescriptionname
0x000000000B44000010.2:0.10.0:0.0NULLNULLNULLNULLNULL67699888Oracle CorporationOracle CORE LibraryC:\oracle\product\10.2.0\client_1\bin\oracore10.dll
0x000000000B6B000010.2:0.10.0:0.0NULLNULLNULLNULLNULL67699888Oracle CorporationOracle CORE UTS PT LibraryC:\oracle\product\10.2.0\client_1\bin\orauts.dll
0x000000000C41000010.2:0.10.0:0.0NULLNULLNULLNULLNULL67699888Oracle CorporationOracle Internet Directory Client LibraryC:\oracle\product\10.2.0\client_1\bin\oraldapclnt10.dll
0x000000000C23000010.2:0.10.0:0.0NULLNULLNULLNULLNULL67699888Oracle CorporationOracle Net ORAN10 DLLC:\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?
Thanks!
Dan B
October 24, 2012 at 7:56 pm
several things to check
1) Is the oracle client path in the environmental settings - image attached
2) is Oracle10g Release 2 ODAC 64 Bit installed
3) check to see which version of the Oracle Client is installed if it is version 10.2.0.1.0 the suggestion is to apply Oracle patch 4547817 for upgrade oracle client to version 10.2.0.2.0 .
October 25, 2012 at 9:09 am
Hey prvmine,
Thanks for the response.
1. Yes, the client directory is in the PATH variable.
2. I don't think I have the ODAC explicitly installed. What I have is the Administrator option of the client install which seems to have all that I need to connect. Specifically the Oracle drivers for ODBC and OLEDB.
3. I am currently 10.2.0.1.0.
I'll look into applying the patch in item 3, but I think the Oracle client is working properly. I can still connect from the server using SQLPlus and I can ping using tnsping. When this issue happens I just can't get there from within SQL Server. Once I restart the SQL Server services I will be able to connect again and it will work for 3 to six months or whatever and then it happens again.
I think the issue is with SQL Server unloading the DLLs from its address space for some reason and being unable to use them again until the service is restarted and the DLLs reloaded. I would just prefer that I not have to take an extra outage to repair...
Sincerely,
Dan B.
October 25, 2012 at 10:28 am
ListDLLs from Sysinternals shows the same info. The DLLs are loaded after a restart, and at some point they are unloaded.
Base Size Path
0x0000000001000000 0x2652000 sqlservr.exe
0x0000000077ec0000 0x13c000 ntdll.dll
0x0000000077d40000 0x173000 kernel32.dll
0x0000000078130000 0xc9000 MSVCR80.dll
0x000000007fc00000 0x86000 msvcrt.dll
0x000000007c420000 0x109000 MSVCP80.dll
0x000000007fee0000 0x109000 ADVAPI32.dll
0x000000007fd30000 0x1a0000 RPCRT4.dll
0x000000007e9c0000 0x22000 Secur32.dll
0x00000000344d0000 0x7000 sqlos.dll
0x000000007c680000 0x10a000 USERENV.dll
0x0000000077c20000 0x10c000 USER32.dll
0x000000007fc90000 0x9c000 GDI32.dll
0x000000007e240000 0x50000 WINMM.dll
0x00000000333e0000 0x8000 opends60.dll
0x0000000077370000 0x99000 NETAPI32.dll
0x000000007e380000 0x10000 psapi.dll
0x0000000048060000 0xd000 instapi.dll
0x000000004f610000 0x1a9000 sqlevn70.RLL
0x0000000068000000 0x4c000 rsaenh.dll
0x000000007e490000 0x2e000 AUTHZ.DLL
0x000000007ee50000 0x6f000 MSCOREE.DLL
0x0000000057140000 0x285000 ole32.dll
0x000000007e500000 0x45000 msv1_0.dll
0x000000007dc90000 0xf000 cryptdll.dll
0x0000000077310000 0x30000 WS2_32.dll
0x0000000077140000 0xc000 WS2HELP.dll
0x0000000057040000 0x31000 iphlpapi.dll
0x0000000077410000 0xb5000 kerberos.dll
0x000000007d340000 0x2c000 MSASN1.dll
0x000000007dd50000 0x45000 schannel.dll
0x000000007d370000 0x15f000 CRYPT32.dll
0x000000007eaf0000 0xc6000 COMRES.DLL
0x000000005c5b0000 0x7000 XOLEHLP.DLL
0x0000000067140000 0xd4000 MSDTCPRX.dll
0x000000007ebc0000 0x116000 OLEAUT32.dll
0x0000000006b30000 0xea000 msvcp60.dll
0x000000007b540000 0x2a000 MTXCLU.DLL
0x000000007fbf0000 0xb000 VERSION.dll
0x00000000770f0000 0xa000 WSOCK32.dll
0x000000007b3a0000 0x24000 CLUSAPI.DLL
0x000000007b310000 0x1c000 RESUTILS.DLL
0x00000000771b0000 0x7d000 mswsock.dll
0x000000007e8f0000 0x4f000 DNSAPI.dll
0x000000007e9f0000 0xb000 winrnr.dll
0x000000007e950000 0x66000 WLDAP32.dll
0x000000007ea00000 0x7000 rasadhlp.dll
0x0000000077530000 0x5000 security.dll
0x0000000049980000 0x3b1000 msfte.dll
0x0000000008a00000 0x15d000 dbghelp.dll
0x000000007e3e0000 0x50000 WINTRUST.dll
0x000000007e470000 0x15000 imagehlp.dll
0x0000000068100000 0x3f000 dssenh.dll
0x000000006d200000 0x93000 hnetcfg.dll
0x0000000077170000 0xb000 wshtcpip.dll
0x000000007e4c0000 0x3c000 NTMARTA.DLL
0x0000000077150000 0x16000 SAMLIB.dll
0x000000007dcb0000 0x24000 ntdsapi.dll
0x0000000009040000 0x2c7000 xpsp2res.dll
0x000000007ea10000 0xde000 CLBCatQ.DLL
0x00000000337a0000 0x2c4000 sqlncli.dll
0x0000000076200000 0xef000 COMCTL32.dll
0x000000007d540000 0x77000 comdlg32.dll
0x000000007ef60000 0x9b000 SHLWAPI.dll
0x000000007f190000 0xa0c000 SHELL32.dll
0x000000007f000000 0x187000 comctl32.dll
0x0000000035000000 0x33000 SQLNCLIR.RLL
0x0000000000f30000 0x21000 msftepxy.dll
0x000000004a7c0000 0x8000 xpsqlbot.dll
0x0000000053c30000 0x88000 xpstar90.dll
0x0000000053ad0000 0xc000 SQLSCM90.dll
0x0000000063f00000 0x69000 ODBC32.dll
0x00000000520c0000 0x2d000 BatchParser90.dll
0x000000007c630000 0x1e000 ATL80.DLL
0x0000000063dc0000 0x18000 odbcint.dll
0x000000000ad10000 0x26000 xpstar90.rll
0x0000000034730000 0x10000 xplog70.dll
0x000000000ad50000 0x3000 xplog70.RLL
0x000000005a9e0000 0xc1000 oledb32.dll
0x000000007adb0000 0x28000 MSDART.DLL
0x0000000063ab0000 0x11000 OLEDB32R.DLL
0x0000000057c40000 0x22b000 comsvcs.dll
0x000000007e220000 0x1c000 ATL.DLL
0x0000000010000000 0x88000 OraOLEDB10.DLL
0x000000000b160000 0x2db000 oraclient10.dll
0x000000000b440000 0x144000 oracore10.dll
0x000000000b590000 0xf3000 oranls10.dll
0x000000000b690000 0x1d000 oraunls10.dll
0x000000000b6b0000 0x7000 orauts.dll
0x000000000b6c0000 0x7000 oravsn10.dll
0x000000000b6d0000 0x129000 oracommon10.dll
0x000000000b800000 0x6a1000 orageneric10.dll
0x000000000beb0000 0x32000 orasnls10.dll
0x000000000bef0000 0x1c8000 oraxml10.dll
0x0000000065b30000 0x1b000 msvcirt.dll
0x000000000c0c0000 0x16a000 orannzsbb10.dll
0x000000000c230000 0x167000 oran10.dll
0x000000000c3a0000 0x4b000 oranl10.dll
0x000000000c3f0000 0x1b000 oranldap10.dll
0x000000000c410000 0x7a000 oraldapclnt10.dll
0x000000000c490000 0x21000 orancrypt10.dll
0x000000000c4c0000 0x59000 oranro10.dll
0x000000000c520000 0x9000 oranhost10.dll
0x000000000c530000 0x7000 orancds10.dll
0x000000000c540000 0xa000 orantns10.dll
0x000000000c550000 0x446000 orapls10.dll
0x000000000c9a0000 0xc000 oraslax10.dll
0x000000000c9b0000 0x415000 oraplp10.dll
0x000000000cdd0000 0x75000 orahasgen10.dll
0x000000000ce50000 0x7c000 oraocr10.dll
0x000000000ced0000 0x52000 oraocrb10.dll
0x000000000cf30000 0x14000 orantcp10.dll
0x000000000cf50000 0x64000 orasql10.dll
0x0000000044100000 0x27000 OraOLEDBgmr10.dll
0x0000000044400000 0x33000 OraOLEDBrst10.dll
0x0000000044700000 0x9000 OraOLEDButl10.dll
0x0000000044300000 0x7000 OraOLEDB10us.dll
0x0000000044200000 0x9000 OraOLEDBpus10.dll
0x0000000033380000 0x18000 odsole70.dll
0x000000007b070000 0xd000 wbemprox.dll
0x000000007b7e0000 0x85000 wbemcomn.dll
0x000000007cc60000 0x1e7000 SXS.DLL
0x000000007b400000 0x2f000 wmiutils.dll
0x000000007b050000 0x12000 wbemsvc.dll
0x000000007c030000 0xdd000 fastprox.dll
0x0000000079170000 0x45000 scrrun.dll
0x00000000796f0000 0x16a000 MFC42.dll
0x0000000080000000 0x198000 xpSLS.dll
0x0000000034510000 0x2c000 sqlvdi.dll
October 25, 2012 at 6:15 pm
Okay, understand your point that it appears to be a sql server unloading dlls. However, the issue does appear to be fairly common with Oracle 10 client. So my thoughts are to address Oracle Client issues first.
In searching further I found this these references http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx and http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/ to monitor and diagnose Virtual Address Space.
Hope this helps. . .
October 26, 2012 at 9:57 am
OK. Thanks prvmine. I'll test out upgrading the clients to 10.2.0.2+.
Unfortunately, as this will require a restart of SQL and I am unable to force create the error condition I may never know if the fix worked...
Good time to push for a SQL upgrade.
-Dan B
October 26, 2012 at 10:58 am
Hey prvmine,
Two more questions, if you please.
1 - Any reason that you know of to just upgrade to 10.2.0.2 and not to 10.2.0.5?
2 - I have never installed the ODAC on any machine requiring connections to Oracle. Always the Admin Client. Is there something I am missing by not doing so?
Thanks,
Dan B
October 26, 2012 at 5:33 pm
Dan, Agree good time to upgrade.
1 - Any reason that you know of to just upgrade to 10.2.0.2 and not to 10.2.0.5?
- looks like 10.2.05 oracle client has been certified on Windows 2003 Server, so should be okay
2 - I have never installed the ODAC on any machine requiring connections to Oracle. Always the Admin Client. Is there something I am missing by not doing so?
- ODAC supports .Net framework via Oracle Data Provider for .NET, Oracle Providers for ASP.NET, ODBC, etc. Installing ODAC may be a bit over kill, but appears to have been part of the solution in resolving the disappearing dll’s in some instances. Totally understand wanting to be cautious when introducing new variables into production environment. So, one step at a time is always best.
Best of luck in resolving this issue and keep us posted.
prv.
November 1, 2012 at 9:51 am
Hey prvmine,
Thanks again for all of your help.
Between the lack of an environment where I could reliably reproduce the error and the prospect for a system refresh in the next six months we decided to simply take the outage and restart the database services which has resolved the symptom and reloaded the oracle client dlls.
Not optimal from a technical standpoint, but perhaps better for the business. At least for now.
In my opinion this instance is a bit memory constrained, so I think if it happens again soon I will look at bumping memtoleave or shrinking max server memory a bit.
Sincerely,
Dan B
November 1, 2012 at 8:13 pm
You're welcome.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply