Re: Query RMAN Repository
Date: Mon, 3 Mar 2008 14:26:04 +0400
Here is the query I have developed to monitor and keep track of the FRA (Flash Recovery Area) usage without an RMAN catalog database.
- 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 judgehow many archivelogs are needed to make consistent
I hope you like it.
select /* RMAN RAC FRA content Christo Kutrovsky - The Pythian Group
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 - www.pythian.com I blog at http://www.pythian.com/blogs/ On Sat, Mar 1, 2008 at 5:04 PM, Raj Mareddi <yoursraju007_at_gmail.com> wrote:Received on Mon Mar 03 2008 - 04:26:04 CST
> 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 ?
> !!! 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 - www.pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-l