[Oracle] Startup & Shutdown Oracle Instance

Posted by cikul | Posted in Oracle, SQL | Posted on 17-07-2012-05-2008

0

For DBA,  starting up and shutting down database is a routine operation. How to start and stop Oracle database (windows & Unix base)

Starting Up Oracle Database

To starting up Oracle Database,  you must connect using SQLPLUS /as sysdba or an oracle account that has DBA privilege.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Tue jul 17 15:08:58 2012
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected
SQL>

or you can insert /as sysdba when you prompted for user-name

$ sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on Tue jul 17 15:08:58 2012
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba
Connected
SQL>

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  152116164 bytes
Fixed Size                   453572 bytes
Variable Size             125829120 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/path/to/init.ora

Shutdown Oracle Database

There are three methods available to shutdown oracle database :

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.

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.


SQL> shutdown abort
ORACLE instance shut down.

source

Write a comment

*