Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Shutting down a database before doing an upgrade or a patch can be done several ways.

shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown;

or

alter system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;

Of course there are other options as well. Which should be preferred and why?

share|improve this question
    
check this out for detailed explanation .......... chandu208.blogspot.com/2011/09/oracle-startup-shutdown.html –  user8094 Apr 12 '12 at 14:56
    
@chandu Thank you for the comment, I understand what the commands do, I was more looking for a best practice and an explanation as to why. –  Leigh Riffel Apr 12 '12 at 15:43

2 Answers 2

up vote 10 down vote accepted

The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.

There are 3 SQL*Plus shutdown commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:

  1. shutdown normal or just shutdown - waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open - it used to be the only shutdown mode that did not cancel running transactions
  2. shutdown transactional - disconnects sessions once currently running transactions complete, preventing new transactions from beginning
  3. shutdown immediate - disconnects all sessions immedately and rolls back interrupted transactions before shutting down

The fourth mode of shutdown is shutdown abort. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:

This mode is intended for emergency situations, such as when no other form of shutdown is successful.

All the examples you give perform a checkpoint as part of the shutdown [normal] or shutdown immediate so explicit checkpointing is presumably to reduce the time required for recovery

general advice:

  • do not use shutdown normal
  • use shutdown transactional for attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached)
  • use shutdown immediate for unattended shutdown or when you do not care about currently running transactions
  • do not use shutdown abort (plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.
share|improve this answer
    
Can you provide any more specifics on the disadvantages of shutdown abort? Playing the antagonist, if we can trust Oracle to recover correctly when the power is pulled, shouldn't we trust it during a shutdown abort, particularly if it is faster and we are going to immediately do a startup restrict and a shutdown immediate? In other words, are there facts we can see to back up Oracle's dire warning against shutdown abort? –  Leigh Riffel Apr 3 '12 at 18:50
    
@Leigh - The only specific danger I know of concerning shutdown abort relates to accidental backup of online logs but that is only in the case you do not subsequently do a clean shutdown. If you know what you are doing I think shutdown abort can be considered perfectly safe - and I'm not sure if Oracle's position counts as a "dire warning" ;-) –  Jack Douglas Apr 4 '12 at 16:40

I prefer the shutdown abort method because it is the fastest way to bring a database down. there are some operation that cannot be done after a shutdown abort, e.g

  • recreate the controlfile the database with create controlfile resetlogs (to rename the database, to rename teh logfiles of to rename datafiles)
  • change the dbid with the procedure from dbms_backup_restore (this was the only method in 8i to change the dbid)

in both cases the database was damaged and must be restored from a fullbackup.

since 9i the rename of the database or the change of the dbid can be done with the dbnewid utility. as far as I know the utility checks if the database was shutdown correctly. renaming datafiles, tempfiles and logfiles can be done by executing the appropriate sql statements without recreating the controlfile of course.

share|improve this answer
    
Thank you for you input. +1. –  Leigh Riffel Apr 13 '12 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.