RE: Does the Rman Catalog hold tape volume names?

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Tue, 10 Feb 2009 15:27:35 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA264_at_EXCNYSM0A1AJ.nysemail.nyenet>


Thank You! I'll let you know if this works. My SA is away from his desk right now. When he gets back we'll take a look.

Thanks again Stranger.


-----Original Message-----
From: D'Hooge Freek [] Sent: Tuesday, February 10, 2009 3:19 PM To: Mercadante, Thomas F (LABOR); Subject: RE: Does the Rman Catalog hold tape volume names?


There is indeed a media column, that can hold the tape name(s) on which the backup piece is stored.
But I'm not sure that every rman media layer is capable of passing this information to rman.
Following query can be used in 10gR2 to get an overview of the backups and the media used.
It uses the session_ke and session_stamp from v$rman_backup_job_details to group the different backup sets that belong to the same backup job together

set linesize 200

column bjob format a15
column recid format 99999
column start_time format a20
column handle format a55
column mb format 9G999G999D99
column media format a15

break on bjob skip 1 on recid on backup_type on start_time

compute sum of mb on bjob

with backup_set_details
( select set_count, set_stamp, session_recid, session_stamp   from v$backup_set_details
  group by set_count, set_stamp, set_count, session_recid, session_stamp )
select rbjd.session_key ||',' || rbjd.session_stamp bjob, bs.recid recid, bs.backup_type,

       to_char(bs.start_time, 'DD/MM/YYYY HH24:MI:SS') start_time,
       bp.piece#, bp.bytes/1024/1024 mb, bp.compressed, bp.handle,
from v$backup_set bs, v$backup_piece bp, backup_set_details bsd, v$rman_backup_job_details rbjd
where bs.set_stamp = bp.set_stamp
      and bs.set_count = bp.set_count
      and bs.set_stamp = bsd.set_stamp
      and bs.set_count = bsd.set_count
      and bsd.session_recid = rbjd.session_recid
      and bsd.session_stamp = rbjd.session_stamp
      and bp.status != 'D'

order by bs.start_time, bs.recid, bp.piece#;


Freek D'Hooge
Oracle Database Administrator
tel +32(0)3 451 23 82

[] On Behalf Of Mercadante, Thomas F (LABOR)
Sent: dinsdag 10 februari 2009 21:02
Subject: Does the Rman Catalog hold tape volume names?


I'm sure I saw this answer on the list before but just can't remember it.

Does the Rman catalog store the tape volume name that it is writing a backup to?

Oracle 10.2 and Tivoli Storage Manager.

I am trying to restore from a prior backup. The Rman catalog has the backup cataloged. But the Rman run fails and says:

RMAN-06023: no backup or copy of datafile 22 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

I was able to restore from 2 days sooner (1/29 worked; 1/27 does not). I'm thinking that the tape may be offline or off site.

Does this make sense?



Received on Tue Feb 10 2009 - 14:27:35 CST

Original text of this message