DBA Tips Archive for Oracle

  


Killing Runaway Oracle Processes on Windows with OraKill

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Shadow Processes in UNIX
  3. Using OraKill
  4. Why OraKill?


Overview

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.


Shadow Processes in UNIX

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.

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

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:

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:

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 OraKill?

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:

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:

QuickSlice
Download Fileqslice_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>



Copyright (c) 1998-2017 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.

Last modified on
Saturday, 18-Sep-2010 17:38:24 EDT
Page Count: 96275