Perth, Australia
+ (61) 417713124

Query the RMAN catalog to check backup status

  • Posted by Gavin Soorma
  • On June 23, 2009

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 DB,dbid,
max(b.completion_time)) BACKUPTYPE_db,
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,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'));


Yalcin YAVAS
  • Jan 24 2011
Very useful query. Well done and thanks. Yalcin YAVAS

Leave Reply

Your email address will not be published. Required fields are marked *