Checking the status of RMAN backups

I find the below SQL useful when I want to check when

  1. the last backup ran
  2. how long the backup takes

You can remove the comments as required to filter to either a full backup or archive logs as needed.

alter session set nls_date_format = 'dd-MON-yy hh24.mi.ss';
select
           jd.input_type
          ,TO_CHAR(jd.START_TIME,'mm/dd/yy hh24:mi') start_time
          ,TO_CHAR(jd.END_TIME,'mm/dd/yy hh24:mi')   end_time
          ,jd.AUTOBACKUP_DONE                        ctrl_file
          ,jd.status
          ,round(jd.ELAPSED_SECONDS,2)               elapsed_time_sec
          ,ceil((jd.ELAPSED_SECONDS/60))             elpased_time_min
          ,ceil(((jd.ELAPSED_SECONDS/60)/60))        elpased_time_hr
from       V$RMAN_BACKUP_JOB_DETAILS jd
where      jd.START_TIME > sysdate - 10
--and      jd.input_type = 'DB FULL'
--and      jd.input_type = 'ARCHIVELOG'
order by
           start_time
;

Author: Dean Capps

Database consultant at Amazon Web Services.