Startup & Shutdown Concept

Instance

ü  Memory (SGA) + Background Process

Instance (Database) Startup stages

ü  NOMOUNT -> MOUNT -> OPEN

Stage1: STARTUP NOMOUNT

Step1: Reads Parameter File (Location: $ORACLE_HOME/dbs)

Step2: Allocates SGA

Step3: Start the Background Process

ORACLE_HOME

ü  Location where the oracle software is installed

Parameter File types

ü  PFILE

ü  SPFILE (Server Parameter File)

Note:

ü  Oracle gives the first preference to the SPFILE

ü  If SPFILE is not available then oracle gives the second preference to the PFILE

Stage2: MOUNT

Step1: It reads the Control file & Identifies Database Name, Location of Data files & Redo log files

Step2: It connects the Instance (Logical) to the Database (Physical)

Stage3: OPEN

Step1: It physically checks the presence of Data & Redo Log Files

Step2: It compares the SCN in Data, Control & Redo Log Files

Instance (Database) Shutdown stages

ü  Database (Closed) -> Database (Dismounted) -> Instance (shutdown)

SHUTDOWN Options

ü  NORMAL (Recommended)

ü  TRANSACTIONAL

ü  IMMEDIATE

ü  ABORT

ü  FORCE

SHUTDOWN NORMAL or SHUTDOWN

Step1: No new user connections are allowed

Step2: All dirty buffers will be written to data files

Step3: All redo log buffers will be written to redo log files

Step4: It synchronizes the SCN in data, control and Redo log files

Step5: All background process will be terminated

Step6: Database is dismounted

Step7: Release the SGA

Step8: Instance is shutdown

Note:

When we startup the database next time no recovery is required (Since SCN matches in all the files)

SHUTDOWN TRANSACTIONAL

Step1: No new user connections are allowed

Step2: It waits until the current transaction is completed

Step3: All dirty buffers will be written to data files

Step4: All redo log buffers will be written to redo log files

Step5: It synchronizes the SCN in data, control and Redo log files

Step6: All background process will be terminated

Step7: Database is dismounted

Step8: Release the SGA

Step9: Instance is shutdown

Note:

When we startup the database next time no recovery is required (Since SCN matches in all the files)

SHUTDOWN IMMEDIATE

Step1: No new user connections are allowed

Step2: No new transactions are allowed

Step3: It rollbacks uncommitted transactions & does not wait for the transaction to be completed

Step4: All dirty buffers will be written to data files

Step5: All redo log buffers will be written to redo log files

Step6: It synchronizes the SCN in data, control and Redo log files

Step7: All background process will be terminated

Step8: Database is dismounted

Step9: Release the SGA

Step10: Instance is shutdown

Note:

When we startup the database next time no recovery is required (Since SCN matches in all the files)

SHUTDOWN ABORT

Step1: It immediately crashes the Instance

Step2: Dirty buffers are not written to data files

Step3: Redo log buffers are not written to redo log files

Step4: No synchronization of SCN in data, control and Redo log files

Note:

When we startup the database next time it requires recovery (Since SCN does not matches in all the files)

STARTUP FORCE = SHUT ABORT + STARTUP