 |
|
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. |
|
