Finding Backup details from 11g RMAN respository tables

• List databases registered with rman repository

select NAME
     , DBID
  from RC_DATABASE;

• List rman backup STATUS details with database name, Start time, End time , Processed MB/GB

SELECT db_name, row_type, start_time, end_time, operation, status, mbytes_processed
    FROM   rc_rman_status
    WHERE  db_name like  ‘PROD%’
      AND  start_time > SYSDATE – 1
    ORDER  BY END_TIME desc;

• List backup details of all registered RMAN databases 

SELECT  db_name
      , start_time
      , row_type
      , end_time
      , operation
      , status
      , mbytes_processed
   FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  ORDER BY DB_NAME
  , start_time;

• Show backups which are still RUNNING

SELECT  db_name
      , start_time
      , row_type
      , operation
      , status
  FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  AND STATUS = ‘RUNNING’
  ORDER BY DB_NAME
  , start_time;

• List db name, tablespace & datafiles backed up with size

select DB_NAME
     , TABLESPACE_NAME
     , FILE#
     , BYTES/1000000000 SIZE_GB
     , NAME
  from RC_DATAFILE
 where DB_NAME = ‘PROD’;

• List RMAN backup configuration Parameters

select
DB_UNIQUE_NAME
,NAME
,VALUE         
from RC_RMAN_CONFIGURATION
 where DB_UNIQUE_NAME =’PROD’
order by 1,2,3;

ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE      DISK TO 1
ARCHIVELOG DELETION POLICY                    TO NONE
BACKUP OPTIMIZATION                           OFF
CHANNEL                                       DEVICE TYPE DISK FORMAT   '/mnt/backup/Rman/PROD1/%d_%s_%p_%U'
CONTROLFILE AUTOBACKUP                        ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/Rman/PROD1/%F'
DATAFILE BACKUP COPIES FOR DEVICE TYPE        DISK TO 1
DEVICE TYPE                                   DISK PARALLELISM 3 BACKUP TYPE TO COMPRESSED BACKUPSET
MAXSETSIZE TO                                 UNLIMITED
SNAPSHOT CONTROLFILE NAME                     TO '/opt/app/oracle/product/11.2/db_1/dbs/snapcf_RMANTST.f'  

• 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 desc;

• 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 desc;

DATABASE       DBID LATEST_BA GBYTES_PROCESSED MINUTES_TAKEN
-------- ---------- --------- ---------------- -------------
PROD   4020163152 22-SEP-11                0            20
PROD   4020163152 21-SEP-11                0            19
PROD   4020163152 20-SEP-11                0            17
PROD   4020163152 19-SEP-11                0            16
PROD   4020163152 18-SEP-11                4            13
PROD   4020163152 17-SEP-11                0            15

• 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 desc;

• List all Database FULL backups done in last 30 days

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 opname = ‘dbms_backup_restore’;