RMAN Tape Media Detection

Identify backupset name from RMAN repository database. This method uses RMAN repository database

Run following RMAN commands and obtain the backupset number:

1. list backup of database summary completed after 'sysdate-1';
2. list backup of controlfile summary completed after 'sysdate-1';
3. list backup of archivelog all summary completed after 'sysdate-1';
4. list backup of spfile summary completed after 'sysdate-1';

Login to RMAN repository database and run following query to obtain the tape media label. Simple put all the backupset number into the query. This query contain some examples, which taken from database, controlfile, archivelog backup

col tag format a30
col media format a8
col handle format a45
select bs_key, handle, media, tag
from rc_backup_piece where bs_key in
(340870, 340872, 341596, 341597, 342398, 342399);

Following is another query obtain by tag used:

col tag format a30
col media format a8
col handle format a45
select bs_key, handle, media, tag
from rc_backup_piece where tag in (
'ICCS1-LVL0_20090412',
'ICCS1-LVL1_20090413',
'ICCS1-LVL1_20090414',
'ARC_20090414',
'ARC_20090415')
or bs_key = 344148;

    BS_KEY HANDLE                                     MEDIA    TAG
---------- ------------------------------------------ -------- ------------------------------
   342283 ICCS_18868_1_dkkcdvjn_1_1_20090414.arc     J00423   ARC_20090414
   342284 ICCS_18867_1_djkcdvjn_1_1_20090414.arc     J00563   ARC_20090414
   343419 ICCS_18894_1_eekcgjvp_1_1_20090415.arc     J00206   ARC_20090415
   343420 ICCS_18893_1_edkcgjvp_1_1_20090415.arc     J00023   ARC_20090415
   343895 ICCS_18905_1_epkchg3k_1_1_20090415.arc     J00286   ARC_20090415
   343896 ICCS_18904_1_eokchg3k_1_1_20090415.arc     J00023   ARC_20090415
   340870 ICCS_18818_1_c2kc8sto_1_1_20090412.bkp     J00301   ICCS1-LVL0_20090412
   340872 ICCS_18817_1_c1kc8stn_1_1_20090412.bkp     J00631   ICCS1-LVL0_20090412
   344148 c-1233997722-20090415-04                   J00106

Where in Oracle Database 9i, controlfile autobackup does not produce a tag, so it is obtained from RMAN command "list backup of controlfile;"

These tag is produced using following RMAN backup script (Linux):

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
DATE=$(date +"%Y%m%d")
export ORACLE_SID=iccs
BACKUP_TAG=BACKUP_TAG=` hostname -s `"-${4}" # Param 4 is user custom tag
rman target / << EOF
connect catalog iccs_rman@rcat11g
set echo on;
show all;
run {
set command id to 'iccs_backup.sh';
allocate channel t1 type sbt_tape;
backup
incremental level 0
database
format '%d_%s_%p_%U_%T.bkp'
tag '${BACKUP_TAG}_${DATE}'
current controlfile tag 'Ctrl_${DATE}'
plus archivelog delete input
format '%d_%s_%p_%U_%T.arc' tag 'Arc_${DATE}'
;
release channel t1;
}
crosscheck backup completed before 'sysdate-31';
delete noprompt obsolete;
delete noprompt expired backup;
exit;
EOF
Above SQL query does not support obsolete (exceed retention day), and expired (removed from tape, or disk)