/********************************************************************
 * File:	rman_most_recent.sql
 * Type:	SQL*Plus script
 * Author:	Brian Minor (Great-West Life and Annuity)
 * Date:	25-Sep 2006
 *
 * Description:
 *
 * Modification:
 *	BMinor	25sep06	offered to Tim Gorman for posting and usage...
 ********************************************************************/
column completed format a16 justify center
column Database format a8
set pagesize 9000
set tab off
column dbid format a12 justify center
alter session set nls_date_format = 'DD-MON-YY HH24:MI';
spool rman_most_recent

select	f.Database,
	dbid,
	completed "Last DB Backup",
	' ' || g.arctime "Last Arc Backup"
from	(select	Database,
		db_key,
		lpad(' ',(10-length(dbid))/2 )|| dbid as dbid,
		completed
	 from	(select	a.db_name Database,
			b.db_key,
			b.dbid,
			a.completed
		 from	(select	db_name,
				max(completion_time) completed
			 from	rc_backup_datafile
			 group by db_name) a,
         		rc_database b
		 where	a.db_name = b.name)
         order by 3 desc) f,
	(select	max(completion_time) arctime,
		db_key
	 from	bs
	 where	bck_type = 'L'
	 group by db_key) g
where	f.db_key = g.db_key (+)
order by 3 desc;
spool off