Oracle database startup stages and commands

Database startup stages and commands in Oracle

Simply starting an Oracle database with startup command is not too complex. Actually it is just running a few commands and your database will come up happily and be ready for normal operation.

For example to start your Oracle database you can just login and execute startup command as follows.

$sqlplus / as sysdba

SQL>startup

This command will start your database. But in the background there are a few stages that are hidden when you use the above command. Understanding these stages will help you to get a better insight view of Oracle startup process.

Oracle startup process consists of three stages

Stage 1: NOMOUNT

Stage 2: MOUNT

Stage 3: OPEN


Stage 1: NOMOUNT

This is the first stage in the startup process.  You can start Oracle database in nomount mode using the command

SQL>startup nomount;

When you execute the above command, an Oracle instance is started. When instance starts it will read the initialisation file (commonly known as parameter file) called init.ora file/ initDBNAME.ora( in case spfileinitDBNAME.ora). From this parameter file the instance will know about the size of SGA, PGA, database buffer size and other configurable parameters. The instance will also start the Oracle background process such as (PMON, SMON, LGWR etc). This stage also opens the alert log and the trace files.


Stage 2: MOUNT

The next stage after NOMOUNT is called MOUNT. You can manually start an Oracle database in MOUNT stage using the command

SQL>startup mount;

Or when database is already in nomount stage then you can change the stage by running the command

SQL>alter database mount;

When database goes into mount stage, it will read the control files specified in the parameter file. Remember the parameter file was read in the first stage (nomount). The control files contain the information about the physical structure of the database. So the control file will have the names and locations of all the datafiles and online redo log files. At this stage these datafiles and log files are not opened.

Some database administration operations can only be performed when the Oracle database is MOUNT stage. For example Oracle full database recovery can be done only when the database is in mount stage. If you want to rename a datafile you may need to take the database to mount stage unless the tablespace of the datafile is already offline.


Stage 3: OPEN

The final stage in the Oracle startup process. When the database is open then only normal database operations can takes place. Which means users and applications can login and start reading/writing data.

Running the command below will start the Oracle database and put into OPEN stage.

SQL>startup

And if the database is already in MOUNT stage then you can open the database using the command

SQL> alter database open;

When database is open it will open the datafiles and redo log files. If any of these files are missing or corrupted then Oracle will not open successfully and will return error.


Demo:

-- Verify running database pmon process

$ ps -ef|grep pmon
oracle   21502     1  0 Jun03 ?        00:06:20 ora_pmon_PROD
oracle   28932 28900  0 20:41 pts/0    00:00:00 grep pmon

$ export ORACLE_SID=PROD

-- Close / Down the database

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 7 20:41:57 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

-- Open the database

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size             452987080 bytes
Database Buffers         1677721600 bytes
Redo Buffers                4947968 bytes
SQL>
SQL> alter database mount;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD       MOUNTED

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD       READ WRITE

SQL>