Startup and Shutdown Process of Oracle Database

Startup   

There are various stages which the database undergoes before a database is opened for use.

Nomount State:

This is the state when the control file, online redo log files and the database files are closed and are not accessible. The oracle instance is available. Some of the v$ views (dynamic performance views) are available during this state.

A database may be brought to this state to perform operations like.

1. Creating database.
2. Recreating control file.
Ex: V$session, v$instance, v$database etc.

Mount state:

This is the next phase through which the database passes. During this stage, the control file is opened and the existence of all the database files and online redolog files is verified.

A database may be brought to this state to perform operations like

1. Backup
2. Recovery of the system or undo datafile
3. Change the database to archive log mode etc.
When you query v$database for the open_mode, we get the answer as mounted.    

Open:

The database is opened. During this stage, the datafiles and the online redo log files are opened and are ready to use. Oracle doesn’t allow you to open the database if any of the datafile or online redo log file is missing or is corrupted.

A database may be opened is read only mode as well as in read write mode. The status may be found by querying v$database dynamic performance view. The query for this is as below

Sql>select open_mode from v$database:

In the read only mode, the database may be queried but one cannot perform the dml operations.

Note: To startup or shutdown the database, you need to logon as “sys as sysdba” or with any user account having sysdba role assigned.

There are other options available like one can start the database in a restricted mode. This is used during maintenance activities, upgrades etc. Only those users having restricted session privilege can logon to the database.

Checkout Oracle DBA Interview Questions

Shutdown

Oracle has three shutdown modes namely normal, immediate and abort.

1. Shutdown normal: This is the default mode of shutting down the database. During this state, oracle server waits for all the users to disconnect.

2. Shutdown Transactional: Waits until all the transactions are completed and then shts down the database. During this state, no new connections are permitted.

3. Shutdown immediate: This option will disconnect all the sessions; roll back all the running transactions and shutdown the datababase. During the next startup no instance recovery is needed.

4. Shutdown abort: This option doesn’t rollback any transactions and simply brings down the database. In layman’s term, it just likes pulling the power plug of the television. Any subsequent database startup needs an instance recovery to be initiated by smon.

Any backup taken after shutting down the database in abort mode will not be consistent. It is recommended to use the first three methods to shutdown the database for a consistent backup.

Misconceptions about Shutdown abort:

From the information available, it might appear that using an abort option isn’t a good idea. But however, in case of large databases, it may take a while and hence the option of abort can be judiciously utilized as below

1. Shu abort
2. startup restrict
3. Shu immediate
4. Startup mount restrict
This will be a clean shutdown and will be much quicker than the conventional methods. However, experienced dba always prefer to wait rather than use the previous options.

Related Articles:

How To Create TableSpaces - Oracle DBA

How To Create A Oracle Database – DBA Tutorial

Undo Retention In DBA - Oralce DBA