Managing Oracle database instance

Starting Up an Oracle Database Instance

A normal database operation means that an instance is started and database is mounted and open.

Oracle database instance has 3 phase of start up

SQL> startup nomount 

• An instance is typically started only in nomount mode during database creation, during re creation of control file, or during certain backup and recovery scenarios.
• Starting an instance includes the following tasks:
• Searching ORACLE_HOME/dbs for a parameter file of a particular name the sequence.
• search for spfile<SID>.ora
• If spfile<SID>.ora not found, search for spfile.ora
• If spfile.ora is not found, search for init<SID>.ora
• Allocating the SGA
• Starting the background processes
• Opening the alert_<SID>.log file and trace files.

This command starts the instance and the database is not mounted.

SQL> startup mount

• Associating a database with a previously started instance.
• Locating and opening all control files specified in the parameter file.
• Reading control files to obtain the names and statuses of the data files and online redo log files.
• However, no checks are performed to verify the existence of the data files and online redo log files at this time.

We can open database in mount mode to perform certain maintenance operations.

• Renaming datafiles
• Enabling and disabling online redo log file archive operations
• Performing full database recovery

SQL> startup

Opening database includes following tasks:

• Opening the data files
• Opening the online redo log files

If any of above file is not present when you attempt to open the database, it will returns an error.

Sequence of command in case we open it in mount or no mount state.

SQL> startup nomount
SQL> alter database mount
SQL> alter database open

Using srvctl starting database

$srvctl start database -d orcl -o mount

During this final stage, the oracle server verifies that all data files and online redo log files can be opened, and checks the consistency of the database. If requires SMON background process will perform Instance Recovery.

Note:when the database is enabled with ORACLE RESTART, the srvctl utility can be used to start oracle database instance, the advantage is it can also start all required dependent resources such as asm instance, asm disk group and listener.  srvctl utility is located in both the $ORACLE_HOME/bin directory for grid infrastructure and for the oracle database software.

SHUTTING DOWN ORACLE DATABASE INSTANCE

There is four modes of shutting down oracle database instace

SQL> shutdown abort;

• Current SQL statements being processed by the Oracle server are immediately terminated.
• The Oracle server does not wait for users who are currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or mounted.
• The next startup requires instance recovery, which occurs automatically.

SQL> shutdown Immediate;

• Current SQL statements being processed by the oracle database are not completed.
• the oracle server does not wait for the users who are currently connected to the database to disconnect.
• The oracle server rolls back active transactions and disconnects all connected users.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

NOTE: IMMEDIATE is the default shutdown mode when using OEM.

SQL> shutdown transactional;

• It will allow existing transactions to finish,(It will wait till user commit the transaction or rollback the pending transaction) but not starting new transactions.
• No client can start a new transaction on this particular instance.
• When all transactions have been completed, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SQL> shutdown;(normal)

• It will waits for all sessions to disconnect.
• No new connections can be made.
• Database and redo buffers are written to disk.
• Background processes are terminated and the SGA is removed from memory.
• The Oracle server closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

$srvctl stop database -d orcl -o abort

If we think about time that is taken to perform shutdown abort is fastest and normal is the slowest.



Thank You,