Managing Database Instances (part 2)
Shutting Down an Oracle Instance
Just as with starting the database, several options as well as a variety of interfaces are available for database shutdown:
- SHUTDOWN NORMAL
A normal shutdown is the default type of shutdown that Oracle performs if no shutdown options are provided. You need to be aware of the following performing a normal shutdown:
- No new Oracle connections are allowed from the time the SHUTDOWN NORMAL command is issued.
- The database will wait until all users are disconnected to proceed with the shutdown process.
Because Oracle waits until all users are disconnected before shutting down, you can find yourself
waiting indefinitely for a client who may be connected but is no longer doing any work or who may have left for the day.
This can require extra work, identifying which connections are still active and either notifying the users to disconnect or forcing the client disconnections by killing their sessions. This type of shutdown is also known as a clean shutdown because when you start Oracle again, no recovery is necessary.
2. SHUTDOWN TRANSACTIONAL
A transactional shutdown of the database is a bit more aggressive than a normal shutdown.
The characteristics of the transactional shutdown are as follows:
- No new Oracle connections are allowed from the time the SHUTDOWN TRANSACTIONAL command is issued.
- No new transactions are allowed to start from the time the SHUTDOWN TRANSACTIONAL command is issued.
- Once all active transactions on the database have completed, all client connections are disconnected.
A transactional shutdown does allow client processes to complete prior to the disconnection.
This can prevent a client from losing work and can be valuable especially if the database has long-running transactions that need to be completed prior to shutdown.
This type of shutdown is also a clean shutdown and does not require any recovery on a subsequent startup.
3. SHUTDOWN IMMEDIATE
The immediate shutdown method is the next most aggressive option.
An immediate shutdown is characterized as follows:
- No new Oracle connections are allowed from the time the SHUTDOWN IMMEDIATE command is issued.
- Any uncommitted transactions are rolled back. Therefore, a user in the middle of a transaction will lose all the uncommitted work.
- Oracle does not wait for clients to disconnect. Any unfinished transactions are rolled back, and their database connections are terminated.
This type of shutdown works well if you want to perform unattended or scripted shutdowns of the database and you need to ensure that the database will shut down without getting hung
up during the process by clients who are connected. Even though Oracle is forcing transactions to roll back and disconnecting users, an immediate shutdown is still a clean shutdown.
No recovery activity takes place when Oracle is subsequently restarted.
SHUTDOWN ABORT
A shutdown abort is the most aggressive type of shutdown and has the following characteristics:
- No new Oracle connections are allowed from the time the SHUTDOWN ABORT command is issued.
- Any SQL statements currently in progress are terminated, regardless of their state.
- Uncommitted work is not rolled back.
- Oracle disconnects all client connections immediately upon the issuance of the SHUTDOWN ABORT command.
Do not use SHUTDOWN ABORT regularly.
Use it only if the other options for database shutdown fail or if you are experiencing some type of database problem that is preventing Oracle from performing a clean shutdown.
This type of shutdown is not a clean shutdown and requires instance recovery when the database is subsequently started. Instance recovery is performed automatically when you do the startup—no manual intervention required. During instance recovery the uncommitted changes are rolled back from the database, and committed changes are written to the data files.
Oracle uses the redo log files and undo segments to construct the instance recovery information.
Use it only if the other options for database shutdown fail or if you are experiencing some type of database problem that is preventing Oracle from performing a clean shutdown.
This type of shutdown is not a clean shutdown and requires instance recovery when the database is subsequently started. Instance recovery is performed automatically when you do the startup—no manual intervention required. During instance recovery the uncommitted changes are rolled back from the database, and committed changes are written to the data files.
Oracle uses the redo log files and undo segments to construct the instance recovery information.
Shutting Down Oracle Using SQL*Plus :
You can use the command-line facility SQL*Plus to shut down the Oracle database. You
will need to connect to SQL*Plus as a user with the SYSOPER or SYSDBA privilege. Here is
the syntax of the shutdown options available to you:
SHUTDOWN [NORMAL|TRANSACTIONAL|IMMEDIATE|ABORT]
SQL> shutdown immediate;
