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
( select set_count, set_stamp, session_recid, session_stamp
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, bp.media
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#;
Oracle Database Administrator
tel +32(0)3 451 23 82http://www.uptime.be
From: oracle-l-bounce_at_freelists.org 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?