Killing Hung RMAN Session in Oracle

Sometimes you may need to kill the RMAN session which is hanging and does not respond. 

There are several ways to terminate an RMAN command in the middle of execution:

1. The preferred method is to press CTRL+C (or the equivalent "attention" key combination for your system) in the RMAN interface. This will also terminates allocated channels, unless they are hung in the media management code, as happens when, for example, when they are waiting for a tape to be mounted.

2. The first option is using the alter system kill session command. You need to get the sid and serial# values from the v$process and v$sessions views. 

3. You can terminate the server session corresponding to the RMAN channel on the operating system.

Components of an RMAN Session

The nature of an RMAN session depends on the operating system. In UNIX, an RMAN session has the following processes associated with it:

* The RMAN client process itself

* The default channel, the initial connection to the target database

* One target connection to the target database corresponding to each allocated channel

* The catalog connection to the recovery catalog database, if you use a recovery catalog

* An auxiliary connection to an auxiliary instance, during DUPLICATE or TSPITR operations

* A polling connection to the target database, used for monitoring RMAN command execution on the various allocated channels. By default, RMAN makes one polling connection. RMAN makes additional polling connections if you use different connect strings in the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. One polling connection exists for each distinct connect string used in the ALLOCATE CHANNEL or CONFIGURE CHANNEL command.

Check running RMAN jobs

set lines 300
col STATUS format a22
col hrs format 999.99

select SESSION_KEY, SESSION_RECID, SESSION_STAMP,INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Find session details of all RMAN session

SQL> select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where a.addr=b.paddr and client_info like 'rman%';


Or,

SQL> select b.sid, b.serial#, a.spid, b.client_info
from
 v$process a, v$session b
where
 a.addr=b.paddr and client_info
like
 'rman%';

Examine the SQL output to determine which sbt functions are waiting.

COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, 
       sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
;

Use the alter system kill session command to kill the running backup session:

SQL> Alter system kill session 'SID, SERIAL#' immediate;

Using operating system-level tools appropriate to your platform, kill the hung sessions. 
For example, on Linux execute a kill -9 command:

$ kill -9 PID

$ kill -9 8642 8374

Hope it worked !! 🙂