Oracle Orphaned Sessions: Killed blocking session is still in the Killed state and the corresponding row is no more found in the v$process

Hello folks,

I saw this interesting issue some time back .The problem in this case was that there were many sessions waiting for the lock on an object and there was a terminal blocker blocking these sessions.

To resolve this issue DBA did what he was supposed to do .The terminal blocker was killed using the commands "alter system kill session...." and the session was marked for kill.After some time the session was showing in the killed state but the session was still lingering around and it was never actually released out of the database.

Now to kill the session old ways we tried to get the corresponding process in the v$process view but there was no entry for matching the paddr column of v$session to the addr column of the v$process.

That is no matching row for that session in the v$process.So there is no way we can kill the process using the commands kill -9 .

The following query for that session will show no rows because there is no corresponding entry in the v$process view

SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr and s.sid=
/

If you take a sytemstate dump of the entire database this session will show up under ORPHAN SESSIONS (upper case if you are on unix based platform).

For some reason pmon was not able to cleanup this session and it is still there in the database as orphaned session.

The most irritating part of this problem is that this is locking many objects seen from select count(*) from v$locked_objects where sid= .

Now how to get rid of this session whose corresponding row is not there in the v$process.

SOLUTION

There are two solutions to this problem depending on what works best and is easy in your case.

1) Bouncing the database.

Most obvious solution anyone can think of is bouncing the database.This will no doubt fix this problem but it may not be acceptable in a prod environment to bounce the database just to get rid of an orphaned process.

2) Get help from oracle Doc ID 387077.1.

This is what I ended up doing.If you are in luck and you are on 11g then you can use this MOS note to try to resolve your issue.


Basically what happens when a session gets killed then a new process id gets created and the addr row  in v$process matching the addr row for the session which was there earlier will no longer be there.Instead a new row will be there.To uniquely identify the process and to fix this issue in 11g additional columns have been added to V$SESSION :

V$SESSION
CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process
CREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

Using these columns we can identify the process id of the killed session.In order to identify the new process id you can use the following query.

select * from v$process where addr=(select creator_addr from v$session where sid=);

Once you get the process id from the above query rest of the task is simple.Now we just need to kill the corresponding process id from the OS.

-> Kill -9

Now be patient , it will take some time for the pmon process to finally do the cleanup.

If this is the issue with the stuck cleanup of the killed session then killing the OS process will help to restart the cleanup.

In some cases it may not be able to cleanup and you may need to bounce the database.

If this solution works for you its great .

If not then you know what to do next =>BOUNCE !!

Good Luck!