Backup Report from Catalog

I] Query the RMAN catalog to check backup status:

This script will query the RMAN catalog and report on the backup status of every database in the catalog.
 
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));

II]  Query the RMAN catalog to list backup completion status

Note – run this query connected as the owner of the RMAN catalog

set lines 80

set pages 250

ttitle "Daily Backup........"

select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,

NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP

from (

select a.name DB,dbid,

decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,

decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch

from rc_database a,bs b

where a.db_key=b.db_key

and b.bck_type is not null

and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE

is not null or AUTOBACKUP_SEQUENCE is not null)

and b.bs_key not in(select bs_key from rc_backup_spfile)

group by a.name,dbid,b.bck_type

) group by db,dbid

ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))

/

Backup Report from database side

Script to report on all backups - full, incremental and archivelog backups:

set linesize 2000

col STATUS format a9

col hrs format 999.99

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, STATUS from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Script to report on all backups - full, incrementals, not archivelog backups:

set linesize 2000

col STATUS format a9

col hrs format 999.99

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, STATUS from V$RMAN_BACKUP_JOB_DETAILS where input_type='DB INCR' order by session_key;

I] Script to report on all backupsets:

select decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,

incremental_level,

round(sum(original_input_bytes)/1024/1024,2) "MB in",

round(sum(output_bytes)/1024/1024,2) "MB out",

status,

min(start_time),

max(completion_time),

round((sum(output_bytes)/1024/1024)/((max(completion_time)-min(start_time))*86400), 1) "MB/s"

from v$backup_set_details

group by backup_type, incremental_level, status, session_key, session_recid, session_stamp

order by 6

/

II] Script to report on all backupsets:

 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;