high number of db connections to Oracle from bad Microsoft ODBC driver

In the past week, we've seen a very natty problem between our ASP-based website and our Oracle database that was difficult to troubleshoot. As I'm still on vacation, this problem is even more frustrating!

We have a website that is powered by an Oracle database. Our web application is traditional ASP. We were running Oracle 9i on the server; specifically, 9.2.0.7. In the webserver code, we connect to the database via an ODBC system DSN. The Oracle client version was 8.1.7.

We saw that when we used the older Microsoft ODBC for Oracle driver (version 2.573.7400.00):


we'd occasionally see huge spikes in connections and database deadlocks. The side effect of the deadlocks was that connections from the web servers to the database would increase from 5-10 to 50-100! In turn, this spike in connections made the website slow down to a crawl for the users. Ugly!

We were only able to identify the problem by doing memory dumps on both the web servers and database server simultaneously. We then sent the dumps and SQLnet logs to Oracle and Microsoft. It was a Microsoft engineer who identified the lock condition. In this condition, ASP requests for data sent through the Microsoft ODBC driver waited for database responses that were never returned from the database. On the database side, the database was waiting for CLOSE acknowledgements that never came back from the ODBC driver. Nightmare!

The Microsoft engineer told us that the specific Microsoft for Oracle ODBC driver was no longer supported. Oracle technicians stated that they had seen problems with high database connections and ODBC with Oracle driver versions 9.1 and below. Through a series of successful and unsuccessful driver installations, we finally had a working system without the connection hangs using the Oracle for Ora9i driver version 9.02.00.07 and the latest hotfix patches:


Here is a list of the patches to get to 9.2.0.7:
1) Oracle 9207 patch set (#4163445)
2) Oracle Data Access Software for Windows patch (#4622091)
3) ODBC supplementary patch to the above #4622091 patch (#4640047)

Eeesh!

For those of you wishing to see all the ugly install steps, here they are:

1) Download patches at https://metalink.oracle.com/ or more specifically:
http://www.oracle.com/technology/software/tech/windows/odbc/index.html

Install Oracle 9207 patch set (#4163445)
2) shutdown iis, set startup to manual
3) shutdown Distributed Transaction Coordinator and Oracle MTS Recovery Service
4) reboot in order to clear any connections to Oracle DLLs
5) expand the Oracle 9207 patch set (p4163445_92070_WINNT.zip) to a temp dir (view README.html for general install info)
6) The installer uses the Oracle 10i installer that comes with the patch set. So run the installer from the temp directory ->
../Disk1/setup.exe.
7) Click Next on Welcome screen
8) The patchset directory should automatically be filled in as "Source." For Destination, select "OraHome9i". Click Next.
9) You'll get a summary of the files that the Oracle Installer is going to update. Click Install.
10) If all goes well, the Oracle patch set will be installed successfully.

Install Oracle Data Access Software for Windows patch (#4622091)
11) expand the Oracle Data Access Software for Windows patch (p4622091_92070_MSWIN.zip)
12) Run the installer in the expanded directory ..\ODAC9.2.0.7\Disk1\setup.exe
13) The patchset directory should automatically be filled in as "Source."
For Destination, select "OraHome9i". Click Next.
14) Select Oracle ODBC Driver 9.2.0.7.0 and click Next
15) You'll get a Summary screen to review. Click Install. You should get a successful message. Click Next Install.
16) Keep the same Source/Destination as you started with
17) Select Oracle Data Provider for .NET 9.2.0.7.0 and click Next
18) You'll get a Summary screen to review. Click Install. You should get a successful message. Click Exit and Yes to confirm.

Install one-off, ODBC supplementary patch to the above #4622091 patch (#4640047)
19) expand the Oracle Data Access Software for Windows patch (p4640047_92070_MSWIN.zip)
20) copy sqora32.dll, sqoras32.dll, sqresus.dll into the bin folder of Oracle home (should be D:\Oracle9i\bin)
21) reenable iis startup as automatic
22) reboot for good measure

That is the whole enchilada. Yuck!

CS0007: Unexpected common language runtime initialization error -- 'Catastrophic failure '

Update: apparently, the reason why this error occurred was that my system had some security lockdown permission changes which conflicted with the list of patches below..hence the need to make those permissions changes in steps 4 and 5 below. Harumph.

Argh. One of the recent set of MS security hotfixes has broken ASP.net with the following error:
CS0007: Unexpected common language runtime initialization error -- 'Catastrophic failure '

I upgraded my Win2K server with the following patches:
KB905915 IE6 SP1
KB902400 2K
KB908523 2K
KB912919 2K
KB891861 2K SP4 Rollup
KB890830 Dec Malware Removal

I've found a workaround here:
http://www.iis-resources.com/modules/newbb/viewtopic.php?topic_id=4246&forum=4

Here is the text of the workaround:
PROBLEM
========
Running ASP.NET application was giving
Compiler Error Message: CS0007: Unexpected common language runtime initialization error -- 'Catastrophic failure'
RESOLUTION
============
There were some problems with the permission on the aspnet account. Later we found that EVERYONE group was missing READ permission on c:\windows\registration folder. Below given are the steps to check the permissions of aspnet account at various places.
Following are Generic Steps that we can take to fix the various issues related to ASP.NET that you are seeing on different Windows 2000 SP4 boxes which we believe is happening because of the security patches you installed.

Please make sure that the ASPX pages run under SYSTEM account before proceeding further with following steps
Steps 1 and 2 would make sure that you get rid of event id: 1007 and 1084
Follow Step 3 if you are getting if you are getting "Compiler Error Message: BC32400: Class 'CLSID_CorSysWrite_SxS could not be created 'Catastrophic failure'
Steps 4 and 5 are for "Compiler Error Message: CS0007: Unexpected common runtime initialization error -- 'Catastrophic failure'"

1. Running ASPNetRegIIS –ua followed by ASPNetRegIIS –i from the framework folder.

2. Make sure that ASPNET account has got permission as per following matrix
Location Access type Account Comments installroot\ASP.NET Temporary Files Read/write Process or configured impersonation. This is the location for dynamically compiled files. Beneath this location, application code generation takes place in a discrete directory for each application. The root location itself can be configured using the tempDir attribute of the section. systemroot\assembly Read Process or configured impersonation. This is the location of the global assembly cache. systemroot\system32 R Process Contains system DLLs loaded by the .NET Framework. systemroot\temp Read/write/delete Process Used for web services support. userprofile directory Read/write Process Used by the global assembly cache lock files and the common language runtime security configuration caching mechanism. If the user profile directory for the account doesn't exist, ASP.NET will use the default userprofile directory.application directory Read Process or configured impersonation. This is the location for application files. installroot hierarchy Read Process or configured impersonation ASP.NET must be able to access the system assemblies referenced in the Machine.config file in the config subdirectory under installroot. Default Web Site monitoring. Read Process Used for file change notification monitoring
3 Make sure that ASPNET account has got read permission on following keys
HKCR\CLSID\{0AE2DEB0-F901-478B-BB9F-881EE8066788}
HKCR\CLSID\{0A29FF9E-7F9C-4437-8B11-F424491E3931}
HKCR\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98
4. In the %windir%/registration folder, make sure that the Everyone group has READ permissions.
- In the advanced security properties of the .clb files in the %windir%/registration folder, make sure that the Inherit from parent the permission entries that apply to child objects. Include these with entries explicitly defined here option is selected.
- Make sure that the Everyone group has one of the following permissions: Traverse permissions (“List Folder Contents”) on all parent directories (%systemdrive%, %windir%, and %windir%\registration), or The Bypass Traverse Checking permission is selected.
5. Running RegSvr32.EXE mscoree.dll from the C:\winnt\system32

MS05-051 causes problems with ASP pages

There is a workaround by applying IWAM permissions to a specific folder. The fix given by Microsoft is to add RX permissions for IWAM on c:\winnt\registration folder - it corrects a dcom error on the server. This fix does NOT work for my ASP.net application. So I have removed the patch until an appropriate fix is made.