Shutdown of Database in Oracle 11g

In continuation of my previous article here I will describe the shutdown process of a database. An Oracle database instance can be shutdown using the Enterprise Manager. The Database Control Page shutdown in Oracle offers the following modes:

  • NORMAL
  • TRANSACTIONAL
  • IMMEDIATE
  • ABORT

A5(1).png


Clean database.png

NORMAL MODE

  1. New connections are prohibited.
  2. Waits for session to disconnect.
  3. Redo and database buffers are written to disk
  4. Background processes are killed and SGA memory is unallocated.
  5. Database files are dismounted and the Oracle server is shutdown.
  6. Instance recovery is not required during startup.

TRANSACTIONAL MODE

  1. Client data loss is prevented.

  2. No new transaction is allowed.
  3. Transactions are allowed to finish.
  4. Connections are terminated when the inprogress transactions ends.
  5. Once all transactions are complete the database shuts down immediately.
  6. No instance recovery is required during startup.

IMMEDIATE MODE

  1. This option is mostly used. Uncommitted transactions are rolled back.
  2. Executing SQL queries are not completed.
  3. All user connections are terminated.
  4. Active transactions are rolled back
  5. The Database is dismounted and the instance is shutdown.
  6. Instance recovery is not required during startup.

Dirty database.png

ABORT MODE

  1. Abort mode is typically used when none of the preceding modes work. It requires database recovery before startup. 
  2. This is the fastest mode of shutdown.
  3. Executing the SQL statement are terminated immediately.
  4. Currently logged on users are disconnected.
  5. No rollback for uncommitted transactions.
  6. Redo and database buffer are not written to the disk.
  7. No database files are closed and instance is killed/terminated.
  8. Database is not dismounted.
  9. Instance recovery happens automatically during startup.

Syntax for SQL PLUS Database shutdown

  • SQL>shutdown

    Normal Mode
     
  • SQL>shutdown transactional

    Transactional mode.
     
  • SQL>shutdown immediate
     
    Immediate Mode
     
  • SQL>shutdown Abort.
     
    Abort Mode