How to get archivelog generated based on AWR reports/repository?

Question

We have an application that gets busy during a month in a year. We have enabled awr repository period to 360 days to make sure we store the performance statistic information for analyzing later. Recently, we have an requirement to plan for standby database and for that we need to determine how many archivelogs generated during busiest month(which was 6 months ago) so that we can calculate the required bandwidth needed between the primary and standby location.

We cannot get the archivelogs details from v$loghistory as we don't have the information that long ago. So since we have AWR information, we can generate AWR reports but how do we find out the archivelog generation rate from it?

Answer 1

You can use DBA_HIST_SYSMETRIC_HISTORY to find the amount of redo generated. This should be good enough, although it won't generate the exact number. There will be some extr aredo that hasn't been archived yet, and the number may need to be multiplied to account for multiplexing.

select
    to_char(begin_time, 'YYYY-MM') year_and_month,
    round(sum(seconds*value)/1024/1024/1024, 1) gb_per_month
from
(
    select begin_time, (end_time - begin_time) * 24 * 60 * 60 seconds, value
    from dba_hist_sysmetric_history
    where metric_name = 'Redo Generated Per Sec'
)
group by to_char(begin_time, 'YYYY-MM')
order by year_and_month;