Sunday, June 14, 2015

Killing a process using orakill.exe on Windows

If you kill an oracle session using ALTER SYSTEM KILL SESSION command, status of this session in v$sesison view is set to “KILLED”, and after some time you no longer see this session. Sometimes it may happen that status of this session remains “KILLED” for a very long time and resources taken by this session are still not released. I have observed this especially in case if you try to kill a session which is hung, and killing it really does not work and sessions is still there with “KILLED” status . In this scenario you can use “kill” command on Unix based platforms to kill the process of this session at OS level and soon after that, this session is also gone and cannot be seem in v$session view.
 
If you are on Windows platform; and are caught in this scenario where you want to kill a process because killing a session did not work for you, you may not simply use task manager to kill a process because oracle sessions run as a thread under main oracle process (and task manager only shows one process oracle.exe in windows task manager). To kill a session process/thread on Windows, Oracle provides orakill.exe which is found under ORACLE_HOME\bin directory. Following example shows how you use orakill.exe to kill a session process on Windows platform.
SQL> select a.inst_id,spid,a.program from gv$session a, gv$process b where a.paddr=b.addr and a.program='sqlplus.exe';

   INST_ID SPID         PROGRAM
---------- ------------ ----------------------------------------------------------------
         1 422236       sqlplus.exe
         2 1837168      sqlplus.exe
         3 1468872      sqlplus.exe

Here you see that in my RAC environment I have total 3 sessions across 3 instances, which I want to kill using orakill.exe. You would need to log in to respective node to kill a session connected to the instance running on that node.
You also see that I have SPID column selected in my query from gv$process view because SPID column shows process/thread ID which would be needed to kill these sessions.

Execute following from node1 where instance1 is running to kill session with SPID 422236. First parameter passed to orakill.exe is instance name, and second parameter is SPID.
C:\Users\Administrator>orakill inst1 422236

Kill of thread id 422236 in instance inst1 successfully signalled.

After a few seconds you will see that this session is gone.
SQL> select a.inst_id,spid,a.program from gv$session a, gv$process b where a.paddr=b.addr and a.program='sqlplus.exe’;

   INST_ID SPID         PROGRAM
---------- ------------ ----------------------------------------------------------------
         2 1837168      sqlplus.exe
         3 1468872      sqlplus.exe


Execute same on node2 and node3 to kill sessions connected to these instances
On node2
C:\Users\Administrator>orakill inst2 1837168
Kill of thread id 1837168 in instance inst2 successfully signalled.

On Node 3
C:\Users\Administrator>orakill inst3 1468872
Kill of thread id 1468872 in instance inst3 successfully signalled.


Now all sessions are gone

SQL> select a.inst_id,spid,a.program from gv$session a, gv$process b where a.paddr=b.addr and a.program='sqlplus.exe' and a.machine like '%MYRAC%';

no rows selected


No comments: