Database States

Remember that there are, in essence, two different entities: the Oracle Instance and the database.  The instance is used to access and manipulate the database data and storage structures.

An instance can be Started up which builds the SGA in memory and starts the background processes.  The instances can also be SHUT DOWN which closes the database, stops the instance and removes the memory structures.  The database itself may be open, closed or mounted.

Database Start-up

It can be observed as below that the database can be altered in the upward direction (up the stairs) but not back down them. 

NOMOUNT	Starts up the instance without mounting the database.  Activities are limited to things like creating  a database.  The database is inaccessible with no dictionary available for password checking.
MOUNT	Starts up the instance and mounts the database.  It allows restricted use of the database for “DBA” tasks such as file management and database structuring.  There is no normal user access possible
OPEN	It starts the instance, mounts and then opens the database.  Other file management activities available such as Manipulating log files, taking datafiles offline, backing up control files.  Normal users processing is allowed.
FORCE	Shuts down an instance before starting it up in the specified mode and is the same as performing SHUTDOWN ABORT followed by a start-up.
RECOVER	This is an OPEN option which forces automatic recovery procedures on start-up.
RESTRICT	This is an OPEN option which limits access to the database to users who have been granted both CREATE SESSION AND RESTRICTED SESSION system privileges.  It is useful for doing tasks like database exports while there is no activity on the database. SQL>STARTUP OPEN RESTRICT Can be removed while open by doing the following command: SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;

The above options can be combined at the SQL prompt to create the state you wish the database to start.

SQL>STARTUP [FORCE] [NOMOUNT | MOUNT | OPEN [open_options]][oracle_sid_name][PFILE=parameter file];

Examples:

SQL>STARTUP NOMOUNT

SQL>ALTER DATABASE MOUINT;

SQL>ALTER DATABASE OPEN;

Database Shutdown

The SHUTDOWN command closes the database, dismounts the database and then shuts down the instance.  There are several SHUTDOWN options that can be employed.

1        SQL>SHUTDOWN NORMAL

o     The database will only SHUTDOWN when all users have logged off and all work is committed (or rolled back). 

o     No new connections are allowed.

o     This option is difficult to use in man environments since the users are applications and all the applications have to be shutdown before the database would shutdown.

2        SQL>SHUTDOWN IMMEDIATE

o     Disconnects all users and performs rollback on all uncommitted data by using PMON

o     Terminates all current SQL statements

3        SQL>SHUTDOWN ABORT

o     Shuts down without tidying up; akin to system failure and requires system recovery on start-up

o     No rollback of uncommitted transactions