What Is The Fastest Way To Cleanly Shutdown An Oracle Database?

Oracle Server - Enterprise Edition - Version 9.0.1.0 to 11.2.0.2 [Release 9.0.1 to 11.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.0 to 11.2.0.1.0

***Checked for relevance on 01-Nov-2011***


Goal



What is the fastest way to shutdown an Oracle database?


Fix

SHUTDOWN ABORT is the fastest way to shutdown an Oracle database.  However, this type of shutdown leaves the database in a inconsistent state (non-rolled back) any backups taken at this point would require recovery at the next startup (See NOTE 1 below)

*** NOTE *** SHUTDOWN ABORT is not recommended for databases prior to 8.1.6 as the chances of corrupting the database are much higher in such older versions.


1) Determine how much rollback (in bytes) is needed for a clean shutdown using the following query:


select sum(used_ublk) * from v$transaction;


2) SHUTDOWN ABORT

This will terminate all processes (CLIENT and BACKGROUND) as quickly as possible with no transaction rollback.



A SHUTDOWN IMMEDIATE will cause SMON to try to terminate all client processes (SIGKILL) but there are many cases where SMON cannot do so in a timely manner hence the reason for the SHUTDOWN ABORT. (See NOTE 2 below)

3) From the results in #1 and your experience with your database, determine if you can wait for a clean shutdown (shutdown immediate).  If you cannot wait for a shutdown immediate, then skip the remaining steps and at the next startup SMON will rollback the transactions.

4) STARTUP RESTRICT

5) Watch the rollback (number of blocks) with the following query:
 

select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';


6) When the rollback has completed (which can be immediately after startup), issue SHUTDOWN IMMEDIATE.




Once this competes ... the database will be cleanly shutdown

=============================================================

NOTE 1:

** Oracle� Database Backup and Recovery User's Guide 11g Release 1 (11.1)

7 RMAN Backup Concepts

Consistent Backups

You can use the BACKUP command to make consistent and inconsistent backups of the database. A consistent backup occurs when the database is in a consistent state. A database is in a consistent state after being shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. A consistent shutdown guarantees that all redo has been applied to the datafiles. If you mount the database and make a backup at this point, then you can restore the database backup later and open it without performing media recovery.

Inconsistent Backups

Any database backup that is not consistent is an inconsistent backup. A backup made when the database is open is inconsistent, as is a backup made after an instance failure or SHUTDOWN ABORT command. When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs.

Note:
RMAN does not permit you to make inconsistent backups when the database is in NOARCHIVELOG mode. If you employ user-managed backup techniques for a NOARCHIVELOG database, then you must not make inconsistent backups of this database.

As long as the database runs in ARCHIVELOG mode, and you back up the archived redo logs and datafiles, inconsistent backups can be the foundation for a sound backup and recovery strategy. Inconsistent backups offer superior availability because you do not have to shut down the database to make backups that fully protect the database.

=============================================================

NOTE 2:

There is a condition due to a internal only (not published) bug in SQLPLUS that prevents SQLPLUS sessions from being able to be terminated by SMON ...
    A truss / pdump etc of the client process will show that the SQLPLUS session hung waiting for WAITPID

=============================================================

NOTE 3:

** Oracle� Database Administrator's Guide 11g Release 1 (11.1)

Shutting Down a Database

Shutdown Timeout and Abort

Shutdown modes that wait for users to disconnect or for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown command aborts with the following message: ORA-01013: user requested cancel of current operation. This message is also displayed if you interrupt the shutdown process, for example by pressing CTRL-C. Oracle recommends that you do not attempt to interrupt an instance shutdown. Instead, allow the shutdown process to complete, and then restart the instance.

After ORA-01013 occurs, you must consider the instance to be in an unpredictable state. You must therefore continue the shutdown process by resubmitting a SHUTDOWN command. If subsequent SHUTDOWN commands continue to fail, you must submit a SHUTDOWN ABORT command to bring down the instance. You can then restart the instance.