Database views for backuppiece info

1. On the RMAN catalog database
SELECT bp_key backuppiece_key, bs_key backupset_key, recid, set_count, incremental_level, handle, media, completion_time, status,
decode(backup_type, 'D', 'FULL', 'I', 'INCREMENTAL', 'L', 'LOGS') type
FROM rc_backup_piece
WHERE db_key = (select max(db_key) FROM rc_database WHERE name = 'PDWS')
-- and recid=2876
-- and status = 'X'
ORDER BY completion_time desc
;

After you get the backupset_key from this query, you can use "RMAN> list backupset backupset_key" or "RMAN> list backuppiece backuppiece_key" to see its contents (data files and/or archivelogs).

Once a backup piece has been deleted by "delete expired backup" and "delete obsolete", its record will be removed from this view and so all information on the backup piece in RMAN will be gone.

View RC_BACKUP_SET gives similar information, but is less useful.

2. On the target database
SELECT recid, set_count, handle, status, tag, media, completion_time, deleted
FROM v$BACKUP_PIECE
-- where recid=2876
order by completion_time desc;

This view keeps more records (maybe three months). But, after a backup piece has been deleted by "delete expired backup" and "delete obsolete", the HANDLE column of its record will become blank. So it is only useful if you want to find the tape ID from the MEDIA column.