Recently I cam across a situation where RMAN backup jobs was stuck and I killed the hung session because database size was only 4 GB and it was running from last four hours. After some modifications in RMAN command we ran the jobs again and it completed successfully in less than 1 hour.
Steps to terminate the RMAN jobs.
STEP 1: Check the status of the jobs using below query:
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;
STEP 2: Check the RMAN output log to trace the issue:
https://orahow.com/check-rman-log-file-and-status/
STEP 3: Find the SID and SERIAL# to kill the jobs:
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%';
STEP 4: To Kill the RMAN Jobs:
alter system kill session 'SID, SERIAL#' immediate;
Note: you will get SID and SERIAL# from the step 1.