RMAN Backup Details By Day

-- ----------------------------------------------
--
-- Script to run against actual database to look at the backup piece handle by tag for a particular day
--
--
--
-- ----------------------------------------------


col handle format a87
col STRT format a20
col "Inc Level" format a8
col STATUS format a10
col DEVICE_TYPE format a8
col NP noprint
col HN format a25



select
 A.RECID NP,
 to_char(A.START_TIME,'DD-MON-YY HH24:MI:SS')Strt
 , decode(B.INCREMENTAL_LEVEL,'0','FULL',B.INCREMENTAL_LEVEL) "Inc Level"
 , decode(A.STATUS,'A','Available','D','Deleted','X','Expired',A.status) STATUS
--  , SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)) HN
-- ,SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))
 , SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) HN
 , A.DEVICE_TYPE
 , DECODE(B.BACKUP_TYPE,'D','full backup','L','archived redo log','I','I,incremental level 1') B_TYPE
 , A.tag
 , round(A.BYTES/1048576,2) MB
 from v$backup_piece A,  v$backup_set B
 where a.SET_STAMP = b.SET_STAMP
 and a.SET_COUNT = b.SET_COUNT
 --
 -- Enter DATE below e.g. 02-OCT-13
 --
 and to_char(A.START_TIME,'DD-MON-YY')='&Date'
--  and A.DEVICE_TYPE = 'DISK'
order by SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) --A.RECID, A.COMPLETION_TIME, A.START_TIME
/