RE: Query Database while shutdown in progress

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <andrew.kerber@xxxxxxxxx>, <robertgfreeman@xxxxxxxxx>
  • Date: Wed, 27 Jun 2007 15:42:08 -0400

I am in the shutdown immediate camp.   Note dbshut/dbstart has abort...
but then again I think that was for sudden server shutdowns  (who would
do that)?

 

However, (Not from Oracle), I have heard oracle has gotten a lot better
with abort - as in not as dangerous or fearsome.   I just like
synchronizing all the file headers etc.... Does killing the session stop
the rollback?   If not, then monitoring the alert log for the offending
user is nice but doesn't save very many minutes.

 

Ever see a session take awhile to kill?.... rolling back, no?

 

The moral might be --  if you are desperate --   Most of the time,  the
shutdown abort will shutdown now (immediately), and the fast rollback,
roll forward on startup in the background will still allow you to work
whereas no one can work during an hour shutdown immediate rollback.

 

However, the disclaimer is to get some solid background on whether you
would implement such a procedure as Standard.   Most databases don't
have to shutdown very often anymore... some maintenance is about it.

 

 

 

 

Joel Patterson 
Database Administrator 
joel.patterson@xxxxxxxxxxx 
x72546 
904  727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Andrew Kerber
Sent: Wednesday, June 27, 2007 3:13 PM
To: robertgfreeman@xxxxxxxxx
Cc: oracle-l
Subject: Re: Query Database while shutdown in progress

 


The last time I checked, admittedly in the 8.1.7 world, oracle stated
that you could end up with file corruption on a shutdown abort (note
that is could, not would, I never had it happen),  If they have changed
this and now say that a shutdown abort is as safe as a shutdown
immediate, I have not heard it.  Does anyone have a statement from
oracle to that effect?  

What happens if you have datafiles in backup mode and you try and do a
startup force?




On 6/27/07, Robert Freeman <robertgfreeman@xxxxxxxxx> wrote:

On 6/27/07, Andrew Kerber < andrew.kerber@xxxxxxxxx
<mailto:andrew.kerber@xxxxxxxxx> > wrote: 

        Sometime shutdown abort is necessary.  However, its not
recommended.  I would rather not risk having to recover a database w/o a
good reason.  If all that is needed is to monitor a shutdown immediate.
there are a lot of ways to handle that.  If  nothing else, you can tail
the alert log, and see the id thats keep the instance form shutting
down, then kill that session.  That would be much safer than an abnormal
termination of the instance.

 

I agree with Jared, whats up with this? Yes, a shutdown abort assures
that you will be doing crash/instance recovery, but so what? If you are
truly concerned about that, do a checkpoint before you do the shutdown
abort (assuming that does not take forever). In what cases will a
shutdown require media recovery? I know of none unless you are planning
on rm'ing a bunch of datafiles afterwards.

 

One of the beauties of Oracle, and a fact that seems little known, is
the rule with regards to checkpoints and redo logs. Everyone knows that
a redo log can not be reused in ARCHIVELOG mode until the archived redo
log is created. What seems less known is the rule that an online redo
log can not be reused until all dirty blocks associated with that online
redo log have been checkpointed and written to the datafiles. Thus,
shutdown abort should NEVER lead to a media recovery.

 

I will say, as a point of caution, that back in the 7.x days, there was
a bug that I discovered that would cause you to have to do media
recovery after a shutdown abort. So if you are running 7.x then be
careful about a shutdown abort.

 

RF

Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints
Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)
Oracle Database 11g New Features Now Available for Pre-sales on
Amazon.com!
Sig V1.1 

         




-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.' 

Other related posts: