ORACLE DATABASE STARTUP PROCESS

An oracle database can be started in various modes. Each mode is used by the DBA's to perform some specific operation in the database.

To start the database there are 3 modes.

                                NOMOUNT => MOUNT ==> OPEN

Below are the details about each mode.

STARTUP NOMOUNT MODE:   (Instance startup phase)

Mounting a database into nomount includes the following tasks:

• Reading initialization parameter file

First spfileSID.ora

If not found then, spfile.ora

If not found then, initsid.ora

/*specifying the PFILE parameter with STARTUP overrides the default behaviour. */

• Allocating SGA

• Starting the background Process

• Opening the alertSID.log files and trace files

The dba uses the no mount mode to

•    Create an oracle database

•   Re-create a controlfile for an existing database

Command to start Database in nomount mode:

[chow@oraserver ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 22 08:39:14 2008

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

Connected to an idle instance.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
STARTED

STARTUP MOUNT MODE: (Maintenance phase)

Mounting a database into mount includes the following tasks:

• Locating and opening the control file specified in the parameter file.

• Reading the control file to obtain the name, status and destination of DATA FILES AND ONLINE REDO LOG FILES

• To perform special maintenance operations

• Renaming data files (Data files for an offline tablespace can be renamed when the database is open)

• Enabling and disabling online redo log file archiving, flashback options.

• Performing full Database Recovery

The dba uses the mount mode to

• The mount state is used to recover a database that has crashed due to media failure.

• The mount state is also used by the dba to enable archiver process.

• It is also used by the dba to create a standby controlfile for configuring a standby database using dataguard.

Command to start Database in mount mode:

SQL> ALTER DATABASE MOUNT;  /* To mount a database from a started state (nomount state)*/

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED

(Or)

We can directly go from a shut database to a mount database by typing below command.

SQL> SHUTDOWN
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT  /* To mount a database from a shutdown mode */
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
MOUNTED 

         

STARTUP OPEN MODE: (Available for user access)

the last stage of the startup process is opening the database. When the database is started in the open mode, all valid users can connect to the database and perform database operations. Prior to this stage, the general users can’t connect to the database at all. You can bring the database into the open mode by issuing the ALTER DATABASE command as follows:

To open the database, the Oracle server first opens all the data files and the online redo log files, and verify that the database is consistent. If the database isn’t consistent—for example, if the SCNs in the control files don’t match some of the SCNs in the data file headers—the background process will automatically perform an instance recovery before opening the database. If media recovery rather than instance recovery is needed, Oracle will signal that a database recovery is called for and won’t open the database until you perform the recovery.

Opening a database includes the following tasks:

• Open online data files

• Open online redo log files

Command to start Database in mount mode:

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
OPEN

(Or)

We can directly go from a shut database to an open database by typing below command.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 79691776 bytes
Fixed Size 1217812 bytes
Variable Size 62917356 bytes
Database Buffers 12582912 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Apart from above mode there are other modes also as stated below.

STARTUP FORCE MODE:  (shut abort+startup)

Startup force include the following tasks.

• Shut down database with abort option

• Start up database with normal option.

The dba uses the STARTUP FORCE mode.

• You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.

• You experience problems when starting an instance.

Command to start Database in STARTUP FORCE mode.

     SQL> startup force

This command will perform a shut abort and then executes a startup. This command must be avoided as it calls for crash recovery.

STARTUP RESTRICT MODE: (restricted mode)

If we start an oracle database in restricted mode then only those users who have restricted session privilege will be able to connect to the database.

Startup restrict include the following tasks.

• It open database in restricted mode where only restricted user can access.

The dba uses the STARTUP RESTRICT mode.

• Perform an export or import of database data

• Perform a data load (with SQL*Loader)

• Temporarily prevent typical users from using data

• During certain migration and upgrade operations

Command to start Database in STARTUP RESTRICT mode.

SQL> startup restrict

ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

After database open in restricted mode the alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered

SQL> alter system disable restricted session;
system altered

CONCLUSION:

Ø  STARTUP NOMOUNT MODE- The nomount state is used by the dba to create a new oracle database.

Ø  STARTUP MOUNT MODE- The mount state is used by the dba to perform recovery

Ø  STARTUP OPEN MODE- The open state is used by the dba and programmers to work with the         database in a normal way.

Ø  STARTUP FORCE MODE- The force state is used by the dba in worst-case scenarios when you are not able to shutdown the database using normal/immediate options

Ø  STARTUP RESTRICT MODE- Start an oracle database in restricted mode then only those users who have restricted session privilege can connect.

IMPORTANT NOTE:

Whenever we are shutting a database in a normal way then before shutting the oracle database, oracle will write a common scn to the file headers of the datafiles and to the controlfile.

But incase of a shut abort oracle does not get the chance to write the common scn thus when we restart the database then oracle will find that the scn does not match for the data files and the control file. Thus oracle will call smon to perform 'crash recovery' or 'instance recovery'.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!