Estimate RMAN Backup Size

I got a task to estimate RMAN backup size.

How much 3days RMAN backup occupy?

How long it will take to complete?

Is the backup size is reasonable?

Is there a way to reduce the backup size?


I have used the below query to find the size and time taken for backup.

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from v$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     v$BACKUP_SET_DETAILS d
                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from Gv$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/

Enter value for number_of_days: 1                                                                                                                                                           
                                                                                                                                                           
                                            OUTPUT                                       ELAPSED TIME                                            OUT       
START_TIME          END_TIME                MBYTES STATUS     INPUT_TYPE    DOW          SECONDS TAKEN          CF     DF     I0     I1      L  INST       
------------------- ------------------- ---------- ---------- ------------- --------- ---------- ---------- ------ ------ ------ ------ ------ -----       
2014-06-28 13:18:05 2014-06-28 13:18:08     13,512 FAILED     DB FULL       Saturday           3 00:00:03        1      0      0      0      0     1       
2014-06-28 13:26:11 2014-06-28 13:26:15     11,348 FAILED     DB FULL       Saturday           4 00:00:04        1      0      0      0      0     1       
2014-06-28 17:30:58 2014-06-28 19:07:44     21,370 COMPLETED  DB FULL       Saturday        5806 01:36:46        3      4      0      0      0     1       
2014-06-28 19:08:09 2014-06-28 19:08:16          0 FAILED     ARCHIVELOG    Saturday           7 00:00:07                                          1       
2014-06-28 20:26:00 2014-06-28 20:51:46      5,112 COMPLETED  ARCHIVELOG    Saturday        1546 00:25:46        1      0      0      0      4     1 

This is how estimated the size of the backup from Database.

SQL> select sum(bytes)/1024/1024 from v$datafile;                                                                                                          
                                                                                                                                                           
SUM(BYTES)/1024/1024                                                                                                                                       
--------------------                                                                                                                                       
             50387.5                                                                                                                                       
                                                                                                                                                           
SQL> select sum(bytes)/1024/1024 from dba_free_space;                                                                                                      
                                                                                                                                                           
SUM(BYTES)/1024/1024                                                                                                                                       
--------------------                                                                                                                                       
          23501.5625                                                                                                                                       
                                                                                                                                                           
SQL>                           

50387.5 - 23501.5625 = 26885.9375 = 26GB

I reviewed the below notes for the same.
How to estimate the size of an RMAN database backup (Doc ID 1274720.1)
A Complete Understanding of RMAN Compression (Doc ID 563427.1)