List the most recent FULL Database backup with STATUS=’COMPLETED’

select a.db_name Database,
db.dbid DBID,
a.end_time Latest_Backup,
round(a.output_bytes/1000000000) GBytes_Processed,
round((end_time – start_time) * 60 * 24) Minutes
from rc_rman_status a, rc_database db
where object_type in (‘DB FULL’,'DB INCR’)
and status = ‘COMPLETED’
and operation = ‘BACKUP’
and a.db_name=’&&DB_NAME’
and end_time in (select end_time from rc_rman_status b
where b.db_name = a.db_name
and b.db_key = a.db_key
and object_type in (‘DB FULL’,'DB INCR’)
and status = ‘COMPLETED’
and operation = ‘BACKUP’)
and db.db_key = a.db_key
order by a.db_name,end_time ;

Output would be in format …..
Database DBID Latest Ba GBytes Processed Minutes Taken
——– ———- ——— —————- ————-

TYPES of BACKUPS

DATAFILE FULL

CONTROLFILE

ARCHIVELOG

DB FULL

Following script will list Backups for a Specific Database Instance ‘&&DB_NAME’

select a.db_name “Database”,
db.dbid “DBID”,
a.end_time “Latest Backup”,
round(a.output_bytes/1000000000) “GBytes Processed”,
round((end_time – start_time) * 60 * 24) “Minutes Taken”
from rman.rc_rman_status a, rman.rc_database db
where object_type in (‘DB FULL’,'DB INCR’)
and status = ‘COMPLETED’
and operation = ‘BACKUP’
and a.db_name=’&&DB_NAME’
and end_time in (select end_time from rman.rc_rman_status b
where b.db_name = a.db_name
and b.db_key = a.db_key
and object_type in (‘DB FULL’,'DB INCR’)
and status = ‘COMPLETED’
and operation = ‘BACKUP’)
and db.db_key = a.db_key
order by a.db_name,end_time ;

List Archivelog backups

select a.db_name “Database”,
db.dbid “DBID”,
a.end_time “Latest Backup”,
round(a.output_bytes/1000000) “MBytes Processed”,
round((end_time – start_time) * 60 * 24) “Minutes Taken”
from rman.rc_rman_status a, rman.rc_database db
where
object_type =’ARCHIVELOG’
and status = ‘COMPLETED’
and operation = ‘BACKUP’
and a.db_name=’&&DB_NAME’
and end_time in (select end_time from rman.rc_rman_status b
where b.db_name = a.db_name
and b.db_key = a.db_key
and object_type =’ARCHIVELOG’
and status = ‘COMPLETED’
and operation = ‘BACKUP’)
and db.db_key = a.db_key
order by a.db_name,end_time ;

List all Database FULL backups done between 1-May-2008 and 30-May-2008

select substr(to_char(START_TIME,’DD-MON-YYYY HH24:MI’),1,20),substr(OBJECT_TYPE,1,20)
from rman.rc_rman_status where start_time > sysdate -30 and object_type=’DB FULL’
order by 1 desc;

Is RMAN Backup Still Running:

SELECT to_char(start_time,’DD-MON-YY HH24:MI’) “BACKUP STARTED”,
sofar, totalwork,
elapsed_seconds/60 “ELAPSE (Min)”,
round(sofar/totalwork*100,2) “Complete%”
FROM sys.v_$session_longops
WHERE compnam = ‘dbms_backup_restore’
BACKUP STARTED,SOFAR,TOTALWORK,ELAPSE (Min),Complete%
27-JUN-07 09:54,755,45683,2.73333333333333,1.65
27-JUN-07 09:52,1283,10947,4.36666666666667,11.72
27-JUN-07 09:46,11275,11275,0.783333333333333,100
27-JUN-07 09:46,58723,58723,5.73333333333333,100
27-JUN-07 09:46,12363,12363,0.333333333333333,100
27-JUN-07 09:44,11115,11115,4.53333333333333,100
27-JUN-07 09:44,12371,12371,0.183333333333333,100
27-JUN-07 07:34,4706,4706,0.166666666666667,100
27-JUN-07 07:34,83729,83729,118.35,100
27-JUN-07 05:21,8433,8433,0.333333333333333,100
27-JUN-07 05:21,83729,83729,132.25,100