RMAN Backup Information

Script – Check RMAN Backup Status

To get the details of the backup with the time stamp before launching the rman set this environment  variable 

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

RMAN> List  backup summary completed before "to_date('30-MAY-2014 21:00:00','DD-MON-YYYY HH24:MI:SS')";

using target database control file instead of recovery catalog

List of Backups

===============

Key     TY LV S Device Type Completion Time      #Pieces #Copies Compressed Tag

------- -- -- - ----------- -------------------- ------- ------- ---------- ---

21873   B  F  A DISK        30-MAY-2014 00:12:48 1       1       YES        SRD_FULL_COMP

21874   B  F  A DISK        30-MAY-2014 00:13:09 1       1       YES        SRD_FULL_COMP

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

The below script will tell you STATUS, the START_TIME, END_TIME for the RMAN backups

col RMAN_Status FORMAT A20 heading "Status"
col INPUT_TYPE  FORMAT A15 heading "Backup Type"
col Hrs         FORMAT 999.99 heading "Backup Time"
col Start_Time  FORMAT A20 heading "Backup Start Time"
col End_Time  FORMAT A20 heading "Backup End Time"

SELECT SESSION_KEY "Backup Session ID", INPUT_TYPE, 
       STATUS           RMAN_Status,
       TO_CHAR(START_TIME,'DY mm/dd hh24:mi') Start_Time,
       TO_CHAR(END_TIME,'DY mm/dd hh24:mi')   End_Time,
       ELAPSED_SECONDS/3600                   Hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY desc;

The below script displays the percentage of backup completed or inprogress

SELECT 
USERNAME,
SID,
SERIAL#,
CONTEXT, 
SOFAR, 
TOTALWORK,
TIMESTAMP,to_date(START_TIME,'dd-mon-yyyy HH24:MI:SS'),
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK

Figure out if you data files are in synchronization with the control files.

select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY ‎HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;

Find the backup size of your database

select ctime "Date"  
 , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type  
 , bsize "Size GB"  
 from (select trunc(bp.completion_time) ctime  
 , backup_type  
 , round(sum(bp.bytes/1024/1024/1024),2) bsize  
 from v$backup_set bs, v$backup_piece bp  
 where bs.set_stamp = bp.set_stamp  
 and bs.set_count  = bp.set_count  
 and bp.status = 'A'  
 group by trunc(bp.completion_time), backup_type)  
 order by 1, 2;