How to check RMAN backup job status in Oracle using v$rman_backup_job_details

 RMAN backup job details for 'n' number of days:-

=========================================

Monitoring RMAN backup status using v$rman_backup_job_details and v$rman_status.

Note : - Enter the number of days required for status report, for 1 day backup status report provide input as '1'.

RMAN backup status using v$rman_backup_job_details :-

set pages 9999 lines 500

col INSTANCE for a9
col ELAPSED for a30
SELECT (  SELECT   instance_name FROM v$instance)
              || ' '
              || (  SELECT   instance_number FROM v$instance)
                 instance,
            --  TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI') start_time,
       to_date (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time,
              TO_CHAR (output_bytes / 1048576, '999,999,999.9') output_mb,
              TO_CHAR (output_bytes_per_sec / 1048576, '999,999.9') output_mb_per_sec,
              time_taken_display elapsed,input_type,status
         FROM v$rman_backup_job_details
         where start_time >= sysdate - &NUMBER_OF_DAYS
         ORDER BY start_time
         /


RMAN backup status using v$rman_backup_job_details , v$rman_status:-

set pages 9999 lines 500

set numformat 99999.99

set trim on 

set trims on

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

col INSTANCE for a9

col status for a22

col COMMAND_ID for a20

col INPUT_TYPE for a10

col OUTPUT_DEVICE_TYPE for a10

col OUTPUT_BYTES_PER_SEC_DISPLAY for a9

col status heading "BACKUP|STATUS" 

col COMMAND_ID heading "BACKUP NAME" 

col STARTED_TIME heading "START TIME" 

COL END_TIME heading "END TIME" 

col ELAPSED_TIME heading "MINUTES | TAKEN" 

col INPUT_TYPE heading "INPUT|TYPE" 

col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES" 

col INPUT_SIZE heading  "INPUT SIZE|GB"

col OUTPUT_SIZE heading  "OUTPUT SIZE|GB" 

col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"

SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,

rj.COMMAND_ID,

rj.STATUS,

max(rj.START_TIME) STARTED_TIME, 

rj.END_TIME,

rj.ELAPSED_SECONDS/60 ELAPSED_TIME,

rj.INPUT_TYPE,

rj.OUTPUT_DEVICE_TYPE,

rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE, 

rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,

rj.OUTPUT_BYTES_PER_SEC_DISPLAY

from v$rman_backup_job_details rj, v$rman_status rs

where rj.COMMAND_ID=rs.COMMAND_ID

group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY 

having max(rj.START_TIME) > sysdate-&NUMBER_OF_DAYS order by rj.START_TIME desc

/

                                  BACKUP                                                          MINUTES  INPUT      OUTPUT     INPUT SIZE OUTPUT SIZE OUTPUT RATE

INSTANCE  SID BACKUP NAME          STATUS                 START TIME          END TIME                TAKEN TYPE       DEVICES            GB          GB (PER SEC)

--------- --- -------------------- ---------------------- ------------------- ------------------- --------- ---------- ---------- ---------- ----------- ------------

To get the job details for a specific backup job,  use the following query:-

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

Where,

CF: Number of controlfile backups included in the backup set

DF: Number of datafile full backups included in the backup set

I0: Number of datafile incremental level-0 backups included in the backup set

I1: Number of datafile incremental level-1 backups included in the backup set

 L: Number of archived log backups included in the backup set

Backup set details : -

To get the Backup set details for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
 
set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

Backup job output :-

To get the Backup job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, use the following query:
   
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;

----------

Ref->RMAN

------------------