Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Killing Oracle User Processes


Oracle Tips by Burleson
 

There are a number of reasons to kill Oracle user processes. (Note: By “killing Oracle processes” I mean killing nonessential database processes.) These nonessential database processes usually consist of terminal sessions that are left connected after real work has been accomplished. These active sessions result in problems when the database has to be shut down for either backup or maintenance operations. As long as there is an active session, a normal-mode shutdown will hang. Coming in on Monday to discover that the database couldn’t shut down, and thus couldn’t be backed up, is a frustrating experience. Oracle has provided the immediate shutdown mode, but this isn’t always reliable and, in some situations, can result in an inconsistent backup. The abort shutdown option will shut down the database, but you then have to restart and perform a normal shutdown before any backup operations, or risk an inconsistent backup. Therefore, it is important for the DBA to know how to kill these processes before operations of this type are accomplished.

Methods of Murder

Other than the aforementioned abort option for the SHUTDOWN command, which after all is rather rude, what are the methods of killing these recalcitrant sessions? Essentially, you can issue an ALTER SYSTEM KILL SESSION or you can issue a manual process kill command such as the UNIX: kill -9 pid from the operating system side. You should do one or the other of these types of kill operations, but not both. If you kill both the Oracle process and the operating system process, it can result in database hang situations, which will force you to perform a shutdown abort.

Killing from the Oracle Side

The DBA can either issue a series of ALTER SYSTEM commands manually or develop a dynamic SQL script to perform the operation. Source 2.3 shows a PL/SQL procedure to perform a kill of a process using the dynamic SQL package of procedures: DBMS_SQL. In Oracle8i, a new option is available for the ALTER SYSTEM that allows disconnection of the user process after a transaction completes. The commented-out section in Source 2.3 shows this alternative to a straight kill.

Using the procedure from Source 2.3 the DBA can then create a quick SQL procedure to remove the nonrequired Oracle sessions from the Oracle side. An example of this procedure is shown in Source 2.4. An example of the output from ora_kill.sql (kill_all.sql) is shown in Listing 2.4.

REM
REM ORA_KILL.SQL
REM FUNCTION: Kills nonessential Oracle sessions (those that aren't
REM owned)
REM          : by SYS or "NULL"
REM DEPENDENCIES: Depends on kill_session procedure
REM MRA 9/12/96
REM
SET HEADING OFF TERMOUT OFF VERIFY OFF ECHO OFF
SPOOL kill_all.sql
SELECT 'EXECUTE kill_session('||chr(39)||sid||chr(39)||','||
chr(39)||serial#||chr(39)||');' FROM v$session
WHERE username IS NOT NULL
OR username <> 'SYS'
/
SPOOL OFF
START kill_all.sql

The kill_session procedure Is defined as:

CREATE OR REPLACE PROCEDURE kill_session ( session_id in varchar2,
serial_num in varchar2)
AS
cur INTEGER;
ret INTEGER;
string VARCHAR2(100);
BEGIN
--
-- Comment out the following three lines to
-- not use KILL
--
 string :=
        'ALTER SYSTEM KILL SESSION' || CHR(10) ||
CHR(39)||session_id||','||serial_num||CHR(39);
--
-- Uncomment the following 4 lines to use DISCONNECT
--
— string :=
—          'ALTER SYSTEM DISCONNECT SESSION' || CHR(10) ||
— CHR(39)||session_id||','||serial_num||CHR(39)||CHR(10)||
—' POST_TRANSACTION';
   cur := dbms_sql.open_cursor;
   dbms_sql.parse(cur,string,dbms_sql.v7);
   ret := dbms_sql.execute(cur)  ;
   dbms_sql.close_cursor(cur);
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'Error in execution',TRUE);
      IF dbms_sql.is_open(cur) THEN
        dbms_sql.close_cursor(cur);
      END IF;
END;
/

Source 2.3 ORA_KILL.SQL procedure for killing nonessential Oracle sessions.

EXECUTE kill_session('10','212');
EXECUTE kill_session('13','1424');

Listing 2.4 Example of a kill.sql script (output from ora_kill.sql).

Once a session has been killed, its status in the V$SESSION view goes to KILLED, and users will receive an error if they try to reactivate the session. The session entry will not be removed until the user attempts to reconnect. Shutdown immediate and shutdown normal are supposed to be able to handle killed sessions properly, but there have been reports of problems up to version 7.3.2 on some platforms. In 8i and 9i databases, you may wish to use the more polite kill command, which allows for a transactional kill; that is, it waits to kill the process until after its transaction is over. The format for a transactional kill would be:

ALTER SYSTEM DISCONNECT SESSION 'integer1 , integer2'
[POST_TRANSACTION| IMMEDIATE]

Killing from the Operating System Side

The other method of removing these unwanted sessions is to kill them from the operating system side. In UNIX environments, this is accomplished with the kill -9 command executed from a privileged user. In other operating systems, there are similar commands. Source 2.4 shows a UNIX shell command script that will remove the nonessential Oracle sessions for all currently active Oracle databases on the UNIX server.

The ora_kill.sh script in Source 2.4 employs a technique used in the dbshut and dbstart shell scripts. It uses the /etc/oratab file to determine which databases should be operating. An alternative to using the oratab file would be to do “ps -ef|grep smon”, redirecting output into a file, and using awk to strip out the SID names (similar to the technique used below). Each operating instance will have one smon process, so this makes a logical string value to grep out of the “ps -ef” process list.

Killing the sessions from the operating system side will remove their entries from the V$SESSION view. An example of the output from ora_kill.sh (kill.lis) is shown in Listing 2.5.

#!/bin/ksh
ORATAB=/etc/oratab
trap 'exit' 1 2 3
# Set path if path not set (if called from /etc/rc)
case $PATH in
    "")     PATH=/bin:/usr/bin:/etc
     export PATH ;;
esac
rm kill.lis
rm proc.lis

touch kill.lis
touch proc.lis
#
# Loop for every entry in oratab
#
cat $ORATAB | while read LINE
do
    case $LINE in
     \#*)          ;;     #comment-line in oratab
     *)
     ORACLE_SID='echo $LINE | awk -F: '{print $1}' -'
    if [ "$ORACLE_SID" = '*' ] ; then
          ORACLE_SID=""
    fi
      esac
      if [ "$ORACLE_SID" <> '*' ] ; then
           proc_name='oracle'$ORACLE_SID
          ps -ef|grep $proc_name>>proc.lis
      fi
done
cat proc.lis | while read LINE2
do
     command='echo $LINE2 | awk -F: 'BEGIN { FS = ",[ \t]*|[ \t]+" }
                              { print $2}' -'
        test_it='echo $LINE2|awk -F: 'BEGIN { FS = ",[ \t]*|[ \t]+" }
                               { print $8}' -'
     if [ "$test_it" <> 'grep' ] ; then
           command='kill -9 '$command
          echo $command>>kill.lis
     fi
done    
rm proc.lis
chmod 755 kill.lis
kill.lis

rm kill.lis

Source 2.4 Shell script to kill nonessential Oracle processes from the server side.

kill -9 11240
kill -9 11244
kill -9 11248
kill -9 11252
kill -9 11256
kill -9 9023
kill -9 9025
kill -9 9028
kill -9 9030

Listing 2.5 Example output from the ora_kill.sh script (kill.lis).

It may be necessary to terminate nonessential Oracle sessions if these sessions are “abandoned” by their users, or if a shutdown must be accomplished regardless of database activity. This termination can be accomplished with one of three methods: a shutdown with the abort option, use of the ALTER SYSTEM kill options, or use of the operating system process killer.



See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter