Monitoring Historical Generation of Archivelogs

 
Production database environments usually have scheduled cyclic backups for archivelogs to avoid Full archivelog destination issues. But in some cases, sudden spikes of archivelogs being generated by the database will breach the destination threshold before backup can execute (RMAN backups with delete option will delete archivelogs that have been applied to the database).

The script below will be useful to determine historical archivelog generation to understand database usage trends for escalation to development/application team.

select to_char(first_time,'yyyy-mm-dd'),
round(sum(blocks*block_size)/1024/1024/1024,2) archive_generated_in_GB
from v$archived_log
group by to_char(first_time,'yyyy-mm-dd')
order by 1;

The code below is useful for debugging standby databases where archivelog backups are not being deleted due to a DELAY parameter (in minutes). We use completion_time column to identify when the archivelog backups arrived at the standby site. The timer for DELAY parameter only starts once archivelogs have arrived at the standby site.

select to_char(completion_time,'yyyy-mm-dd'),
round(sum(blocks*block_size)/1024/1024/1024,2) archive_generated_in_GB
from v$archived_log
group by to_char(completion_time,'yyyy-mm-dd')
order by 1;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart