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.

Killing Sessions in Oracle Database

There's no running away from this... you have to kill sessions at some point while managing Oracle databases. A database session is essentially a user process spawned as a result of a user connections to the database i.e. a session is  created when a user connects to the database. When a session is terminated/killed, active transactions from the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

Terminating the wrong session can be destructive if the wrong session is killed, you could end up killing a wrong session that was not intended to be killed or removing a background process which could lead to instance crash/abort (I've made this error in the past - It was not a funny incident).

Several reasons that can warrant this drastic action (as the name suggests) are highlighted below.

1.You might want to perform an administrative operation and need to terminate all non-administrative user sessions

2. Wait events can be resolved by killing the blocking session which holding up the resource(s)

3. User sessions can be killed to enable batch processes run faster

4. You can use the technique to remove long running queries either to rewrite the query or run at another time when the system is less busy

Identifying sessions to be killed

You can identify session to be killed by querying V$SESSION and V$PROCESS. Database sessions can be marked for kill by selecting based on USERNAME, MACHINE, PROGRAM, OS_USER or STATUS. Sessions STATUS can be any of the following states;

• ACTIVE - Session currently executing SQL
• INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
• KILLED - Session marked to be killed
• CACHED - Session temporarily cached for use by Oracle*XA
• SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.

To get more familiar with V$SESSION and V$PROCESS run a describe on each table.

Identifying sessions for kill in a RAC database has a little twist to it. The GV$SESSION and GV$PROCESS dynamic performace views can be queried from any instance to get database wide session data. Whiile instance specific session data can be gotten by querying our good old V$SESSION & V$PROCESS from each instance.

Killing the sessions

To kill sessions at the database level, Oracle provides the alter system kill statement. Captions below demonstrate how to kill a user session;

1. Connect to the database with any named user

SQL> conn kehinde

Enter password:

Connected.

SQL> select sysdate from dual;

SYSDATE

-----------

16-OCT-2015

SQL>

2. In another session login as sysdba to kill the user session created above

SQL> select sid,serial# from v$session where username='KEHINDE';

     SID   SERIAL#

-------- ---------

    2273       145

           

SQL> alter system kill session '2273,145';

System altered.

SQL>

3. Go back to user session and try to run any script

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-00028: your session has been killed

                                                                  

For RAC databases, ensure that the kill script for instance a run instance a session as you might end up terminating a session you did not intend to terminate if the script is run on another instance.

To kill multiple sessions, say all inactive sessions on the server you can use script below to genrate the kill script and run as a batch;

Script

SELECT NVL(s.username, '(oracle)') AS username,

       s.osuser,

       'alter system kill session '''||s.sid||','||s.serial#||'''',

       'kill -9 '||p.spid,

       s.sid,

       s.serial#,

       p.spid,

       s.lockwait,

       s.status,

       s.module,

       s.machine,

       s.program,

       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM   v$session s, v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY s.osuser;

For unix systems you can also kill sessions from the operating system level by using the command kill -9 spid, this is also selected as part of the script above. Alternatively you can run ps -ef | grep LOCAL from the OS to identify process ids to be killed. Note that you need to be very careful while selecting processes to be killed with this command. I will show the pitfalls and how to avoid them in the next post which will be on "Startup & Shutdown - Bouncing the Database". 

Hope this helps!

Kehinde.