How to Check Archive Generation Rate in Oracle

As a DBA, we often need to find what is the archive generation rate as a part pf the Basic Health checkups in Oracle. Today we will discuss it.

Importance to Check Archive Generation Rate :

Sometimes, we may encounter the RECO disk group getting full or the archive mount point is getting full all of a sudden. In that situation, we need to take action and first check why it happened. In most cases, we may find the archive generation got high all of a sudden due to some Application job or some Change activity. So, let’s go through how to check it in detail.

Hourly Archive Generation Day wise:

set lines 400 
set pages 400
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) Archives_Size_In_GB,
count(*) No_Of_Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Output will be as below :

How to Check Archive Generation Rate in Oracle:

Hourly Archive Generation Rate :

set pages 300
set lines 400
set long 9999
col HOUR for a10
col DATE for a20
select count(*) No_Of_Archives ,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) Archive_Size_In_GB, to_char(next_time,'mm/dd/yy') "DATE" ,to_char(next_time,'hh24') "HOUR"
from v$archived_log
group by to_char(next_time,'mm/dd/yy'),to_char(next_time,'hh24') order by to_char(next_time,'mm/dd/yy');

Daily Archive Generation :

      select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) Archives_Size_In_GB,
count(*) No_Of_Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;