Re: Query RMAN Repository
Date: Mon, 3 Mar 2008 14:26:04 +0400
Message-ID: <52a152eb0803030226i55d52e17t826d6cba558b25cc@mail.gmail.com>
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 judgehow 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 - 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 ?
>
> 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 - www.pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-l