Show RMAN parameters

RMAN> show all;

Check backup jobs

SQL>
set pagesize 0;
select start_time as "Data", status as "Result" from v$rman_backup_job_details order by 1;

SQL> SELECT start_time, end_time, input_type, input_type, status FROM v$rman_backup_job_details ORDER BY 1;

SQL> SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time AND vrbjd.input_type <> 'ARCHIVELOG' ORDER BY 2,1;

Status of the last backup

SQL> select status from V$RMAN_BACKUP_JOB_DETAILS where end_time = (select max( end_time) from V$RMAN_BACKUP_JOB_DETAILS);

Age in days of the last backup

SQL> select round(sysdate - end_time) from V$RMAN_BACKUP_JOB_DETAILS where end_time = (select max( end_time) from V$RMAN_BACKUP_JOB_DETAILS);

Report of all backups: full, incremental and archivelog backups

SQL>
col STATUS format a15;
col hrs format 999.99;
col INPUT_TYPE format a10;
col START_TIME format a15;
col END_TIME format a15;
SELECT
SESSION_KEY, 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;

Size and date of Incremental Backups

SQL> SELECT end_time, ROUND(output_bytes/1024/1024) as Size_Mb FROM V$RMAN_BACKUP_JOB_DETAILS WHERE input_type = 'DB INCR' ORDER BY end_time DESC;

Info

RMAN> list backup

Get max SCN numbers contained in the last online backup

SQL> select max(checkpoint_Change#) from (select max(COMPLETION_TIME) dt from v$backup_datafile) temp, v$backup_datafile bd where temp.dt =bd.COMPLETION_TIME;

This will give SCN of last backup, it could be levlel1 and level 0, you can modify above as per your backup schedule

SQL> 
select ctime "Date"
     , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type
     , bsize "Size MB"
from (select trunc(bp.completion_time) ctime
        , backup_type
        , round(sum(bp.bytes/1024/1024),2) bsize
   from v$backup_set bs, v$backup_piece bp
   where bs.set_stamp = bp.set_stamp
   and bs.set_count  = bp.set_count
   and bp.status = 'A'
   group by trunc(bp.completion_time), backup_type)
order by 1, 2;

Last backup, SQL for v$backup_set

SQL> select * from v$backup_set  where completion_time > sysdate-7 order by completion_time desc;

Most recent backup, SQL for rc_backup_set

SQL> select * from rc_backup_set where completion_time > sysdate-7 order by completion_time desc;

Most recently completed RMAN backup

SQL> select * from rc_backup_set where backup_type = 'D' and controlfile_included = 'BACKUP' order by completion_time desc;

Newest_backupset_time for RMAN backup

SQL> select db_name, newest_backup_time from rman.rc_backupset_summary where dn_name = 'ORCL';

Outer join rc_backupset_summary into rc_database to see the RMAN latest backup start time

SQL> select name, max(start_time) from rman.rc_database d, rman.rc_backup_set s where db_id(+) = dbid group by name order by 2 desc;

RMAN backup status start and end times

SQL> 
select operation, status, object_type, to_char(start_time,'mm/dd/yyyy:hh:mi:ss') as start_time, to_char(end_time,'mm/dd/yyyy:hh:mi:ss') as end_time 
from v$rman_status 
where start_time > SYSDATE -1 
and operation = 'BACKUP' 
and object_type = 'DB FULL' 
order by start_time desc;