Shutting Down Database

You may need to shut down a database for a number of reasons, for example, for some types of backup, for upgrades of software, and so on. You have several options for shutting down a running database. The option you choose has several implications for the time it takes to shut down the database and any potential need of database instance recovery upon a consequent starting up of the database. The following sections cover the four available shutdown command options for the Oracle9i database.

Shutdown Normal

When you issue the shutdown normal command to shut the database down, Oracle will wait for all users to disconnect from the database before shutting the database down. That is, if a user goes on vacation for a week after logging into a database and you subsequently issue a shutdown normal command, the database will have to keep running until the user returns. The normal mode is Oracle's default mode for shutting down the database. The command is issued as follows:

Sql> shutdown normal
OR
Sql> shutdown

The shutdown normal command involves the following:

• No new user connections can be made to the database.

• Oracle waits for all users to exit their sessions.

• No instance recovery is needed when you restart the database because Oracle will write all redo log buffers and data block buffers to disk before shutting down. Thus, the database will be consistent when it's shut down in this way.

• Oracle closes the data files and terminates the background processes. Oracle's SGA is deallocated.

Shutdown Transactional

If you don't want to wait for a long time for a user to log off, you can use the shutdown transactional command. Oracle will wait for all active transactions to complete before disconnecting all users from the database, and then it will shut down the database.

Sql> shutdown transactional

The shutdown transactional command involves the following:

• No new user connections are permitted.

• Existing users can't start a new transaction and will be disconnected.

• If a user has a transaction in progress, Oracle will wait until the transaction is completed before disconnecting the user.

• After all existing transactions are completed, Oracle shuts down the instance and deallocates memory. Oracle writes all redo log buffers and data block buffers to disk.

• No instance recovery is needed because the database is consistent.

Shutdown Immediate

Sometimes, a user may be running a very long transaction when you decide to shut down the database. Both of the previously discussed shutdown modes are worthless to you under such circumstances. Under the shutdown immediate mode, Oracle will neither wait indefinitely for users to log off nor wait for any transaction to complete. It simply rolls back all active transactions, disconnects all connected users, and shuts the database down. Here is the command:

Sql> shutdown immediate

The shutdown immediate operation involves the following:

• No new user connections are allowed.

• Oracle immediately disconnects all users.

• Oracle terminates all currently executing transactions.

• For all transactions terminated midway, Oracle will perform a rollback so the database ends up consistent. This rollback process is why the shutdown immediate operation is not always immediate. This is because Oracle is busy rolling back the transactions it just terminated. However, if there are no active transactions, the shutdown immediate command will shut down the database very quickly. Oracle terminates the background processes and deallocates memory.

• No instance recovery is needed upon starting up the database because it is consistent when shut down.

Shutdown Abort

The shutdown abort command is a very abrupt shutting down of the database. Currently running transactions are neither allowed to complete nor rolled back. The user connections are just disconnected.

Sql> shutdown abort

The shutdown abort command involves the following:

• No new connections are permitted.

• Existing sessions are terminated, regardless of whether they have an active transaction or not.

• Oracle doesn't roll back the terminated transactions.

• Oracle doesn't write the redo log buffers and data buffers to disk.

• Oracle terminates the background processes, deallocates memory immediately, and shuts down.

• Upon a restart, Oracle will perform an automatic instance recovery, because the database isn't guaranteed to be consistent when shut down.

When you shut down the database using the shutdown abort command, upon recovery the database has to perform instance recovery to make the database transactionally consistent because there may be uncommitted transactions that need to be rolled back. The critical thing to remember about the shutdown abort command is this: The database may be shut down in an inconsistent mode. That's the reason Oracle recommends that you always shut down the database in a consistent mode by using the shutdown or shutdown immediate command and not the shutdown abort command before backing it up. In most cases, you aren't required to explicitly use a recover command, because the database will perform the instance recovery on its own.