Oracle Database startup and shutdown procedure

Oracle Database startup and shutdown procedure

by

For a DBA, starting up and shutting down of oracle database is a routine and basic operation. Sometimes Linux administrator or programmer may end-up doing some basic DBA operations on development database. So, it is important for non-DBAs to understand some basic database administration activities.

Oracle Database startup and shutdown procedure

Oracle Database startup and shutdown procedure : In this article, let we will discuss about Oracle Database startup and shutdown procedure.

How to start Oracle Database :

  1. Startup procedures.
  2. Connect to oracle sysdba.
  3. Start Oracle Database.

1. Startup Procedures :

We can startup Oracle Database in 3 states/modes :

startup nomount : In this state when the control file, online redo log files, and the database files are closed and are not accessible. The Oracle instance is available. We startup the database in this state to perform operations like, creating a database, recreating controlfile etc.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             285212672 bytes
Database Buffers          461373440 bytes
Redo Buffers                3678208 bytes
SQL>

startup mount : This is the next phase through which the database passes. In this stage, the control file is opened and the existence of all the database files and online redo log files is verified. We startup the database in this state to perform operations like, Backup, Recovery of the system or undo datafile, Change the database to archive log mode etc.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             285212672 bytes
Database Buffers          461373440 bytes
Redo Buffers                3678208 bytes
Database mounted.
SQL>
SQL> select name, database_role, open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
ORCL      PRIMARY          MOUNTED

SQL>

open mode : During open mode, the datafiles and the online redo log files are opened and are ready to use. Oracle doesn’t allow you to open the database if any of the datafile or online redo log file is missing or is corrupted.

SQL> startup
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             285212672 bytes
Database Buffers          461373440 bytes
Redo Buffers                3678208 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, database_role, open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
ORCL      PRIMARY          READ WRITE

SQL>

2. Connect to oracle as sysdba :

You can connect using either “/ as sysdba” or an oracle account that has DBA privilege. Make sure ORACLE_HOME, ORACLE_SID all environment are setup properly as shown below.

[oracle@node-1 ~]$ . oraenv
ORACLE_SID = [ORCL] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@node-1 ~]$
[oracle@node-1 ~]$ env | grep ORA
ORACLE_UNQNAME=ORCL
ORACLE_SID=ORCL
ORACLE_BASE=/u01/app/oracle
ORA_INVENTORY=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@node-1 ~]$
[oracle@node-1 ~]$
[oracle@node-1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 9 17:55:24 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

3. Start Oracle Database :

Oracle will use this SPFILE during startup, if you don’t specify PFILE. Default Location of the pfile and spfile are $ORACLE_HOME/dbs.

SQL> startup
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             285212672 bytes
Database Buffers          461373440 bytes
Redo Buffers                3678208 bytes
Database mounted.
Database opened.
SQL>

When you need to startup Oracle Database with PFILE, then pass it as a parameter as shown below :

SQL> startup pfile='$ORACLE_HOME/dbs/initORCL.ora';
ORACLE instance started.

Total System Global Area  759165480 bytes
Fixed Size                  8901160 bytes
Variable Size             285212672 bytes
Database Buffers          461373440 bytes
Redo Buffers                3678208 bytes
Database mounted.
Database opened.
SQL>

How to shutdown Oracle Database :

Below mentioned 3 methods are available to shutdown the oracle database :

  1. Normal Shutdown
  2. Shutdown Immediate
  3. Shutdown Abort

Normal Shutdown : When normal shutdown is running, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. Oracle recommended that, use this option to shutdown the database under normal conditions.

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

Shutdown Immediate : During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

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

Shutdown Abort : During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.

Leave a Comment