SQL Script RMAN Statistic Summary

Updated: 08-Jan-2002
Version: 1.0

Description

Script reports RMAN backup statistics direct from the recovery catalog.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 200

col datafileMb format 9,999,999 heading "Datafile Size Mb"
col backedupMb format 9,999,999 heading "Backuped Size Mb"

break on report
compute sum of datafileMb on report 
compute sum of backedupMb on report 

show user

SELECT db_name,MIN(completion_time)
FROM rc_backup_datafile
GROUP BY db_name;

REM top running backups

select rownum as rank
       ,name
       , DECODE(backup_type,'D','Full','I','Incremental'
           ,'L','Archive Log',backup_type) backup_type
       ,round(max_secs/60) mins
FROM ( select name , backup_type , max(elapsed_seconds)  max_secs
     from rc_backup_set bs, rc_database d
     where bs.db_key = d.db_key
     group by name, backup_type
     order by max(elapsed_seconds) desc
     )
WHERE rownum <= 10

SELECT db_name  
   , DECODE(status,'A','Available'
                   ,'D','Deleted'
                   ,'O','Unusable'
                   ,status)        status
   , COUNT(*)
from rc_backup_datafile
GROUP BY db_name,status

SELECT db_name
   , DECODE(backup_type, 'D','Full','Incremental') backup_type
   , SUM(datafile_blocks*block_size) /1024/1024 datafileMb
   , SUM(blocks*block_size) /1024/1024 backedupMb
FROM rc_backup_datafile
GROUP BY db_name
   , DECODE(backup_type, 'D','Full','Incremental');

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links