Oracle DB Startup and Shutdown Process

Startup Process:

You need to login as SYS user to perform a start up or shutdown of an Oracle Database Instance. Only a SYS user who has SYSDBA role can startup and shutdown the database.  You can startup a database if it is shutdown. How do you know if your database is shutdown or not? When you try to connect to a shutdown database, it will connect to Idle Instance.

What happens when you startup the Oracle database? Three things are happening during startup:

1.  Start an Instance :  During Instance startup, it reads the server parameter file (SPFILE or PFILE) or initialization parameter to determine the values of initialization to allocate SCA and creates background process.
2.  Mount the Database: Reads the control file parameter
3. Open the Database: Connect the data files 

Example:

SQL> startup
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL>

Types of Start up process:

1. STARTUP;   
2. STARTUP NOMOUNT;
3. STARTUP MOUNT;
4. STARTUP OPEN;
5. STARTUP RESTRICTED: 

MOUNT/NO-MOUNT is used only for administrative task for DBA. Database users can't access the database during the startup.

RESTRICTED: The Database is mounted and opened but only available to those users who have restricted privilege granted.

Startup using pfile:

STARTUP PFILE=//u01/app/oracle/product/10.2.0/dbs/init.ora

By default Oracle uses SPFILE which is in binary format and can’t be modified. Don’t even try that.

Oracle Shutdown Process:
Oracle will ensure all the active users and sessions are disconnected before shutdown happens. 

What happens during shutdown?

1. Close the database
2. Dismount the database
3. Shutdown the instance

Example:

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Shutdown Process:

SHUTDOWN;  

Oracle will wait till all the users sessions are disconnected/completed.This type of shutdown is not commonly used. No new connections accepted.

SHUTDOWN IMMEDIATE; 

Frequently used shutdown command where all the connected users and their sessions are terminated immediately. Their transactions will be either rolled back if not committed. 

SHUTDOWN TRANSACTION ;

 Mainly used in a banking or financial industries which does not accept any new connections, The process will wait until all the transactions are completed (committed) then only the shutting down of database starts. This is very crucial for bank transaction as you can't afford the rollback of a transaction. 

SHUTDOWN ABORT; 

Used only during emergency situations when above processes fail. This will terminate all users and this does not rollback the transaction. This is like power failure.