Start and Shutdown the Oracle Instance

Startup the Oracle Database Instance
You have 3 stages of startup command for start the database in Oracle.
1. Nomount – In this stage you can create the control file. Consider as a recovery stage.
2. Mount – In this stage you start recover of the Database files . consider same as recover stage
3. Open – Database is open for all. All user can connect with username/password and access the data from the database through SQL language.
4. OPEN RESTRICT – It is restricted state only oracle DBA can connect in the stage and it needed for DBA maintance task performed by DBAs.

Check database in which stage open

Select open_mode from v$database;


1. Set the Environment varialbe of ORACLE_HOME, ORACLE_SID, PATH

SET ORACLE_HOME=E:\oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%PATH%
SET ORACLE_SID=ORCL


2. Login the SQLPLUS with OS or SYS user authentication

sqlplus / as sysdba

sqlplus sys/password as sysdba


3. Start the Database in Mount State.(only exper can use for recovery purpose)

SQL> Startup Nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 708838320 bytes
Database Buffers 352321536 bytes
Redo Buffers 5517312 bytes
SQL>

-- Mount database from nomount state
SQL> alter database mount;
Database altered.

--Check database is in which stage
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

-- Open database from mount state
SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE


4. Start the Database in Mount State( Only expert can use for recovery purpose)

SQL>Startup mount

-- Open database from mount state.
SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE


5. Start the database in normal read write mode.(used by all users having DBA privileges)

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 708838320 bytes
Database Buffers 352321536 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.


Shutdown the Oracle Database Instance

Four ways to shutdown the Oracle Database:
1. Normal : In Normal stage Oracle will wait for all active user to disconnect their session.
2. Transactional: In Transactional Stage Oracle will wait for all transaction to be completed then it disconnect user connection.
2. Immediate : In Immediate Stage Oracle Rollback on going transaction and disconnect the active session.
3. Abort: all user sessions terminated immediately. Uncomitted transactions will not be rolled back. (Not recommended)

1. Connect with SQLPLUS.

SQLPLUS sys as sysdba


2. Shutdown the database normal it will wait for user to be disconnect.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.


3. Shutdown the database in transactional. it will wait for active transaction to be completed.(Always try to use)

Shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Shutdown the database immediate. it will start rollback the user on going transaction and disconnect the active session.

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


5. Shutdown the database with abort option. (nor recommended but sometime upper commands hanged then use it)

SQL> shutdown abort
ORACLE instance shut down.