Startup and Shutdown Oracle Database

There are different stages and types of Startup and Shutdown Oracle Database. To perform these operations user account must have administration privileges. Generally sys account is used to perform these operations.

Connect to database using below method, suppose database name is techon_db

$set ORACLE_SID=techon_db

$sqlplus /nolog

SQL>connect / as sysdba

STARTUP

Different Stages of startup

NOMOUNT:  During NOMOUNT stage, memory gets allocated as per pfile/spfile memory parameters and mandatory background processes (for 11g- PMON,SMON, DBWn, LGWR, CKPT,RECO, MMON and MMNL) gets started

MOUNT:  During Mount stage, Oracle takes control file. Only sysdba will be able perform operations during these stage

OPEN: During Open stage, header of data files, control files and redo log files gets checked by Oracle for synchronization. Once it is done, database gets opened and ready for all users.

Different types of database startup

§  STARTUP

This will be perform all three operations NOMOUNT + MOUNT + OPEN with single command “startup”. Database will be available for all users

§  STARTUP NOMOUNT 

This command opens database in nomount mode and allocates memory and starts mandatory background processes.

§  STARTUP MOUNT

Startup mount will perform NOMOUNT + MOUNT in single step. It will allocate memory, starts mandatory background processes and acquires control file

§  STARTUP RESTRICT

Database will undergo with NOMOUNT, MOUNT and OPEN stage but it will be available only for SYSDBA or users having RESTRICTED_SESSION privilege

§  STARTUP FORCE

This command will perform SHUTDOWN ABORT + STARTUP. First it will perform shutdown abort and then start the database in open state. This type of startup requires instance recovery and it will be done by SMON during startup

SHUTDOWN

Different types of shutdown

§  SHUTDOWN | NORMAL

Default shutdown mode

It waits for all users to disconnect before completing the shutdown.

New connections or transaction cannot be made

Checkpoint process occurs and Oracle close and dismount the database before shutting down the instance.

Instance recovery will not be required for next startup

§  SHUTDOWN TRANSACTIONAL

New connections or transaction cannot be made

Wait to complete current transaction from users and then close its session to proceed with shutdown

Checkpoint process occurs and Oracle close and dismount the database before shutting down the instance.

Instance recovery will not be required for next startup

§  SHUTDOWN IMMEDIATE

New connection or transaction cannot be made

Oracle will forcefully close all database sessions and proceed with shutdown

Checkpoint process occurs and Oracle closes and dismounts the database before shutting down the instance.

Instance recovery will not be required for next startup

§  SHUTDOWN ABORT

Forcefully terminate user’s SQL statement and close all users currently connected

No checkpoint will occur and instance will be terminated directly.

Since checkpoint has not happened, the next startup will require instance recovery, which is automatically done by SMON

Recommended only if other options do not work

         Comment below if you need additional information