Re: SQL Statement Shutdown

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Wed, 5 Mar 2003 22:52:51 -0000
Message-ID: <3e667ff5_2@mk-nntp-1.news.uk.worldonline.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0303041627.67374467_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<pan.2003.03.04.08.34.13.113105_at_yahoo.com.au>...
> > On Mon, 03 Mar 2003 17:47:56 +0000, Joel Garry wrote:
> >
> > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > > news:<pan.2003.03.03.19.11.40.446603_at_yahoo.com.au>...
> > >> On Mon, 03 Mar 2003 14:40:47 +0000, Patrice Castet wrote:
> > >>
> > >> > what do you want to shutdown ? the database ? a dispatcher ? by the
> > >> > way "shutdown abort" is like you "kill" your database, it has to
> > >> > perform a recovery when restarting ! I advice you to use "shutdown
> > >> > immediate" which stop it cleanly instead of "abort". The "abort"
> > >> > option must not be used except for emergency issues.
> > >> >
> > >> >
> > >> Provided you have protected yourself against the loss of your current
> > >> redo log by appropriate multiplexing and mirroring, there is zero
risk
> > >> in doing shutdown aborts. They are functionally equivalent to
shutdown
> > >> immediates, in the sense that both cause you to lose uncommitted
> > >> transactions, and both preserve all committed transactions. There is
> > >> nothing inherently dangerous or naughty about shutdown abort, and
they
> > >> are perfectly OK to use in situations which don't count as an
> > >> emergency.
> > >
> > > You think there is no risk in recovery? You've never seen a recovery
> > > session not work? You think there is no risk in the untrained doing
> > > shutdown abort and attempting recovery?
> >
> > Excuse me? What recovery does any user, however trained or untrained,
have
> > to do after a shutdown abort? The recovery that is involved is an
instance
> > recovery, and is performed entirely automatically by SMON upon
subsequent
> > startup.

>
> And you are saying that is somehow different than a manual recovery?
> Yes, the SMON recovery is fairly, no, _very_ stable on it's own, but
> external influences such as it happening to take a _very long time_ it
> some cases can really confuse people into doing the wrong thing.  And
> sometimes it can fall within the definition of "not work."
>

> >
> > And if you think there's anything inherently dodgy about Oracle's
instance
> > recoveries, then you'd best not use Oracle at all!

>
> So what would you recommend?  (What I think is dodgy isn't Oracle's
> instance recovery mechanism, but rather all the manual tasks involved
> in other recovery - and doing abort as a habit may lead to the other
> recovery.)
>

> >
> > > You've never seen a site
> > > where they do shutdown abort because they don't understand what is
> > > preventing the instance from shutting down immediate?
> >
> > I'm not responsible for people's ignorance. If they choose to kill
things
> > off, there are plenty worse ways of doing it (kill -9 anyone?). I'm not

>
> If you are giving advice, you are taking some responsibility.
> Actually I think that is a very good thing, and your advice is
> normally excellent.
>
> So why is kill -9 worse than abort?  Guess you haven't seen defunct
> processes that ignore kill -9.
>

> > saying it's polite or even desirable: I'm saying there is no greater
risk
> > of losing data with a shutdown abort than there is with a shutdown
> > immediate, provided you have already taken suitable precautions against
> > loss of online redo logs. And that functionally there is no difference
> > between an abort and an immediate: committed transactions are safe,
> > uncommitteds are lost, and no data is lost.

>
> There is a functional difference, and that is whether recovery has to
> take place.  If a very large transaction was in process you may have
> issues.  If someone has taken tablespaces off and online you may have
> issues.  If you intend to take a cold backup you may have issues,
> perhaps in the future at the worst possible time.  I haven't thought
> it through, but I suspect temp tablespaces could cause issues.  I have
> vague memories of distributed issues.  You are setting yourself up for
> problems if you don't take any of this into account, and doing
> willy-nilly shutdown abort flips over from being an innocent quirk to
> a nasty habit.
>

> >
> > What exactly do you have to quarrel about with any of that?
> >
> > >You've never
> > > seen a situation where instances are repeatedly aborted while
> > > recovering?
> >
> > You're just getting silly now. I've seen users delete the controlfiles
and
> > then say 'how do I do that 'backup to trace' command?'. If people are
> > idiots, they are idiots. I'm not talking about people. I'm talking about
> > the safety, or otherwise, of shutdown abort. Try not to throw red
herrings
> > about.

>
> Apparently, you've never seen the situation.  It's not that silly.
> Besides that, once you are advising people to use shutdown abort, you
> are talking about people.
>
> So what is the difference between yanking the plug and shutdown abort?
>

> >
> > >You've never seen recovery documented incorrectly by
> > > Oracle? You've never seen Oracle ask for logs that don't exist?
> >
> > Joel, I realise that you are on something of a crusade at the moment to
> > make me look a fool, and demonstrate to the Universe the paucity of my
> > practical experience (as you imagine it), but really: tell me the last
> > time you saw an INSTANCE recovery ask for a single log.

>
> No, I'm not targetting you specifically, sorry you feel that way, I
> can perhaps understand why you do.  This is usenet, it is carved in
> stone for the Universe.
>
> Haven't seen an instance recovery ask for a log, but I've seen some
> amazingly bizzare behavior trying to bring up instances that were not
> shut down nicely.  Both human and database.
>

> >
> > > You've never heard of support telling people to use files that
> > > documentation says are not needed for recovery? You've never seen
> > > people restore files they are _not_ supposed to restore?
> >
> > Give up Joel. You are making a fool of yourself. What has restoring
> > ANYTHING got to do with instance recoveries?

>
> Isn't SMON just automatically doing a subset of restoration?
>

> >
> > Nada.
> >
> > Now tell me in calmer tones why shutdown aborts are so dangerous.

>
> SMON can have, um, "personal problems."  Dblinked databases may get
> hung jobs.  Admins may get used to doing it and blow it when they have
> to do something like rename the db.  Deferring an unknown number of
> long running operations for the next startup (imagine a latch
> problem!).  ORA-376, server reboot after abort, or RBS problems.
> Before shutdown triggers.  A bunch more things I've seen that are
> probably old-version/platform specific, like leaving shared memory
> areas used up (unix and windows!) and PMON getting confused and wild
> processes using up the cpu.  Oracle support might not be able to help
> you.  Possible OPS or snapshot strangeness.
>

Joel,

I don't want to get contentious here, but it seems to me that according to fundamental principles, one must trust shutdown abort, followed by startup.

If not, surely the conclusion must be that Oracle cannot recover from instance failure? And of course it always can, and always does.

Yes, I've been there with replication failure - across the Atlantic - and sometimes a wait (or a few judicious kill sessions to hurry it up a bit) is/are needed.

Regards,
Paul Received on Wed Mar 05 2003 - 16:52:51 CST