Re: re SHUTDOWN ABORT -- was RE: Debate on rc commands Solaris and

...kind of a side story on the virtues of ALTER SYSTEM CHECKPOINT...

About 6-7 years ago, I was teaching Oracle Education's "Backup and Recovery Workshop", which was a 3 day class. The first 2 days are blabber/lecture and the last day is all lab. In that last day, students spend the morning designing and building databases for fault-tolerance and in the late-morning/afternoon the instructor pokes holes in them. The idea is that the instructor sits down at the student's console, plays for 2-3 minutes, then lets the student find out what's wrong and try to fix it. Often it turns into a long afternoon, but we kept it light...

My favorite ploy was to write the user's ".profile" file right smack dab into the middle of a datafile somewhere, after making sure that there were some valid extents at that location. I did so and then stood up, to let the student take over...

...he sat right down and entered ALTER SYSTEM CHECKPOINT. Surprised, I asked him why and he just smiled and said, "Oh, I like to type that in, first thing, any time I get nervous".

He never found the corruption I had caused. I searched for it and couldn't find it either. My only theory was that the checkpoint caused the DBWR to overwrite what I had corrupted, fixing the problem...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, February 02, 2003 6:08 PM

> You're absolutely correct - I have neglected the
> acknowledge many of the various places I've seen
> proposing the 'alter system checkpoint / shutdown
> abort' model, including your web site.
>
> Apologies.
>
> Cheers
> Connor
>
> --- Jeremiah Wilton <jwilton_at_speakeasy.net> wrote: >
> Alter system checkpoint... You don't say...
> >
> > Hey, this is the first time this thread has
> > concluded without the
> > usual "you guys better watch out b/c yer gonna break
> > your database!"
> > post.
> >
> > I'd say this universal support for ABORT over
> > IMMEDIATE represents a
> > dramatic change in the prevailing DBA attitude over,
> > say, two years
> > ago.
> >
> > How do you suppose that happened?
> >
> > :-)
> >
> > The only dissenter was Dan. Dan, what's the
> > difference between a
> > kernel transaction and a regular transaction? Are
> > you talking about
> > the O/S kernel or Oracle? Can you explain in more
> > detail what the
> > kernel transaction does to make Oracle unrecoverable
> > after ABORT?
> >
> > I'm still mulling over that 'alter system
> > checkpoint.' Sounds
> > familiar.
> >
> > --
> > Jeremiah Wilton
> > http://www.speakeasy.net/~jwilton
> >
> > On Sat, 1 Feb 2003, [iso-8859-1] Connor McDonald
> > wrote:
> >
> > > Agreed. All that matters is the redo logs. If
> > Oracle
> > > had named the shutdown options more accurately,
> > that
> > > is
> > >
> > > shutdown abort => shutdown fast
> > > shutdown immediate => shutdown hopefully
> > > shutdown transactional => shutdown when hell
> > freezes
> > > over
> > > shutdown normal => shutdown never
> > >
> > > then I'm pretty sure I know what everyone would be
> > > using...An 'alter system checkpoint' just before
> > the
> > > abort also helps startup times.
> > >
> > > --- Hemant K Chitale <hkchital_at_singnet.com.sg>
> > wrote:
> > > >
> > > > Comparing the shutdown scenarios with what would
> > > > happen if someone
> > > > were to "pull the plug" on the server is the
> > best
> > > > way to explain the behaviour
> > > > of Oracle.
> > > > What if
> > > > a. Someone switched-off the server [there's a
> > > > power failure and the UPS
> > > > doesn't kick in] ?
> > > > b. The server crashes with a Unix Panic
> > > > well, the Oracle instance dies and yet, [as long
> > as
> > > > the disks are still good,
> > > > even if you have to do an "fsck"] there's
> > Instance
> > > > Recovery which goes through
> > > > successfully.
> > > > So what's the problem with SHUTDOWN ABORT ? Its
> > > > about the same thing,
> > > > but not as bad.
> > > >
> > > > I consistently use SHUTDOWN ABORT, STARTUP
> > RESTRICT,
> > > > SHUTDOWN NORMAL
> > > > on Production, Mission-Critical databases. I've
> > > > never had a problem with a
> > > > SHUTDOWN ABORT.
> > > > Even a clone of an ABORTed instance can work if
> > the
> > > > online redo logs and
> > > > current
> > > > control-file are also cloned !
> > > >
> > > > I have seen SHUTDOWN IMMEDIATE hang very many
> > times
> > > > [particularly when
> > > > DBMS_JOB jobs are running or the un-intelligent
> > > > Intelligent Agent is
> > > > connected].
> > > >
> > > > At 05:29 PM 31-01-03 -0800, you wrote:
> > > > >Dan,
> > > > >
> > > > >If you look at Rama Velpuri's most famous
> > treatise
> > > > on B & R - namely
> > > > >'Oracle8 Backup & Recovery Handbook' (Oracle
> > > > Press), you will see the path
> > > > >that the various shutdown options take. [Page
> > 43,
> > > > Chapter 2: The Oracle
> > > > >Architecture and Configuration]. I belive this
> > is
> > > > the closest that we could
> > > > >come to a published look under the covers. Some
> > one
> > > > mentioned a switch
> > > > >logfile prior to the shutdown abort. In any
> > case, a
> > > > shutdown abort would not
> > > > >be as bad as someone pulling the plug on the
> > juice
> > > > (as in Cleaning lady: 'I
> > > > >need to plug in my hoover... aaah - here's a
> > socket
> > > > I can use). The instance
> > > > >goes down not-so-gracefully, but the disk is
> > still
> > > > safe as long as the
> > > > >server stays up.
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: Fink, Dan [mailto:Dan.Fink_at_mdx.com]
> > > > > >
> > > > > > I know I am going to catch major grief for
> > this,
> > > > but I avoid
> > > > > > shutdown abort
> > > > > > when possible. Shutdown immediate can take
> > some
> > > > time, as it will do
> > > > > > transaction rollback in serial (where
> > recovery
> > > > at startup can do it in
> > > > > > parallel and as needed). The main reason I
> > avoid
> > > > it is that
> > > > > > there are known
> > > > > > bugs that will cause the database to be
> > > > unrecoverable when a
> > > > > > shutdown abort
> > > > > > is done while a kernel transaction is being
> > > > performed.
> > > > > > Granted, I have not
> > > > > > personally encountered this, and the chances
> > of
> > > > encountering
> > > > > > it are slight,
> > > > > > but why take the risk?
> > > > > >
> > > > > > <Ducking and running for cover>
> > > > > > Dan Fink
> > > > > >
> > > > > > -----Original Message-----
> > > > > > Sent: Thursday, January 30, 2003 6:15 AM
> > > > > > To: Multiple recipients of list ORACLE-L
> > > > > >
> > > > > >
> > > > > >
> > > > > > I'll echo that sentiment.
> > > > > >
> > > > > > 'shutdown abort', 'startup restrict' was a
> > > > regular part of my
> > > > > > shutdown scripts beginning in 1994 with
> > 7.0.16,
> > > > as
> > > > > > 'shutdown immediate' wasn't all that
> > reliable,
> > > > even in situations
> > > > > > where it should have worked.
> > > > > >
> > > > > > Jared
> > > > > >
> > > > > > On Wednesday 29 January 2003 15:53, John
> > > > Kanagaraj wrote:
> > > > > > > Rao,
> > > > > > >
> > > > > > > And where did you read that 'shutdown
> > abort'
> > > > is not
> > > > > > recommended? This is
> > > > > > > another myth that has been busted a while
> > ago.
> > > > A shutdown
> > > > > > abort followed
> > > > > > by
> > > > > > > a startup restrict and a normal shutdown
> > is
> > > > the way to go
> > > > > > when dealing
> > > > > > with
> > > > > > > rogue sessions that open a connection and
> > > > never shutdown.
> > > > > > In such cases, a
> > > > > > > shutdown immediate will _never_ return
> > > > (certainly not
> > > > > > within your 5 to 10
> > > > > > > minutes). I have been using this method
> > for
> > > > more than 8 years now -
> > > > > > > starting at 7.0.16 fyi. The trick in this
> > case
> > > > is to script
> > > > > > it into the rc
> > > > > > > commands.
> > > > > > >
> > > > > > > John Kanagaraj
> > > > > > > Oracle Applications DBA
> > > > > > > DBSoft Inc
> > > > > > > (W): 408-970-7002
> > > > > > >
> > > > > > > I don't know what the future holds for me,
> > but
> > > > I do know
> > > > > > who holds my
> > > > > > > future!
> > > > > > >
> > > > > > > ** The opinions and statements above are
> > > > entirely my own
> > > > > > and not those of
> > > > > > > my employer or clients **
> > > > > > >
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > Sent: Wednesday, January 29, 2003 11:49 AM
> > > > > > > To: Multiple recipients of list ORACLE-L
> > > > > > >
> > > > > > >
> > > > > > > Paula,
> > > > > > >
> > > > > > > Shutdown abort is not recommended as the
> > file
> > > > checkpointing
> > > > > > is not done
> > > > > > > during shutdown abort. If you need to
> > perform
> > > > shutdown
> > > > > > abort, then, it is
> > > > > > > preferred to bring up the db with startup
> > > > restrict (so that
> > > > > > the users
> > > > > > > wouldn't connect) and then, cleanly
> > shutdown
> > > > the db and
> > > > > > bring it up again.
> > > > > > >
> > > > > > > Tell to your sys admins. that shutdown
> > > > immediate would take
> > > > > > some time
> > > > > > > (about 5 to 10 minutes) depending on the
> > > > activity on your
> > > > > > db. They would
> > > > > > > have to wait for that much time before
> > calling
> > > > a DBA during
> > > > > > system boots.
> > > > > > >
> > > > > > > Rao
> > > > > > >
> > > > > > > -----Original Message-----
> > > > > > > Sent: Wednesday, January 29, 2003 11:30 AM
> > > > > > > To: Multiple recipients of list ORACLE-L
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > System Administrator says he doesn't trust
> > > > that the rc
> > > > > > commands will stop
> > > > > > > if the database doesn't want to shutdown
> > and
> > > > even if it
> > > > > > does would want to
> > > > > > > shutdown with scripts beforehand so that a
> > DBA
> > > > could
> > > > > > connect and resolve
> > > > > > > the issue. Other DBA says this is all
> > wrong
> > > > and rc commands should
> > > > > > include
> > > > > > > shutdown immediate of database. In the
> > past I
> > > > had setup 2
> > > > > > processes in
> > > > > > the
> > > > > > > system scripts for the sys admin -
> > shutdown
> > > > immediate -
> > > > > > wait .... shutdown
> > > > > > > abort - on a read-only DSS system which of
> > > > course allows
> > > > > > some room for
> > > > > > this
> > > > > > > type of activity. I kind of would want to
> > > > know if a
> > > > > > database was going to
> > > > > > > be shutdown with an abort esp. in OLTP
> > system
> > > > and do it myself.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > - any ideas
> > > > > > --
> > > > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.net
> > > > > > --
> > > > > > Author: Jared Still
> > > > > > INET: jkstill_at_cybcon.com
> > > > > >
> > > > > > Fat City Network Services -- 858-538-5051
> > > > http://www.fatcity.com
> > > > > > San Diego, California -- Mailing list
> > and
> > > > web hosting services
> > > > > >
> > > >
> > >
> >
> ---------------------------------------------------------------------
> > > > > > 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).
> > > > > > --
> > > > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.net
> > > > > > --
> > > > > > Author: Fink, Dan
> > > > > > INET: Dan.Fink_at_mdx.com
> > > > > >
> > > > > > Fat City Network Services -- 858-538-5051
> > > > http://www.fatcity.com
> > > > > > San Diego, California -- Mailing list
> > and
> > > > web hosting services
> > > > > >
> > > >
> > >
> >
> ---------------------------------------------------------------------
> > > > > > 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).
> > > > > >
> > > > >--
> > > > >Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.net
> > > > >--
> > > > >Author: John Kanagaraj
> > > > > INET: john.kanagaraj_at_hds.com
> > > > >
> > > > >Fat City Network Services -- 858-538-5051
> > > > http://www.fatcity.com
> > > > >San Diego, California -- Mailing list
> > and
> > > > web hosting services
> > > >
> > >
> >
> >---------------------------------------------------------------------
> > > > >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).
> > > >
> > > > Hemant K Chitale
> > > > My web site page is :
> > http://hkchital.tripod.com
> > > >
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.net
> > > > --
> > > > Author: Hemant K Chitale
> > > > INET: hkchital_at_singnet.com.sg
> > > >
> > > > Fat City Network Services -- 858-538-5051
> > > > http://www.fatcity.com
> > > > San Diego, California -- Mailing list and
> > web
> > > > hosting services
> > > >
> > >
> >
> ---------------------------------------------------------------------
> > > > 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).
> > > >
> > >
> > > =====
> > > Connor McDonald
> > > web: http://www.oracledba.co.uk
> > > web: http://www.oaktable.net
> > > email: connor_mcdonald_at_yahoo.com
> > >
> > > "GIVE a man a fish and he will eat for a day. But
> > TEACH him how to fish, and...he will sit in a boat
> > and drink beer all day"
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Everything you'll ever need on one web page
> > > from News and Sport to Email and Music Charts
> > > http://uk.my.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > > --
> > > Author: =?iso-8859-1?q?Connor=20McDonald?=
> > > INET: hamcdc_at_yahoo.co.uk
> > >
> > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > San Diego, California -- Mailing list and
> > web hosting services
> > >
> >
> ---------------------------------------------------------------------
> > > 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).
> > >
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Jeremiah Wilton
> > INET: jwilton_at_speakeasy.net
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > 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).
> >
>
> =====
> Connor McDonald
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).