0

I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This caused the 11g Client to not be able to connect to the Oracle Instances because of the PATH variable using the 12c TNSnames instead of the 11g TNSnames.

The PATH variable was corrected and the 11g Client can now connect to the Oracle Instances using TNSPing or SQLPlus but when trying to use the linked server the SQL Server will go into PREEMPTIVE mode with the wait type PREEMPTIVE_COM_COCREATEINSTANCE. The linked server query will not make a connection to Oracle or error out and cause the SQL Server to not allow any new connections. They will get stuck trying to login with the wait PREEMPTIVE_OS_GETPROCADDRESS and wait for the Linked Server query to finish in order to login.

Any thoughts on why the Linked Server would be causing long PREEMPTIVE_COM_COCREATEINSTANCE waits?

2

These calls are all part of loading the Oracle OleDb provider. Not sure why they would block instead of failing, but it suggests that your Oracle client setup is still not not right.

The 11g OraOLEDB.Oracle provider not only needs to access the correct TNSNAMES.ORA file. It also needs to load the correct OCI.DLL, which it does through the path.

OraOLEDB.Oracle, like all OleDB providers, is a COM component. So it's loaded with a call to CoCreateInstance(). The OleDb provider in turn calls calls LoadLibrary() to find OCI.DLL, and then (in the normal DLL-loading process) calls GetProcAddress() to find the expected DLL entry point functions. Any time SQL Server calls one of these Win32 functions it enters a 'PREEMPTIVE_...' waits.

So you need to ensure that the LoadLibrary function call finds and loads the correct copy of OCI.DLL. You typically do this by setting the system path so that the folder containing your desired OCI.DLL appears first, and rebooting.

You can verify you're loading the expected OleDB provider from an elevated powershell prompt by running

PS C:\WINDOWS\system32> ls  hklm:\SOFTWARE\Classes\OraOLEDB.Oracle

to discover the CLSID of the OleDb provider, and then looking it up

PS C:\WINDOWS\system32> ls  "hklm:\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}"

Hive: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3F63C36E-51A3-11D2-BB7D-00C04FA30080}


Name                           Property
----                           --------
ExtendedErrors                 (default) : Extended Error Lookup Service
InprocServer32                 (default)      : c:\oracle\odac64\bin\OraOLEDB12.dll
                               ThreadingModel : Both
OLE DB Provider                (default) : Oracle Provider for OLE DB
ProgID                         (default) : OraOLEDB.Oracle.1
TypeLib                        (default) : {0BB9AFD1-51A1-11D2-BB7D-00C04FA30080}
VersionIndependentProgID       (default) : OraOLEDB.Oracle

The full process is ProgId `OraOLEDB.Oracle' is resolved to a CLSID. The CLISD is resolved to a .dll (InProcServer32). The .DLL is loaded and it, in turn, runs LoadLibrary to load oci.dll. Any link in that chain can break, including finding a copy of OCI.DLL of the wrong "bitness". CoCreateInstance is bitness-aware, but LoadLibrary is not.

You can also test connectivity in powershell. First ensure you are in a process with matching "bitness" to SQL Server.

PS C:\WINDOWS\system32> [system.environment]::Is64BitProcess

Then

PS C:\WINDOWS\system32> $con= new-object system.data.oledb.oledbconnection
PS C:\WINDOWS\system32> $con.connectionstring="provider=OraOLEDB.Oracle;data source=//server/xe;user id=xxx;password=xxx"
PS C:\WINDOWS\system32> $con.Open()
  • Thanks for the response and I have updated the path variable to point to the Oracle 11g home, not just the TNSNames, rebooted and am still experiencing the same issue. – KenWilson Aug 26 at 14:13
  • I was able to use process explorer to determine that the Linked Server is still using the wrong OCI.dll. I am trying to figure out why since the PATH variable is pointing to the 11g home. – KenWilson Aug 26 at 14:24
  • You can double-check SQL Server's path by briefly enabling xp_cmdshell and running exec xp_cmdshell 'path'. – David Browne - Microsoft Aug 26 at 14:27
  • It's showing the correct location for the 11g client as the first entry in the PATH variable as well. – KenWilson Aug 26 at 14:43
  • See update for additional troubleshooting. – David Browne - Microsoft Aug 26 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.