ORACLE - DBA Tips Corner

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

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:

Oracle Instance (SID)
SPID of the thread to kill

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:

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:

Clearing Locks - Using alter system kill session... will not clear any locks that may exist. Oracle will keep the session connected until the user session times out. It is only then that Oracle releases any locks. With OraKill, the thread will be killed and all locks held by that thread released.

Unable to Connect to Oracle - It may be that the DBA cannot gain access to the database to obtain the SID and Serial# for the user session. It may be because a runaway process is consuming all available resources (CPU, memory, I/O). With OraKill, if the DBA knows the SPID, they can simply kill the user session from the command-line. But how does the DBA find the SPID if he cannot log into the database. This is where another Microsoft Utility comes in handy - QuickSlice.

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

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: 99619