Oracle Database startup and shutdown Options

Database startup

Normally database startup and shutdown required sysdba privileges.

Database can be started using the below steps

Start SQL Plus without connecting to the database

Sqlplus /nolog

Connect to Oracle Database as SYSDBA:

sqlplus / as sysdba

SQL> startup

Database startup is staring up the instance including various modes

Nomunt the database

Mount the database

Open the database

Nomount Stage:

Nomount stage instance will be start

It will be read the all oracle parameter files like (spfile,pfile)

It will start the memory(SGA) area and background process.

Startup nomount stage mainly using for database creation,Cloning and DB recovery operations.

Example:

SQL>startup nomount

SQL> startup nomount pfile='/<path>/init<SID>.ora';

Mount Stage:

Mount stage is only allow for DBA activities does not allow the general access of database.

It will be checks the controlfiles specified under CONTROL_FILES parameter and open the mount stage.

Then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.

NOTE:

This stage is normally used for maintenance operations like renaming datafiles, enabling and disabling archiving options. Adding, dropping, or renaming redo log files is also done in mount stage. For performing full database recovery database is opened in mount stage.

Example:

SQL>startup mount

Open Stage:

Open stage can be done in unrestricted mode.

It’s access to the all users

Startup options:

Startup force

Startup Restrict

Startup open recover

Startup open read only

Startup open read write

Startup upgrade

Startup downgrade

Startup migrate

Alter database Options:

SQL> Alter database mount;

SQL>Alter database open;

SQL>Alter database read only;

SQL>Alter system disable restricted session;

Database shutdown

During a database shutdown we close the database and terminates the instance

Database shutdown process:

Close the databaseàClose the redologfile and datafiles

Dismount the databaseàUnmount the database and close the control files.

Shutdown the instanceàClose the SGA and background process.

Database shutdown options:

Shutdown

Shutdown immediate

Shutdown transactional

Shutdown abort

Shutdown:

Database waits for all connected users to disconnect before shutting down

It waits for the entire current transactions end.

Example:

SQL>shutdown

Shutdown immediate:

Database terminates any executing SQL statements and disconnects users.

Active transactions are terminated and uncommitted changes are rolled back.

Example:

SQL>shutdown immediate

Shutdown transactional:

Its waits for all current transactions to complete before shutting down

Example:

SQL>shutdown transactional

Shutdown abort:

This is fastest mode of shutdown option

It is close the database without any checkpoint

Example:

SQL>shutdown abort