Rman - Quering catalog

Remember:
obsolete means "not needed" expired means "not found"

select * from V$RMAN_BACKUP_JOB_DETAILs
select * from V$RMAN_BACKUP_TYPE;
select output from v$rman_output

An overview of backupset
Control file
select * from v$backup_set
where completion_time > sysdate-7
order by completion_time desc
Catalog
select * from rc_backup_set
where completion_time > sysdate-7
order by completion_time desc
RMAN
list backupset summary COMPLETED AFTER 'SYSDATE - 2';
List of Backups
===============
Key     TY LV S Device Type Completion Time    #Pieces #Copies Tag
------- -- -- - ----------- ------------------ ------- ------- ---
569686  B  F  A SBT_TAPE    04-AUG-05 02:27:19 1       1       TAG20050804T021508
569687  B  F  A SBT_TAPE    04-AUG-05 02:28:39 1       1       TAG20050804T021508
569844  B  A  A SBT_TAPE    04-AUG-05 09:02:30 1       1       TAG20050804T090036
569845  B  A  A SBT_TAPE    04-AUG-05 09:02:48 1       1       TAG20050804T090036
#LV: F=full, A=archive log

The last archived logs backed up to RMAN
No rows means db is not backed up
Control file
select * from v$backup_redolog
where next_time > sysdate-7
order by first_time desc
Catalog
--set until scn NEXT_CHANGE
select * from rc_backup_redolog
where next_time > sysdate-7
order by first_time desc
RMAN
list archivelog all backed up 1 times to device type sbt;

The archived logs all
Control file
select * from v$archived_log
order by first_time desc
Catalog
select * from rc_archived_log
order by first_time desc
RMAN
list archivelog all;

Archived logs not backed up
Control file
select v$archived_log.*
from v$archived_log
where sequence# > nvl(( --nvl is used if rc_backup_redolog is empty
  select sequence# from (
    select sequence# from v$backup_redolog
    order by first_time desc
  ) where rownum = 1
) ,sequence#-1)
and first_time > nvl((  --nvl is used if rc_backup_redolog is empty
  select first_time from (
    select first_time from v$backup_redolog
    order by first_time desc
  ) where rownum = 1
), first_time-1)  
order by first_time desc
Catalog
select * from rc_archived_log
where sequence# > nvl(( --nvl is used if rc_backup_redolog is empty
  select sequence# from (
    select sequence# from rc_backup_redolog
    order by first_time desc
  ) where rownum = 1  
),sequence#-1)
and first_time > nvl((  --nvl is used if rc_backup_redolog is empty
  select first_time from (
    select first_time from rc_backup_redolog
    order by first_time desc
  ) where rownum = 1
), first_time-1) 
order by first_time desc
RMAN
#First see the last archive logs backed up
list archivelog all backed up 1 times to device type sbt;
List of Archived Log Copies
Key     Thrd Seq     S Low Time           Name
------- ---- ------- - ------------------ ----
570113  1    161995  A 05-AUG-05 06:45:28 /arch_aol/archive/arch_AOL_PRO_0000161995.log
570119  1    161996  A 05-AUG-05 06:45:59 /arch_aol/archive/arch_AOL_PRO_0000161996.log
570122  1    161997  A 05-AUG-05 09:00:43 /arch_aol/archive/arch_AOL_PRO_0000161997.log
#Then see the remaining archive log not yet backed up
list archivelog from sequence 161998;
List of Archived Log Copies
Key     Thrd Seq     S Low Time           Name
------- ---- ------- - ------------------ ----
570241  1    161998  A 05-AUG-05 09:00:49 /arch_aol/archive/arch_AOL_PRO_0000161998.log

Archives backed up more than once (from controlfile)
select sum(num_doppioni_inutili) num_doppioni_inutili
, sum(size_backup_inutile)/1024/1024/1024 size_backup_inutile 
from (
	select a.*
	, size_archive * (num_doppioni - 1) size_backup_inutile
	, (num_doppioni - 1) num_doppioni_inutili from (
		select 
		sequence#, first_time, count(*) num_doppioni, (blocks * block_size) size_archive
		from v$backup_redolog
		where next_time > sysdate-30
		group by sequence#, first_time, blocks , block_size
		having count(*)>1
		order by sequence# desc
	) a
) b

Caution! In RMAN catalog structure the minimum field is a backup set.
In this environment i suppose a backup controlfile is performed for each full.

Complete backups summary
Control file
select * from v$backup_set
where backup_type = 'D'
and controlfile_included = 'YES'
order by completion_time desc
Catalog
select * from rc_backup_set
where backup_type = 'D'
and controlfile_included = 'BACKUP'
order by completion_time desc

The minimum archivelog needed
catalog
--select sum(MB) from (
select a.* from (
  select sequence#, first_change#, first_time, next_change#, next_time, 
  to_date('07/04/07 00:47:49', 'dd/mm/yy hh24:mi:ss') backup_start_time,
  to_date('07/04/07 04:13:35', 'dd/mm/yy hh24:mi:ss') backup_end_time,
  blocks*block_size / 1024/1024 MB
  from rc_backup_redolog
) a
where not (first_time < backup_start_time and next_time < backup_start_time)
and not (first_time > backup_end_time and next_time > backup_end_time)
order by first_time desc
--)

Monitor progress of a backup
SELECT SID, serial#, CONTEXT, sofar, totalwork,
       ROUND (sofar / totalwork * 100, 2) "%_COMPLETE", opname
  FROM v$session_longops
 WHERE sofar <> totalwork AND totalwork != 0 AND opname LIKE 'RMAN%'  
  
SELECT   *
    FROM bp
   WHERE completion_time BETWEEN (SELECT completion_time
                                    FROM bp
                                   WHERE handle = 'dbaseGEA_PRO_bqhvh8bg')
                             AND (SELECT completion_time
                                    FROM rc_backup_redolog
                                   WHERE next_change# = 6927237507686)
     AND handle LIKE 'arch_%'
ORDER BY start_time

Usefull functions

See current SCN
select * from (
  select status, current_scn, 
    lead(sequence#, 1, null) OVER (ORDER BY rownum) sequence#,
    lead(first_change#, 1, null) OVER (ORDER BY rownum) first_change#,
    lead(next_change#, 1, null) OVER (ORDER BY rownum) next_change# 
  from ( 
    select 
    status, current_scn, 
    null sequence#, null first_change#, null next_change# from v$database, v$instance
    union all
    select
    null, null, sequence#, first_change#, next_change#
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log)
  ) a
) where not current_scn is null