Re:RE: shutdown immediate hangs

On Mon, 10 Sep 2001, dgoulet_at_vicr.com wrote:

> One correction, shutdown immediate waits till all uncommitted
> transactions are committed or rolled back. The client session must
> do one or the other, otherwise the shutdown hangs waiting for the
> end user.

No, that's shutdown transactional.

> ____________________Reply Separator____________________
> Author: "Walthour; Jon (GEAE; Compaq)" <Jon.walthour_at_ae.ge.com>
>
> The message in the alert log means that there are uncommitted
> transactions.

Not necessarily, there are other (unnecessary, time-wasting) actions that must be performed before a shutdowm immediate completes successfully. One of these is cleaning up sort segments. Any operation that is part of the immediate shutdown routine can hold up the shutdown and cause this message to be produced, not just rollback.

> You could do a shutdown abort, but then you would have to have this
> rollback done during startup

Since version 7, rollback takes place after the database is open and available to users. This is not a problem.

> Oracle does not recommend shutdown aborts unless absolutely
> necessary

This is misguided advice. In parts of the Oracle documentation, they say to avoid aborts, but in the FailSafe documentation, they mention that abort is the preferable and default shutdown mode for high availability. I agree.

> and one shouldn't rely on a cold backup taken on an aborted
> db. Either way, though, the rollback must occur.

Shutdown abort/startup restrict/shutdown immediate should be a reasonable way to get the desired state of the database for a cold backup.

> One thing you could do is check v$transaction before you shutdown. The
> used_ublk column may give you some idea of how much rollback has to be done.

Good advice, if the problem is transaction rollback.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton



> -----Original Message-----

>

> I have Oracle 8.1.7 running on windoze NT 4.0.  Every night I have a script

> that kicks off a cold backup.  Part of the script issues the command

>

> SHUTDOWN IMMEDIATE

>

> which normally works just fine before I actually copy my files.  However,

> periodically the "shutdown immediate" command does not work.  It just hangs

> until someone complains that they cannot connect to the database or when I

> come in early in the morning and notice it hanging. In my <SID>Alrt.log I

> get the following

>

> SHUTDOWN: waiting for active calls to complete

>

> This also shows up in a trace file file multiple times.  I have to issue a

> "shutdown abort" to stop the database.

>

> In doing some research on metalink I see that otheres also have this

> problem.  The responses from Oracle are somewhat vague.  I do see that one

> of the responses seems to indicate that if the intelligent agent is running

> that it could be the cause for the hanging.  Has anyone else had this

> problem and solved it by shutting down the intelligent agent service??  Any

> other things that I can do to remedy this.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).