Re: db hang on shutdown

On Wed, 16 Jan 2002 01:27:35 GMT, Ban Spam <ban-spam_at_operamail.com> wrote:

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

To quote myself from above: "my shutdown script stops the Oracle Agent prior to shutting down the db's"
>
>If you are desparate & have some "extra" disk space,
>you might want to try to add a "BIG" (2GB) file to TEMP tablespace.
>

Help me out here. What does having a very large TEMP tablespace do here?

>
>HTH & YMMV!
>
>
>HAND!
>
>

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