RE: Query RMAN Repository

["Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>]

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 bs
 WHERE 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@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 !!!!

***************************************************************************
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-l

• References:
  • Query RMAN Repository
    • From: Raj Mareddi
  • Re: Query RMAN Repository
    • From: Christo Kutrovsky

Other related posts:

Query RMAN Repository

RE: Query RMAN Repository

Re: Query RMAN Repository

RE: Query RMAN Repository