Re: Query RMAN Repository

From: Christo Kutrovsky <>
Date: Mon, 3 Mar 2008 14:26:04 +0400
Message-ID: <>

Here is the query I have developed to monitor and keep track of the FRA (Flash Recovery Area) usage without an RMAN catalog database.

Some specifics:
- grouped by day, tag and type
- archivelogs are reported by number only. The number in brackets is instance number.

- all sizes are compressed sizes, if used
- P - percentage of overal size (not percentage of FRA size parameter)
- controlfile/spfile tags masked for grouping reasons
- H - hours it took to complete, for datafiles can be used to judge
how many archivelogs are needed to make consistent

I hope you like it.

select /* RMAN RAC FRA content Christo Kutrovsky - The Pythian Group */to_char(nvl(trunc(rl_first_time),
trunc(DF_CKP_MOD_TIME)),'YYYY-Mon-DD Dy') as dt,backup_type,decode(file_type,'CONTROLFILE','C','ARCHIVED LOG', '('||rl_thread#||') '||min(RL_SEQUENCE#) ||'-'||max(RL_SEQUENCE#),'SPFILE','SPFILE',file_type||'-'||bs_incr_type) as file_type,count(*) as
cnt,round(sum(nvl(bs_bytes_avg,bytes))/1024/1024,0)as mb, round(ratio_to_report(sum(nvl(bs_bytes_avg,bytes))) over ()*100) as P, keep_options as
keep,bs_status,decode(file_type,'CONTROLFILE','','SPFILE','',bs_compressed) as ZIPED,obsolete,
decode(FILE_TYPE,'CONTROLFILE','<MASKED>','SPFILE','<MASKED>',bs_tag) as bs_tag,min(bs_completion_time) as
completion_time,round((max(bs_completion_time)-min(bs_completion_time))*24,1) as H, max(rl_next_time) as archive_time
from ( select bs_bytes/count(bs_bytes) over (partition by bs_key) as bs_bytes_avg, t.* from v$backup_files t where (bs_device_type = 'DISK' or DEVICE_TYPE = 'DISK' ) and file_type <> 'PIECE' ) bf group by backup_type,file_type,rl_thread#,bs_incr_type,keep_options,bs_status,bs_compressed,obsolete,decode(FILE_TYPE,'CONTROLFILE','<MASKED>','SPFILE','<MASKED>',bs_tag), trunc(rl_first_time), trunc(DF_CKP_MOD_TIME) order by nvl(min(DF_CHECKPOINT_CHANGE#),max(RL_NEXT_CHANGE#)) desc,completion_time desc, mb desc;

Christo Kutrovsky
DBA Team Lead
The Pythian Group -
I blog at

On Sat, Mar 1, 2008 at 5:04 PM, Raj Mareddi <> wrote:

> I am trying to find out file size, name as of date/ sequence number. For
> example, I want to know what the datafile sizes last month were (Assume that
> RMAN backup is taken everyday).
> I found the following tables interesting but they are not documented.
> rc_datafile , offr
> Any ideas ?
> Thanks.
> --
> !!! In the confrontation between the stream and the rock, the stream always
> wins - not through strength, but through persistence. -Buddha !!!!
-- Christo Kutrovsky DBA Team Lead The Pythian Group - I blog at --
Received on Mon Mar 03 2008 - 04:26:04 CST

Original text of this message