InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 13:28:23 GMT20http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxYou're welcome. Thu, 01 Nov 2012 20:13:57 GMTprvminehttp://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxHey 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 BThu, 01 Nov 2012 09:51:21 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxDan, Agree good time to upgrade. [quote]1 - Any reason that you know of to just upgrade to 10.2.0.2 and not to 10.2.0.5?[/quote] - looks like 10.2.05 oracle client has been certified on Windows 2003 Server, so should be okay[quote]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?[/quote] - 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.Fri, 26 Oct 2012 17:33:16 GMTprvminehttp://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxHey 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 BFri, 26 Oct 2012 10:58:34 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxOK. 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 BFri, 26 Oct 2012 09:57:28 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxOkay, 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 [strike]this[/strike] these references [url]http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx[/url] and [url]http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/[/url] to monitor and diagnose Virtual Address Space.Hope this helps. . .Thu, 25 Oct 2012 18:15:31 GMTprvminehttp://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxListDLLs from Sysinternals shows the same info. The DLLs are loaded after a restart, and at some point they are unloaded. Base Size Path0x0000000001000000 0x2652000 sqlservr.exe0x0000000077ec0000 0x13c000 ntdll.dll0x0000000077d40000 0x173000 kernel32.dll0x0000000078130000 0xc9000 MSVCR80.dll0x000000007fc00000 0x86000 msvcrt.dll0x000000007c420000 0x109000 MSVCP80.dll0x000000007fee0000 0x109000 ADVAPI32.dll0x000000007fd30000 0x1a0000 RPCRT4.dll0x000000007e9c0000 0x22000 Secur32.dll0x00000000344d0000 0x7000 sqlos.dll0x000000007c680000 0x10a000 USERENV.dll0x0000000077c20000 0x10c000 USER32.dll0x000000007fc90000 0x9c000 GDI32.dll0x000000007e240000 0x50000 WINMM.dll0x00000000333e0000 0x8000 opends60.dll0x0000000077370000 0x99000 NETAPI32.dll0x000000007e380000 0x10000 psapi.dll0x0000000048060000 0xd000 instapi.dll0x000000004f610000 0x1a9000 sqlevn70.RLL0x0000000068000000 0x4c000 rsaenh.dll0x000000007e490000 0x2e000 AUTHZ.DLL0x000000007ee50000 0x6f000 MSCOREE.DLL0x0000000057140000 0x285000 ole32.dll0x000000007e500000 0x45000 msv1_0.dll0x000000007dc90000 0xf000 cryptdll.dll0x0000000077310000 0x30000 WS2_32.dll0x0000000077140000 0xc000 WS2HELP.dll0x0000000057040000 0x31000 iphlpapi.dll0x0000000077410000 0xb5000 kerberos.dll0x000000007d340000 0x2c000 MSASN1.dll0x000000007dd50000 0x45000 schannel.dll0x000000007d370000 0x15f000 CRYPT32.dll0x000000007eaf0000 0xc6000 COMRES.DLL0x000000005c5b0000 0x7000 XOLEHLP.DLL0x0000000067140000 0xd4000 MSDTCPRX.dll0x000000007ebc0000 0x116000 OLEAUT32.dll0x0000000006b30000 0xea000 msvcp60.dll0x000000007b540000 0x2a000 MTXCLU.DLL0x000000007fbf0000 0xb000 VERSION.dll0x00000000770f0000 0xa000 WSOCK32.dll0x000000007b3a0000 0x24000 CLUSAPI.DLL0x000000007b310000 0x1c000 RESUTILS.DLL0x00000000771b0000 0x7d000 mswsock.dll0x000000007e8f0000 0x4f000 DNSAPI.dll0x000000007e9f0000 0xb000 winrnr.dll0x000000007e950000 0x66000 WLDAP32.dll0x000000007ea00000 0x7000 rasadhlp.dll0x0000000077530000 0x5000 security.dll0x0000000049980000 0x3b1000 msfte.dll0x0000000008a00000 0x15d000 dbghelp.dll0x000000007e3e0000 0x50000 WINTRUST.dll0x000000007e470000 0x15000 imagehlp.dll0x0000000068100000 0x3f000 dssenh.dll0x000000006d200000 0x93000 hnetcfg.dll0x0000000077170000 0xb000 wshtcpip.dll0x000000007e4c0000 0x3c000 NTMARTA.DLL0x0000000077150000 0x16000 SAMLIB.dll0x000000007dcb0000 0x24000 ntdsapi.dll0x0000000009040000 0x2c7000 xpsp2res.dll0x000000007ea10000 0xde000 CLBCatQ.DLL0x00000000337a0000 0x2c4000 sqlncli.dll0x0000000076200000 0xef000 COMCTL32.dll0x000000007d540000 0x77000 comdlg32.dll0x000000007ef60000 0x9b000 SHLWAPI.dll0x000000007f190000 0xa0c000 SHELL32.dll0x000000007f000000 0x187000 comctl32.dll0x0000000035000000 0x33000 SQLNCLIR.RLL0x0000000000f30000 0x21000 msftepxy.dll0x000000004a7c0000 0x8000 xpsqlbot.dll0x0000000053c30000 0x88000 xpstar90.dll0x0000000053ad0000 0xc000 SQLSCM90.dll0x0000000063f00000 0x69000 ODBC32.dll0x00000000520c0000 0x2d000 BatchParser90.dll0x000000007c630000 0x1e000 ATL80.DLL0x0000000063dc0000 0x18000 odbcint.dll0x000000000ad10000 0x26000 xpstar90.rll0x0000000034730000 0x10000 xplog70.dll0x000000000ad50000 0x3000 xplog70.RLL0x000000005a9e0000 0xc1000 oledb32.dll0x000000007adb0000 0x28000 MSDART.DLL0x0000000063ab0000 0x11000 OLEDB32R.DLL0x0000000057c40000 0x22b000 comsvcs.dll0x000000007e220000 0x1c000 ATL.DLL0x0000000010000000 0x88000 OraOLEDB10.DLL0x000000000b160000 0x2db000 oraclient10.dll0x000000000b440000 0x144000 oracore10.dll0x000000000b590000 0xf3000 oranls10.dll0x000000000b690000 0x1d000 oraunls10.dll0x000000000b6b0000 0x7000 orauts.dll0x000000000b6c0000 0x7000 oravsn10.dll0x000000000b6d0000 0x129000 oracommon10.dll0x000000000b800000 0x6a1000 orageneric10.dll0x000000000beb0000 0x32000 orasnls10.dll0x000000000bef0000 0x1c8000 oraxml10.dll0x0000000065b30000 0x1b000 msvcirt.dll0x000000000c0c0000 0x16a000 orannzsbb10.dll0x000000000c230000 0x167000 oran10.dll0x000000000c3a0000 0x4b000 oranl10.dll0x000000000c3f0000 0x1b000 oranldap10.dll0x000000000c410000 0x7a000 oraldapclnt10.dll0x000000000c490000 0x21000 orancrypt10.dll0x000000000c4c0000 0x59000 oranro10.dll0x000000000c520000 0x9000 oranhost10.dll0x000000000c530000 0x7000 orancds10.dll0x000000000c540000 0xa000 orantns10.dll0x000000000c550000 0x446000 orapls10.dll0x000000000c9a0000 0xc000 oraslax10.dll0x000000000c9b0000 0x415000 oraplp10.dll0x000000000cdd0000 0x75000 orahasgen10.dll0x000000000ce50000 0x7c000 oraocr10.dll0x000000000ced0000 0x52000 oraocrb10.dll0x000000000cf30000 0x14000 orantcp10.dll0x000000000cf50000 0x64000 orasql10.dll0x0000000044100000 0x27000 OraOLEDBgmr10.dll0x0000000044400000 0x33000 OraOLEDBrst10.dll0x0000000044700000 0x9000 OraOLEDButl10.dll0x0000000044300000 0x7000 OraOLEDB10us.dll0x0000000044200000 0x9000 OraOLEDBpus10.dll0x0000000033380000 0x18000 odsole70.dll0x000000007b070000 0xd000 wbemprox.dll0x000000007b7e0000 0x85000 wbemcomn.dll0x000000007cc60000 0x1e7000 SXS.DLL0x000000007b400000 0x2f000 wmiutils.dll0x000000007b050000 0x12000 wbemsvc.dll0x000000007c030000 0xdd000 fastprox.dll0x0000000079170000 0x45000 scrrun.dll0x00000000796f0000 0x16a000 MFC42.dll0x0000000080000000 0x198000 xpSLS.dll0x0000000034510000 0x2c000 sqlvdi.dllThu, 25 Oct 2012 10:28:13 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxHey 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.Thu, 25 Oct 2012 09:09:25 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxseveral things to check1) Is the oracle client path in the environmental settings - image attached2) is Oracle10g Release 2 ODAC 64 Bit installed3) 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 .Wed, 24 Oct 2012 19:56:15 GMTprvminehttp://www.sqlservercentral.com/Forums/Topic1376665-1042-1.aspxHello 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 x64One 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 x64All 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[code="sql"]select * from sys.dm_os_loaded_modules where company = 'Oracle Corporation'order by description[/code]Sample results[quote]base_address file_version product_version debug patched prerelease private_build special_build language company description name0x000000000B440000 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.dll0x000000000B6B0000 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.dll0x000000000C410000 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.dll0x000000000C230000 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[/quote]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 BWed, 24 Oct 2012 14:17:16 GMTskrilla99