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
- New connections are prohibited.
- Waits for session to disconnect.
- Redo and database buffers are written to disk
- Background processes are killed and SGA memory is unallocated.
- Database files are dismounted and the Oracle server is shutdown.
- Instance recovery is not required during startup.
TRANSACTIONAL MODE
- Client data loss is prevented.
- No new transaction is allowed.
- Transactions are allowed to finish.
- Connections are terminated when the inprogress transactions ends.
- Once all transactions are complete the database shuts down immediately.
- No instance recovery is required during startup.
IMMEDIATE MODE
- This option is mostly used. Uncommitted transactions are rolled back.
- Executing SQL queries are not completed.
- All user connections are terminated.
- Active transactions are rolled back
- The Database is dismounted and the instance is shutdown.
- Instance recovery is not required during startup.
![Dirty database.png]()
ABORT MODE
- Abort mode is typically used when none of the preceding modes work. It requires database recovery before startup.
- This is the fastest mode of shutdown.
- Executing the SQL statement are terminated immediately.
- Currently logged on users are disconnected.
- No rollback for uncommitted transactions.
- Redo and database buffer are not written to the disk.
- No database files are closed and instance is killed/terminated.
- Database is not dismounted.
- 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