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!
No comments:
Post a Comment