How to Start and Stop an Oracle Database

    Starting up and shutting down of Oracle Database is a basic routine operation. But any other programmer or sysadmin  might end-up doing some basic DBA operations on development database. So, it is important for non DBA to understand some basic database administration activities. So, let us see how to start and stop an Oracle database with simple examples.

How To Startup Oracle Database

1. Login to the system with oracle username

Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below. 
$ su - oracle

2. Connect to oracle sysdba

Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below. 
$ env | grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0
You can connect using either “/ as sysdba” or an oracle account that has DBA privilege. 
$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Dec 23 09:11:22 2016
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL

3. Start Oracle Database

The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE. Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.
  1. spfile$ORACLE_SID.ora
  2. spfile.ora
  3. init$ORACLE_SID.ora
Type “startup” at the SQL command prompt to startup the database as shown below. 
SQL startup
ORACLE instance started.

Total System Global Area  812529152 bytes
Fixed Size                  2264280 bytes
Variable Size             960781800 bytes
Database Buffers           54654432 bytes
Redo Buffers                3498640 bytes
Database mounted.
Database opened.
SQL
If you want to startup Oracle with PFILE, pass it as a parameter as shown below. 
SQL STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

How To Shutdown Oracle Database

Following three methods are available to shutdown the oracle database:
  1. Normal Shutdown
  2. Shutdown Immediate
  3. Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions. 
SQL shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL

2. 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

3. 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. 
$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Dec 23 09:11:22 2016
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to an idle instance.

SQL shutdown abort
ORACLE instance shut down.
SQL

Copyright 2020. All Rights Reserved