db hang on shutdown

Back in May I posted this problem and got several good, thoughtful responses, but each lead seemed to be a dead end and the problem persisted. It got put on the back burner for a while, but I'd like to take another crack at it now.

Platform is Oracle 8.0.5 running on NT 4. (Yes, I know 8.0.5 is de-supported. One of my tasks for this year is to get us upgraded.)

Every weekend we have a small maintenance window. During this time I have operations people bounce all of my Oracle servers. I have a simple NT command file that they execute to gracefully shut down the databases (some of the servers have multiple db's) before bouncing the machine. All works as planned except 1 particular db will hang during shutdown, eventually necessitating our pulling the plug on it. The logs show that we were able to connect and issue the shutdown, but an hour later we're still wating for it to complete.

A typcial sequence of msgs from the alert log looks like this:

Sun Dec 23 02:13:39 2001
Shutting down instance (immediate)
License high water mark = 151

(and then the first msg of the next startup.)
Dump file e:\Oradmin\LCOP\bdump\lcopALRT.LOG Sun Dec 23 02:59:31 2001

My first thought was that the shutdown was waiting for a large amount of rollback to complete. This lead me to a discovery that may or may not be related to the hanging shutdown and presents another question of itself. When I check this sid for current sessions I typically find there are well over 100. HOWEVER, the nature of the application is such that there are only six machines that can be connected to the database at any one time. These machines are touch-screen kiosks running a single, full screen application, so there is no possibility of mutliple sessions from a single machine. That presents the secondary question of how am I getting so many sessions? By querying V$SESSION_WAIT, I have observed sessions that were several DAYS old! As far as the impact on transaction rollback at shutdown, the application goes through several screens of read-only information gathering before performing a write/update transaction. It issues a disconnect immediately upon completion of the write/update. That being the case, I can't see it generating an inordinate amount of rollback.

When I last approached this problem, I received the following sugestions:

One respondent reported seeing a problem with shutdowns if auditing was enabled.
(I find audit_trail=none)

Another suggestion was to incrase extent size on my temp ts so that SMON would have fewer extents to de-allocate. However, my temp TS is "temporary" so I wouldn't think this would be an issue. When I do a "select * from dba_extents where TABLESPACE_NAME = <my temp TS> I get zero rows. A select distinct segment_type from dba_extents indicates no temporary segments..

Another was "In 8.0.5 there was a bug regarding multiple db_writers with large buffer cache. It causes Oracle to hang in shutdown immediate for several hours."
(I find db_writer_processes = 1)

Another was to look as SNMP processes. However, we do not use Oracle job scheduleing, and my shutdown script stops the Oracle Agent prior to shutting down the db's.

Any takers to either question?
1 - Why does this one db seem to hang on a shutdown immediate 2 - Why does this one db show well over 100 sessions (some up to several days old) when there are only six client machines in existence?

--
Ed Stevens


(Opinions expressed do not necessarily represent those of my employer.)