Re: db hang on shutdown

Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) wrote in news:3c433fbd.7157151_at_ausnews.austin.ibm.com:

> 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.)

The source of the problem may be because you are using OEM's "Intelligent Agent". If so stop it before shutting down the DB.

If you are desparate & have some "extra" disk space, you might want to try to add a "BIG" (2GB) file to TEMP tablespace.

HTH & YMMV! HAND! Received on Tue Jan 15 2002 - 19:27:35 CST