Home > tips > How to kill, get rid of sticky RMAN processes

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!

Advertisements
  1. Desmond
    August 1, 2014 at 4:52 am

    Thanks for you article, just a quick one, its also possible to use an instance id for disconnect session,which will disconnect the session on the appropriate system.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: