How to kill, get rid of sticky RMAN processes

Killing RMAN processes with alter system kill session... will not have an effect on them.They will marked as killed but never killed.

You have no time to wait, but those silly RMAN processes have much of that time. So, how to deal with these guys?

Finding the SPID of the process and killing from OS side is a solution, which most people recommend.

I have a practical, handy solution for this case, don't wait them:) Use disconnect session clause with immediate keyword with alter system disconnect session. For example, you can disconnect a session having SID 1122 and serial# 222 like below:

alter system disconnect session '1122,222' immediate;

Below is a script which could be used to generate disconnect session sqls for all of the RMAN sessions.

SELECT 'alter system disconnect session '''||sid||','||serial#||''' immediate;' 
FROM V$SESSION s 
 WHERE 
(
UPPER( s.PROGRAM )LIKE 'RMAN%'
)

 

Remember that there are differences between disconnect and kill session clauses.
Most remarkable is ability to give inst_id or not.

In case of disconnect session, You cannot issue disconnect session to sessions spread on cluster instances from one session. Thus, you must directly connect to sessions' instance first, and then issue the alter system disconnect session statement.

In case of kill session, you have a chance to give third parameter "inst_id" in kill session clause. This allows you to kill sessons spread on cluster instances from one session.

It woths referring to SQL Language Reference for details of disconnect session and kill session clauses.

Hope this helps!