database & instace
A database is defined as the actual data, index, redo, temp, and control files that exist on the files system.
The instance consists of the processes(PMON, SMON, DBWR, LGWR, and others) and the SGA(memory pool) that access and process data from the database files.
The instance is what accesses the database, and it is the instance that users connect to.
In ascending order, during startup the database instance goes through these states:
NOMOUNT
Read Parameter File
Allocate SGA
Start Background Processes
Only SGA and Background Porcesses Running
Used for CREATE DATABASE (Only SYS can access)
Specified by STARTUP NOMOUNT
MOUNT
Read Parameter File
Allocate SGA
Start Background Processes
Open and Read Control File
SGA and Background Processes Running and Control Files Open
Used for database maintenance and recovery operations(Only SYS can access)
OPEN
Read Parameter File
Allocate SGA
Start Background Processes
Open and Read Control File
Open All Database Files
SGA and Background Processes Running, Control Files Open, All Database Files Open
Default OPEN state for database and is accessible by users and applications
There is also an order for how a database instance is stopped. Ideally, this is what happens during a database shutdown:
New connections to the database are denied.
Existing transactions are either committed or rolled back with proper updates to online redo log files.
User sessions are terminated.
Database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
SHUTDOWN [NORMAL]
New connections to the database are denied.
Existing transactions continue normally until either they rollback or commit.
User log out normally on their own.
After the last user logs out, database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN or SHUTDOWN NORMAL command.
SHUTDOWN TRANSACTIONAL
New connections to the database are denied.
Existing transactions continue normally until either they roll back or commit.
After an existing transaction is completed, user sessions are terminated.
Database file headers are updated and files are closed.
SGA is shut down.
Background processes are termianted.
Specified by the SHUTDOWN TRANSACTIONAL command.
SHUTDOWN IMMEDIATE
New connections to the database are denied.
Existing transactions are roll back.
User sessions are terminated.
Database file headers are updated and files are closed.
SGA is shut down.
Background processes are terminated.
Specified by the SHUTDOWN IMMEDIATE command.
SHUTDOWN ABORT
New connections to the database are denied.
Existing transactions are not rolled back.
User sessions are terminated.
SGA is shut down.
Background processes are termianted.
Specified by the SHUTDOWN ABORT command.
Instance recovery is required on startup.