Achive log generation per day oracle

Sometimes we may face issues like since there was high number of archive log generation; the filesystem may get full. This will result into non functioning of database.

It may happen that a database is generating 35 archives log on weekdays and more than 100 archive logs weekends due to heavy transactions on weekend. If we have the archive log generation data handy we can plan our action accordingly.

By using this query we can get the trend of the number of archives logs generated on per day basis, minimum and maximum sequence of the archive log and physical space occupied at OS level in megabytes(MB).
Note: We can get the actual size of archive log generated only if we have not specified any value to ARCHIVE_LAG_TARGET.

Here the query goes as :

select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc


Example:

SQL> select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc;

ON_DATE        THREAD MIN_SEQUENCE MAX_SEQUENCE NOS_ARCHIVES REQ_SPACE_MB
---------- ---------- ------------ ------------ ------------ ------------

06/05/2013          1           23           28            5          625
06/08/2013          1           29           29            0            0
06/09/2013          1           30           30            0            0
06/10/2013          1           31           31            0            0
06/11/2013          1           32           32            0            0
06/12/2013          1           33           33            0            0
06/13/2013          1           34           42            8         1000
06/14/2013          1           43           83           40         5000
06/15/2013          1           84           84            0            0
06/16/2013          1           85           85            0            0
06/17/2013          1           86           86            0            0
06/18/2013          1           87          158           71         8875
06/19/2013          1          159          159            0            0
06/21/2013          1          160          183           23         2875


In the above example, we can identify that on 18th June 2013; 8875 MB of achive log was generated. The minimum sequence was 87 and maximum sequence was 158.
Total 71 number of archive logs generated.

Few people may get output only for last seven, simultaneously few can get the output for as long as 5 years. This all depends upon the size of the control file.

Hope this helps some way to the DBA's. Good luck.