Re: SQL Statement Shutdown

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

jg

--
@home.com is bogus.  "If startup hangs and the database went down
abnormally (crash or shutdown abort)and there are no errors in the
alert log, this could be normal instance recovery, an internal matter
(if errors),system problems (see system logs), inadequate cleanup at
shutdown (try shutdown abort / startup), unsupported software/ os
combination or (less likely) data corruption. Wait 30 minutes then
call support if it doesn't open."