Re: Query RMAN Repository

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
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 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_at_gmail.com> 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/
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 03 2008 - 04:26:04 CST