Managing Oracle Instance

Oracle Database Startup Modes

1. NOMOUNT
2. MOUNT
3. OPEN
4. RESTRICT
5. FORCE

The first two( NOMOUNT and MOUNT) is known as maintenance mode. OPEN is general startup mode. Only DBAs can startup the instance in RESTRICT mode and access the database, other users cannot access the database during this mode.

On the other hand, FORCE means to restart the instance (SHUTDOWN ABORT + STARTUP OPEN).

Suppose we have an instance where the initialization parameter file is OK, but the control file is lost(corrupt). Then, take the database to NOMOUNT mode.

Solution: After the database is taken to the NOMOUNT mode, then restore the control file from the backup. (TECHNIQUE WILL BE EXPLAINED LATER).

This command will still generate the error: because the STARTUP is still running.

Next thing that might come in mind is to take the database to OPEN mode, by the following command.

 

This command will not work either, we cannot jump to two modes (see the above step-wise modes). The error message will hint that the database has not been mounted. So finally, use the combination of the commands:

If DBAs desire to open the instance for the DBAs only (while upgrade and maintenance), then the following command must be issued instead of OPEN:

This will not allow the normal users to access the database, for example HR cannot login into the system during this mode.

Oracle Database Shutdown Modes

1. NORMAL
2. TRANSACTIONAL 
3. IMMEDIATE
4. ABORT

The NORMAL shutdown mode waits for all the user who has already been active, or ideal,  must end their session, (might keep waiting for long period, waiting session is high).

The TRANSACTIONAL shutdown mode indicates forced logout for ideal session and SHUTDOWN is processed as soon as the running transaction is committed.

The IMMEDIATE shutdown mode is generally preferred, it is proper way for shutting down the database.

The ABORT shutdown mode aborts the running transaction, but need ROLLBACK for next startup. Therefore, sometimes the database might shutdown fast but will take long to startup. This is only preferred for mission critical situation.