RE: Query RMAN Repository
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <yoursraju007@xxxxxxxxx>
- Date: Thu, 6 Mar 2008 18:03:51 -0600
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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
