RE: Query RMAN Repository

From: Herring Dave - dherri <>
Date: Thu, 6 Mar 2008 18:03:51 -0600
Message-ID: <>


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
             , COUNT(DISTINCT tape_count
             , SUM(bsi.bytes) / 1073741824 total_gb
             , (AVG(bsi.effective_bytes_per_second)) / 1048576
          FROM v\$backup_piece@${ORACLE_SID}_dbl bp
             , (SELECT set_count, set_stamp, bytes,
                  FROM v\$backup_sync_io@${ORACLE_SID}_dbl
                 WHERE type = 'OUTPUT'
                       UNION ALL
                SELECT set_count, set_stamp, bytes,
                  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 bs
 WHERE bp1.set_count = bs.set_count
   AND bp1.set_stamp = bs.set_stamp
                     FROM perfstat.stats_rman_backup_summ_tb srb2
                    WHERE srb2.tag = bp1.tag
                      AND srb2.database_name = '$ORACLE_SID');


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 <> wrote:
> I am trying to find out file size, name as of date/ sequence number.
> 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
> 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.

Received on Thu Mar 06 2008 - 18:03:51 CST

Original text of this message