Shutting down the database

Shutting down a database is a routine task for DBAs. There are several situations that might require the DBA to shutting down a database instance:

• To carry out a maintenance on the database or host server.
• To perform recovery.
• A last resort when database performance is poor and you have no idea what the root cause is.
• To take a cold backup the database

Shutting down

This goes through the same process for startup but this time in reverse order.

Close Database>Unmount Database>Terminate Instance

These concepts have been explained in the post starting up oracle database

Types of Shutdown

Normal> This an impractical method of shutdown except for the test database installed on your laptop. This is because the database waits for all active users to disconnect from the database, then the database is shutdown in normal manner. One advantage of this shutdown method is that no instance recovery will be done at next startup. This is done by running the command below;

SQL>shutdown

OR

SQL>shutdown normal

Immediate> This is the most common method and somewhat the safest method of shutting down the database. It is performed by running

SQL>shutdown immediate

In this method, all new connections to the database are prevented. Then all uncommitted transactions are rolled back then the database is closed. This what most DBAs call a clean shutdown. This method can be slow at times when there are multiple users connected to the database and there are long uncommitted transactions.

Speeding up Shutdown Immediate

These techniques can be used individually or run as a chain of steps before performing shutdown immediate.

Switch logfiles (applicable for databases running in archivelog mode only)

SQL>alter system switch logfile;

Perform a checkpoint to clear dirty buffers from the buffer cache. This also creates an SCN which could be useful for recovery

SQL>alter system checkpoint;

For UNIX systems you can kill all user sessions from the operating system. Though this might be a little unconventional and somewhat risky, it makes the process faster. Get process id of all database sessions using the script below;

ps –ef | grep LOCAL

In cases where there are multiple instances on the same server (this could also be an ASM instance), eliminate other processes that are not owned by the instance to be shutdown using the command below. Refer to screen shot for better understanding

ps –ef | grep ora<INSTANCENAME>

Killing Sessions from the OS

Then select each process and terminate using;

kill -9 processID

Note the process with elaborate connection description, always ensure they are not killed. You might have multiple occurrences of these for a production database.

After this is completed you can issue shutdown immediate from SQLPLUS, and in situations where you need to bring down the database as soon as possible you can issue the shutdown immediate before proceeding to kill the OS processes.

Abort> This is not considered to be a clean shutdown because the database is brought down instantaneously without doing any form of transaction rollback or commit. Prior to Oracle 11g this method was not recommended… but in Oracle 11g, Oracle guarantees a database will always come back up from a shutdown abort except there are other underlying issues with the server or database initialization parameters. 

SQL>shutdown abort

When you need to bounce a large database (i.e. shutdown and startup the database immediately); you can issue shutdown abort, startup the database, shutdown immediate, then startup. Also note that the database performs instance recovery after a shutdown abort.  Screenshot below displays a database bounce;

Pitfalls

Shutting down the wrong database instance>there might be several instances running on a single machine, so you need to be sure you are connected to the correct instance by running define on SQLPLUS or do

SQL>select name from v$database;

Shutting down without understanding of the underlying issues>in cases where an issue is reported, you should check the alert log for errors first before going ahead with the shutdown. You can also check the status of the database by running

SQL>select open_mode from v$database;

Server restart>ensure that the database is shutdown cleanly before a server restart is performed

Violating company policy>make sure you know what regulations apply for shutting down databases in your organization to avoid sanctions

Hope this helps!

Kehinde.