Tuesday 3 April 2018

Script to see RMAN running jobs and to kill them - Oracle Database

Script to monitor RMAN running jobs and to kill them

Script to monitor RMAN running jobs and to kill them

The below mentioned script helps you to find out already running RMAN operations (if any) in the database. You can modify the query as per the output you want to get but this particular one will show you the list of all the RMAN jobs which are currently in running state and are not yet completed. This query will also show you the jobs which might not be running because of manual cancellation but are still pending to be cleared up from the queue.

select sid, serial#, start_time, totalwork,  sofar, (sofar/totalwork) * 100 pct_done 
from v$session_longops
where totalwork > sofar
AND opname like 'RMAN%';

If you want to kill some particular RMAN Job which you do not want to exist in the database, use the below mentioned queries

For Single Instance:

alter system kill session 'SID,SERIAL#' immediate;

In Oracle's multi-instance environment (Oracle RAC), it might happen that when you run the above mentioned query, it'll fail with the with mentioned error,

ERROR at line 1: 
ORA-00026: missing or invalid session ID

This is because in multi-instance environment, you need to specify the instance_id as well with the query. There are chances that the instance from where you are trying to kill the job is different from the instance from where the job has run.

For Oracle RAC (multi instance environments):

alter system kill session 'SID,SERIAL#,@instance_id' immediate;

No comments:

Post a Comment