18 January 2007

The Four Options of the SHUTDOWN

One thing that's always good to know is that there are actually 4 different options to the Oracle SHUTDOWN command. They are:
  • NORMAL
  • TRANSACTIONAL
  • IMMEDIATE
  • ABORT
Now allow me to cover the differences.
NORMAL
This is the default option for the SHUTDOWN command. Once a SHUTDOWN NORMAL is issued, no new user connections will be allowed. However, all existing connections are allowed to continue for as long as they like, and the SHUTDOWN will simply wait for them. So not until the last user (voluntarily) logs off will the database actually shut down. It's pretty much a useless option.

TRANSACTIONAL
As with NORMAL, a SHUTDOWN TRANSACTIONAL command will tell the database to not allow any new user connections. Also, any existing sections that are not currently in a transaction are terminated. Sessions that are in a transaction (eg: INSERT, UPDATE, DELETE or PL/SQL block) are kindly allowed to finish that transaction and will be terminated immediately after. As before, when all user sessions are gone, the database will shut down.

IMMEDIATE
Like before, no new connections allowed. However now ALL sessions are terminated. Any active transactions are rolled back. After all the sessions terminated and rollbacks complete (which could take a bit of time), then the database shuts down. This is usually the way you'll want to shutdown your database.

ABORT
This one differs from the other 3 in that it results in an inconsistent shutdown. SHUTDOWN ABORT is basically the equivalent of a power cut, or someone killing the SMON process. The Oracle instance terminates immediately. No effort is made to write out dirty blocks to disk or redo logs. Upon the following startup, SMON will need to perform instance recovery to bring the database (controlfiles and datafiles) to a consistent state. This type of shutdown should only be performed in the direst of situations.
As I mentioned, a SHUTDOWN ABORT leaves the database in an inconsistent state. Usually, with any of the other three options, Oracle will perform a consistent, or "clean" shutdown. This entails:
  1. Incomplete transactions get rolled back
  2. A checkpoint is issued that tells the database writer (DBWn) to write updated blocks from the db buffer cache to the datafiles on disk
  3. LGWR (the logwriter) flushes any change vectors from the redo log buffer to the online redo logs.
  4. Then the file headers are updated (so that the SCN in the controlfiles and datafiles match), and the filehandles are closed.
So let's just keep things nice and easy, and use SHUTDOWN IMMEDIATE, alright?

No comments:

Post a Comment