Re: db hang on shutdown

Ed,

use a better shutdown routine.
This is from memory, please test.

execute in svrmgr30:

alter system checkpoint;
alter system archive log all;
shutdown abort
startup mount restrict
alter system set job_queue_processes=0;
alter database open;
shutdown normal

in this manner, you get a shutdown guaranteed, and the dbms_jobs can't interfere with the restart/clean shutdown.

hth,

Paul

Ed Stevens wrote:

> 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.)
>
Received on Tue Jan 15 2002 - 23:51:15 CST