Blog

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 :

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;

So here are the detailed commands for checking archive generation that may help to troubleshoot your performance issues too.

Also, it can happen due to some other reasons like internal and commit operations, etc. You can take further reference from below Oracle Doc too.

Excessive Archive Log Generation with INTERNAL and COMMIT operations (Doc ID 1070861.1)