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.
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)
How to estimate the size of an RMAN database backup (Doc ID 1274720.1)
A Complete Understanding of RMAN Compression (Doc ID 563427.1)
