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.
In this article, let us review how to start and stop an oracle database.
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 Jan 18 11:11:28 2009 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.
- spfile$ORACLE_SID.ora
- spfile.ora
- 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:
- Normal Shutdown
- Shutdown Immediate
- 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 Sun Jan 18 11:11:33 2009 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to an idle instance. SQL> shutdown abort ORACLE instance shut down. SQL>
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Good article – simple and nice.
excellent note indeed
Good article. Very precise. Thanks!
very nice topcs;
Excellent topic
it’s work… thanks a lot
Good one.
hi, very easy to understand, thank you
good …
thanks very much
hi,
what mean this error ?
startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora’
Very useful. Quick and easy to understand. Thanks 🙂
nice and working excellent on me..
2009?? i hope i’m not too late learning oracle on linux 😀
thanks ja
Awesome and detail explanation
very useful topics in interviews
Can you please share PFILE & SPFILE paths.
Can the oracle instance be restarted using Crontab?
1. I want to restart one table in database. any table
2. How I log table.
Thanks
Thanks, excellent article
Everytime I write shutdown it shows
Ora-24324: service handle not initialized
Ora-24323: value not allowed
Ora-01090: shutdown in progress – connection is not permitted.
J-Salem, Same goes for me
very nice. easy to understand
Your articles are very easy understanding and pretty straight forward.
Suggestion from my end is that better to update the few articles depend on current stuff and advanced changes. Like, Now we have 4th type of shutting down the DB, which is “shut transactional”, if possible.