Startup and shutdown Methods in Oracle

When startup command is issued, Oracle looks for the parameter file in following order.

1. Spfile<SID>.ora

2. init<SID>.ora

Oracle look for the parameter file in $ORACLE_HOME/dbs

We can startup the database instance with following options.

1. startup

2. startup nomount

3. Startup mount

startup pfile=’/location/filename’

4. startup upgrade

5. startup mount restrict

1. Startup:-

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started.

ü  It reads the controlfile. –Instance mounted.

ü  Physically checks the datafiles and redolog files.

ü  Checks the SCN number of the datafiles and controlfiles.

ü  It is used in normal situations.

ü  If the SCNs in the control files don’t match some of the SCNs in the data files headers—the background process (smon) will automatically perform an instance recovery before opening the database.

2. Startup mount:--

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started.

ü  It reads the controlfile. –Instance mounted.

ü  Can be used when:-- Recovery is needed for the entire database, Flashback is to be performed, any feature like archive log is to be enabled or disabled.

3. startup nomount:--

ü  It will read the parameter file.

ü  It will start the background processes and allocate memory (SGA). – Instance started

ü  Can be used when:-- Creating database and creating controlfile.

4. startup upgrade:--

ü  It will do 1 to 5 and

ü   Give the option of upgrading database.

ü  Users cannot connect to the database.

ü  Can be used to upgrade the database dictionary

ü  Database must be shutdown and restarted for users to connect.

5. startup mount restrict:--

ü  It will read 1, 2 and 3.

ü  It will not read 4 and 5.

ü  Used when the database is dropped.

Changing the status:--

Alter database mount;

This command can be used when database is in nomount state, this will read the controlfiles and mount the database.

Alter database open;

This command can be used when database is mounted.

This will read the datafiles and redolog file and will check if the datafiles are consistent. Allows the users to connect to the database,

Alter database close;

This command can be used when database open. This will release the datafiles and redolog files.

User connections will not be allowed. Status will change to mount.

Alter database dismount;

This command can be used when database is at mount state. This will release the control file and change the status to nomount.

The database can be shutdown with the following options:--

Shutdown:--

No new user connections can be made to the database once the command is issued. Oracle waits for all users to exit their sessions before shutting down the database. No instance recovery is needed when you restart database, it will be consistent when it’s shut down in this way. Oracle closes the data files and terminates the background processes. Oracle’s SGA is deallocated.

Shutdown transactional:--

No new user connections are permitted once the command is issued. Existing users can’t start a new transaction and will be disconnected. If a user has a transaction in progress, oracle will wait until the transaction is completed before disconnecting the user. After all existing transactions are completed. Oracle shuts down the instance and deallocates memory. Oracle writes all redo log buffers and data block buffers to disk. No instance recovery is needed because the database is consistent.

BEGIN
  DBMS_LOCK.sleep(seconds => 60.01);
END;
/

Shutdown immediate:--

No new user connections are allowed once the command is issued. Oracle immediately disconnects all users. Oracle terminates all currently executing transactions. For all transactions terminated midway, oracle will perform a rollback so that database ends up consistent. Sometimes oracle may be busy rolling back the transactions it just terminated. Oracle terminates the background processes and deallocates memory. No instance recovery is needed upon starting up the database because it is consistent when shut down.

Shutdown  abort:--

No new connections are permitted once the command is issued. Existing sessions are terminated, regardless of whether they have an active transaction or not. Oracle doesn’t roll back the terminated transactions. Oracle doesn’t write the redo log buffers and data buffers to disk. Oracle terminates the background processes, de allocates memory immediately, and shutdown. Upon restarting, oracle will perform an automatic instance recovery, because the database isn’t guaranteed to be consistent when shut down.