Re: Query RMAN Repository
- From: "Christo Kutrovsky" <kutrovsky.oracle@xxxxxxxxx>
- To: yoursraju007@xxxxxxxxx
- 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.
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 - www.pythian.com
I blog at http://www.pythian.com/blogs/
On Sat, Mar 1, 2008 at 5:04 PM, Raj Mareddi <yoursraju007@xxxxxxxxx> 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 - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
