![]() DBA Tips Archive for Oracle |
Killing Runaway Oracle Processes on Windows with OraKill
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
Shadow Processes in UNIX
As you will see in this article, the OraKill utility can be used much in the
same way as kill -9 would be used in a UNIX environment.
Using OraKill
Now, let's work through a simple example of how to kill an Oracle
session using the OraKill utility. First, we take a look at
several sessions:
Why OraKill?
As mentioned above, it may not be possible for the DBA to login to the database
to obtain the SPID value for the user session. This is needed to kill the
user session using OraKill. A free utility named QuickSlice can be used to look
into the individual threads created by each process - namely the oracle.exe
process. QuickSlice can be downloaded using the following link:
Copyright (c) 1998-2019 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
This article provides details on how to kill runaway Oracle processes
in Microsoft Windows using the orakill.exe
command-line utility. The OraKill utility is included
with the Oracle database on all Windows platforms. It allows the
DBA to kill an Oracle session directly from an MS-DOS command-line
without requiring any login connection to the database.
If you have ever worked with Oracle in a UNIX environment, you should already be
familiar with killing an Oracle session (actually the Oracle shadow process) using
the kill -9 command. Under the UNIX operating environment, the shadow process is forked
from the Oracle client application (i.e. sqlplus). You would use the UNIX ps
command to list all processes you are interested in as in the following example:
% ps -ef | grep TARGDB
oracle 4407 1 0 19:23:25 ? 0:01 ora_smon_TARGDB
oracle 4415 1 0 19:23:25 ? 0:00 ora_arc0_TARGDB
oracle 4403 1 0 19:23:24 ? 0:02 ora_lgwr_TARGDB
oracle 4405 1 0 19:23:25 ? 0:00 ora_ckpt_TARGDB
oracle 4525 1 2 22:48:29 ? 0:00 oracleTARGDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 4439 1 0 19:31:50 ? 0:20 ora_qmn0_TARGDB
oracle 4417 1 0 19:23:25 ? 0:00 ora_arc1_TARGDB
oracle 4401 1 0 19:23:24 ? 0:00 ora_dbw0_TARGDB
oracle 4409 1 0 19:23:25 ? 0:00 ora_reco_TARGDB
oracle 4399 1 0 19:23:23 ? 0:02 ora_pmon_TARGDB
oracle 4411 1 0 19:23:25 ? 0:00 ora_cjq0_TARGDB
root 4419 1 0 19:23:26 ? 0:00 ora_dism_TARGDB
If I know that I have a runaway process (let's say process ID 4525), I could
kill it from the UNIX command-line using the following:
% kill -9 4525
Unlike the UNIX operating environment, Microsoft Windows is completely thread-based.
For each Oracle instance, all background processes and user sessions are contained within the
the oracle.exe executable. These threads are not listed in the Processes
tab under the Windows Task Manager application. Each user session will create its own
thread within the running oracle.exe process and is not easily visible to the Windows
DBA. If you were to try to kill the oracle.exe process, you would take down (crash)
the entire Oracle instance.
To get started, let's simply type in the OraKill utility at the command
prompt:
C:\> orakill
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill
The thread id should be retrieved from the spid column of a query such as:
select spid, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
The OraKill utility requires two parameters:
SELECT a.username, a.osuser, b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT null;
USERNAME OSUSER SPID
---------------- ---------------------- ----
OEM SYSTEM 2964
OEM SYSTEM 3072
OEM SYSTEM 3116
OEM SYSTEM 3124
OEM SYSTEM 3144
OEM SYSTEM 3156
OEM SYSTEM 3180
OEM SYSTEM 3188
OEM SYSTEM 3196
OEM SYSTEM 3204
OEM SYSTEM 3216
SYS SYSTEM 3740
SYSTEM MELODY\jhunter 1116
SCOTT MELODY\jhunter 1384
14 rows selected.
Now that we have the user sessions (and their SPIDs) let's kill
the SCOTT session using OraKill:
C:\> orakill JEFFDB 1384
Kill of thread id 1384 in instance JEFFDB successfully signalled.
When we go back to query all user sessions, we can see that the
SCOTT user session has been killed:
SELECT a.username, a.osuser, b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT null;
USERNAME OSUSER SPID
---------------- ---------------------- ----
OEM SYSTEM 2964
OEM SYSTEM 3072
OEM SYSTEM 3116
OEM SYSTEM 3124
OEM SYSTEM 3144
OEM SYSTEM 3156
OEM SYSTEM 3180
OEM SYSTEM 3188
OEM SYSTEM 3196
OEM SYSTEM 3204
OEM SYSTEM 3216
SYS SYSTEM 3740
SYSTEM MELODY\jhunter 1116
13 rows selected.
Why would Oracle provide a command-line utility to kill Oracle user sessions
when the option already exists to kill sessions within Oracle. For example, we
could have simply determined the SID and Serial# for the user
session, logged into the database and used the following command:
SQL> alter system kill session SID, Serial#;
The DBA would be able to query the SID and Serial# for the user
session from the v$session dynamic view. There are several reasons
why the DBA would want to use OraKill from the command-line rather than
using alter system kill session... command:
QuickSlice.
QuickSlice
qslice_setup.exe
641 KB file
3 min @ 28.8 Kbps
After downloading, installing and running the QuickSlice utility, you will have a screen similar to the
following:
Let's now run through a quick example of how to use QuickSlice to find
the SPID for a user session. We can start by creating a
small runaway process. Type in the following anonymous PL/SQL block
and run it:
begin
loop
null;
end loop;
end;
/
After kicking off the above code, we now have a runaway process. This can be
seen in QuickSlice:
OK, we know that Oracle is consuming a tremendous amount of CPU, but which
thread (user session) is taking all of the CPU? Simply double-click
the oracle.exe process in QuickSlice. This will bring up the
following window:
We can see from the window (above) that Thread ID "7e8" is consuming
all of the CPU. This is the thread (SPID) we want to kill. Notice that QuickSlice
displays the Thead ID in HEX. You will need to convert this to a decimal
number in order to use it as a parameter to OraKill:
7e8 (Hex) ==> 2024 (Dec)
We now have all of the information we need to kill our runaway
user session using OraKill:
C:\> orakill JEFFDB 2024
Kill of thread id 2024 in instance JEFFDB successfully signalled.
That's all there is to it. Our runaway process is immediately killed
as seen below:
SQL> begin
2 loop
3 null;
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
Saturday, 18-Sep-2010 17:38:24 EDT
Page Count: 99332