RE: Query RMAN Repository
Date: Thu, 6 Mar 2008 18:03:51 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36044B606C@CWYMSX04.Corp.Acxiom.net>
Raj,
All our RMAN backups make a call to a shell script which sets a few variables, then executes the following script, which saves stats from the backup in our stats repository:
INSERT INTO perfstat.stats_rman_backup_summ_tb SELECT '$ORACLE_SID', bp1.tag, bs.backup_type, bp1.bkup_start_time, bp1.bkup_completion_time
, bp1.elapsed_minutes, bp1.total_pieces, bp1.tape_count, bp1.total_gb, bp1.avg_mb_transfer_rate_per_sec FROM (SELECT bp.tag
, MAX(bp.set_count) KEEP (DENSE_RANK last ORDER BY bp.start_time) set_count
, MAX(bp.set_stamp) KEEP (DENSE_RANK last ORDER BY bp.start_time) set_stamp
, MIN(bp.start_time) bkup_start_time , MAX(bp.completion_time) bkup_completion_time , COUNT(*) total_pieces , (MAX(bp.completion_time) - MIN(bp.start_time)) * 1440 elapsed_minutes , COUNT(DISTINCT bp.media) tape_count , SUM(bsi.bytes) / 1073741824 total_gb , (AVG(bsi.effective_bytes_per_second)) / 1048576 avg_mb_transfer_rate_per_sec FROM v\$backup_piece@${ORACLE_SID}_dbl bp , (SELECT set_count, set_stamp, bytes, effective_bytes_per_second FROM v\$backup_sync_io@${ORACLE_SID}_dbl WHERE type = 'OUTPUT' UNION ALL SELECT set_count, set_stamp, bytes, effective_bytes_per_second FROM v\$backup_async_io@${ORACLE_SID}_dbl WHERE type = 'OUTPUT') bsi WHERE bp.start_time >= sysdate - $IN_MODE_DAYS AND bp.tag LIKE 'TAG%T%' AND ( bp.set_count = bsi.set_count AND bp.set_stamp = bsi.set_stamp) GROUP BY bp.tag) bp1 , v\$backup_set@${ORACLE_SID}_dbl bsWHERE bp1.set_count = bs.set_count
AND bp1.set_stamp = bs.set_stamp
AND NOT EXISTS (SELECT '1'
FROM perfstat.stats_rman_backup_summ_tb srb2 WHERE srb2.tag = bp1.tag AND srb2.database_name = '$ORACLE_SID');
Dave
Dave Herring, DBA | A c x i o m M I C S / C S O
630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
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 !!!!
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank You.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 06 2008 - 18:03:51 CST