Shutting Down the Database

When you shut down the database, you can make operating system level (cold) offline backups of all physical structures. A shutdown allows static initialization parameters to take effect at the next startup.

To shut down the instance and its associated database, a user with SYSOPER or SYSDBA privilege uses the SHUTDOWN command with the following:

 SHUTDOWN [NORMAL| TRANSACTIONAL |IMMEDIATE | ABORT] 

NORMAL is the default if no option is specified.

SHUTDOWN NORMAL

During a SHUTDOWN NORMAL, the database buffer cache is written to the data files as the SHUTDOWN command forces a checkpoint, uncommitted changes are rolled back, and resources are rolled back. The database is in a consistent state when it is completely closed. When an instance is started after a SHUTDOWN NORMAL, no instance recovery is needed.

No new connections are allowed to the instance, but the Oracle Server waits for all the currently connected users to disconnect before completing the shutdown. SHUTDOWN NORMAL is the most polite way to shut down the database but can also be the least practical. In SHUTDOWN NORMAL, because users are not killed, shutdown could take a long time if user sessions are forced to disconnect after a given period of idle time. Often users will simply lock their computers and go home at the end of the day without logging off their application.

Redo buffers are written to disk during a SHUTDOWN NORMAL. This is important, because if something should happen during the shutdown or during the period during which the database is shut down, and the database cannot start in a consistent manner, the redo log files will allow it to start up and recover to the point where it was shut down.

Background processes are terminated, and the SGA is removed from the computer's memory as Oracle closes and dismounts the database and then shuts down the instance.

SHUTDOWN TRANSACTIONAL

SHUTDOWN TRANSACTIONAL prevents users from losing any work.

During a SHUTDOWN TRANSACTIONAL, the database buffer cache is written to the data files, resources are rolled back. The database is in a consistent state when it is completely closed. When an instance is started after a SHUTDOWN TRANSACTIONAL, no instance recovery is needed.

Users cannot start new transactions through any interface, but the session is disconnected as soon as the active user session completes the transaction that is in progress. After all transactions are finished, all users will be logged off, and the instance shuts down immediately. This makes SHUTDOWN TRANSACTIONAL more practical than SHUTDOWN NORMAL from the DBAs perspective and still allows for no lost work from the users' perspective.

A checkpoint is forced, making sure that the redo buffers are written to disk.

SHUTDOWN IMMEDIATE

During a SHUTDOWN IMMEDIATE, the database buffer cache is written to the data files, uncommitted changes are rolled back, and resources are rolled back. The database is in a consistent state when it is completely closed. When an instance is started after a SHUTDOWN IMMEDIATE, no instance recovery is needed.

Currently active SQL statements in process are not completed. This means that work could be lost from a user's perspective.

Oracle does not wait for any transaction to finish or for any user to disconnect from the database.

A checkpoint is forced, the active redo buffers are written out to the online redo logs, and active transactions are rolled back as active users are disconnected. The time that it takes for rollback to complete can be extensive, depending on how much work the active transactions in the database were doing.

Oracle dismounts the database, closes the database, and shuts down the instance.

SHUTDOWN ABORT

SHUTDOWN ABORT simulates either an instance failure or a startup force. When the instance and database shut down, no buffers are written to data files, nothing is rolled back, and no checkpoints are taken.

At the point when the instance is closed, the database is in an inconsistent state.

When the database restarts from a SHUTDOWN ABORT, redo logs are used to reapply changes that were currently active in the database at the abort point. Then undo segments are used to roll back all uncommitted changes. Only at this point are the resources released and user connections permitted to the reopened database.

SQL statements that are currently being processed are immediately terminated. Oracle doesn't wait for any transactions to finish or for users to disconnect. Redo information in the buffers are not written to disk. Database buffers are not written to disk.

The instance does not close files, and the database is not closed or dismounted. Instance recovery occurs automatically during the next startup.

Shutting Down and Starting Up Using OEM

From the running OEM interface, expand the database you want to access from the Database folder. Expand that database's Instance folder, and click on Configuration.

From the General tab, select the instance state that you want the database to be in and click Apply.

The requirements for allowing shutdown or startup through the OEM interface are that you be connected to OEM as a user with SYSDBA privilege.