Oracle Database Startup / Shutdown

March 16, 2013

oracle database

With the new job, surrounded by Oracle/Sun servers, I had to be doing some basic database operations though I’m not a DBA. Certainly, I’m not fan of Oracle DB, but the basic commands for startup / shutdown come handy when you’re confronted with. The below described procedure is a time saving one.

First you have to log in using the ‘oracle’ user. While on most systems this user is called ‘oracle’, nothing prevents to put it something else. So, to find the correct username associated with your Oracle instance find as which user the processes ‘pmon’, ‘smon’ or ‘ckpt’ are running.

ps -ef | grep -i pmon

Let’s assume here the user is ‘oracle’. Now, you’re good to log in.

su - oracle

Next step, you need to verify if the ORACLE_SID is set correctly.

echo $ORACLE_SID

If it isn’t set, you could export it. On most systems the ID is referenced in the instance name, which you must have got in your previous ‘ps’ output.

You can now connect as the Oracle ‘sysdba’ or some other user having DBA privilege.

sqlplus '/ as sysdba'

You’ll see the ‘SQL’ prompt. From here you can launch your startup or shutdown command.

When Oracle starts it looks for the SPFILE (server parameter file) in the following order :

spfile$ORACLE_SID.ora
spfile.ora
init$ORACLE_SID.ora

Alternatively, you can specify the location of the SPFILE using PFILE.

To start the database, you just have to type ‘startup’ at the SQL prompt.

SQL> startup

In order to specify the SPFILE during startup, you could do as follows (e.g. file path) :

SQL> startup pfile=/u01/app/oracle/product/10.2.0/dbs/init.ora

To shut down the database you can either do a normal shutdown or an immediate shutdown. In a normal shutdown, Oracle wait for active user sessions to end then proceeds with shutting down. During immediate shutdown, Oracle rolls back all active transactions and disconnects active users, then proceeds with shutting down.

Normal shutdown as follows:

SQL> shutdown

Immediate shutdown as follows:

SQL> shutdown immediate

In case the above two fail and you need to urgently shut down the database, you can do ‘shutdown abort’. All user sessions will be terminated instantly and uncommitted transactions won’t be rolled back.

SQL> shutdown abort

One last thing when stopping & starting Oracle DB servers. You may have your database started up but your application won’t connect until the listener is running. To verify the listener status do :

lsnrctl status

If it is not running, you can start it as :

lsnrctl start

If you need a specific listener to start you can specify it next to the start, i.e lsnrctl start [listener-name]. The comment ‘lsnrctl’ options are status, start, stop and reload.

The listener parameter file (listener.ora) is typically located under $ORACLE_HOME/network/admin. The listener log file is usually called log.xml. These information are also displayed in the listener status output.

The Oracle ‘tnsping’ utility determines whether the listener can be reached successfully.

If you can connect successfully from a client to a server (or a server to another server) using the TNSPING utility, then it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle service.

If it fails, then it displays a message describing the error that occurred. This enables you to see the network error that is occurring without the overhead of a database connection.

Use the command as follows :

tnsping net_service_name count

– net_service_name refers usually to the $ORACLE_SID
– count determines how many times the program attempts to reach the server.

The Oracle ‘trcroute’ utility enables administrators to discover the path or route a connection is taking from a client to a server. If TRCROUTE encounters a problem, then it returns an error stack to the client instead of a single error. These additional error messages make troubleshooting easier.

Use the command as follows :