...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" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > > 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:[EMAIL PROTECTED]] > > > > > > > > > > > > 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: [EMAIL PROTECTED] > > > > > > > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > > > > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > "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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > "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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).