List of tapes needed for restoring DB from a backup

My restore job hangged for hours before I found that none of the needed tapes were in the tape drive (library). When I requested tpae Operations to bring the tapes back from offsite, they asked me for the list of tapes needed to recovering the database.

This query on the RMAN catalog database helps to answer the question, but you might check with Tape administration to confirm the list:

select distinct media, to_char(start_time, 'MM-DD-YY')
from rc_backup_piece b, rc_database d
where b.db_id = d.dbid and d.name='PDB'
and b.start_time > sysdate - 3
order by to_char(start_time, 'MM-DD-YY');

Here is another query for getting details on tape speed (in 10G):

select db_id, media, start_time, completion_time, status, elapsed_seconds,
bytes, (bytes /(1024*1024))/(elapsed_seconds/60) MB_PER_MINS
from rc_backup_piece b, rc_database d
where b.db_id = d.dbid and d.name='PDB'
and b.start_time > sysdate - 3
-- and status != 'A'
order by to_char(start_time, 'MM-DD-YY');

If the date is beyond the RMAN Redundancy period of the database, you may not get the tape list from above views. You have to run a query on the target database itself as DBA:

select distinct SYS_CONTEXT('USERENV','DB_NAME') DB, to_char(start_time, 'MM-DD-YY') bk_date, media tape
from v$backup_piece
where start_time > sysdate - 13
order by to_char(start_time, 'MM-DD-YY') desc
;

Note if you go back too many days, the tape may be overwritten by the tape retention policy which makes the restore impossible.