3 different Oracle database Startup modes: Easy Guide

In this article, we will discuss the 3 different Oracle database startup modes.

Please note that to startup or shutdown the database, you need to logon as “sys as sysdba” or with any other user having sysdba role assigned.

Oracle database startup sequence :

1. Nomount: this is the first phase

Oracle opens and reads parameter file (spfile or pfile). Here oracle first checks for the spfile, if it is not present, it will go for the pfile/init file.An instance is created based on the value of memory parameters mentioned in the parameter file(spfile/pfile) and Background processes started.
In this state control file, online redo log files, and the database files are closed and inaccessible.
This stage is useful to create a database and recreate the control file.
Some of the v$ views(dynamic performance views) like v$instance,v$session are available during this state but Data dictionary views whose names start with DBA_, ALL_, and USER_ are not available.

We can start the database in the NOMOUNT state as below. Please note that the database must be in shutdown state:

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    STATUS
--------------- ---------------- ------------
              1 oradb            STARTED

SQL> select name,open_mode from v$database;
select name,open_mode from v$database
                           *
ERROR at line 1:
ORA-01507: database not mounted

Here we can see the instance has been started but if you query v$database for the open_mode, we get the error ORA-01507: database not mounted because the database is not mounted yet.

2. Mount: this is the second phase

Oracle opens and reads the control file. The existence of all the database files and online redo log files is verified.
In this state the control file is open but the online redo log files and the database files are still closed and inaccessible.
This stage is useful to enable or disable database archive log mode and flashback database. Also, we can perform Backup, Recovery of the system or undo datafile, recover a database that has crashed due to media failure, rename datafiles, add, drop, or rename redo log files.
All the v$ views(dynamic performance views) are available during this state but Data dictionary views whose names start with DBA_, ALL_, and USER_ are not available.

To mount a database from a started state (nomount state) issue the following command:

SQL> alter database mount;
Database altered.

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    STATUS
--------------- ---------------- ------------
              1 oradb            MOUNTED

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORADB     MOUNTED

Here we can see the instance has been mounted with the database. To mount a database directly from the shutdown state issue the following command:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

3. Open: this is the last phase

While moving from mount state to open state Oracle performs “SANITY CHECKING”. It means verify the physical existence of database files and redo log files, ensuring that the System Change Number (SCN) in the control files and datafiles are synchronized, and perform any necessary recovery operations.Oracle doesn’t allow you to open the database if any of the datafile or online redo log files are missing or corrupted.
In this state the control files, online redo log files and the database files are open and accessible.
The database completely opens, where end users connect and perform all transactions.
All the v$ views(dynamic performance views) and Data dictionary views are available in this phase.

To open a database from the mount state issue the following command:

SQL> alter database open;
Database altered.

SQL> select INSTANCE_NUMBER,INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    STATUS
--------------- ---------------- ------------
              1 oradb            OPEN

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORADB     READ WRITE

Here we can see the instance has been opened and the database is ready for any read-write operation. To open a database from a shutdown state(when the database is down) issue the Oracle database startup command. Here instance goes through a normal 3-step process (nomount, mount and open):

SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

A database may be opened in read-only mode also. In the read-only mode, the user can perform read-only(only select query) operations but cannot perform any Write/DML operations.

I hope you found this article helpful. In this post, I have covered everything you need to know about the different stages of Oracle database startup modes. Feel free to reach out if you have any questions.

If you want more info then you can go through the Oracle doc.