Monitoring redo generation

Sometimes I am checking how much redo is generated during the day to know if I have enough space in the arch destination during reorgs.

To check redolog generation I use:

break on SUBSTRING skip 1
compute sum label 'TOTAL' of MB on SUBSTRING
set lines 180 pages 999

select count(1) "Rate" ,trunc(FIRST_TIME,'HH24') "By Hour",round(sum(blocks*block_size)/1024/1024,2) MB, trunc(FIRST_TIME,'DD') as substring
from v$archived_log where dest_id=1 group by trunc(first_time,'HH24'),trunc(FIRST_TIME,'DD') order by 2 ;


PROMPT Max redo in MB
with max_day as (
select trunc(FIRST_TIME,'DD') as day from v$archived_log group by trunc(FIRST_TIME,'DD') having count(*)=(select max(cnt) from (select count(*) cnt,trunc(FIRST_TIME,'DD') from v$archived_log group by trunc(FIRST_TIME,'DD')))
select sum(blocks*block_size)/1024/1024 "MB per Max Day", from v$archived_log,max_day where trunc(FIRST_TIME,'DD') and dest_id=1 group by;

Sometimes you have to decide how much storage do you need for a database. You are not an oracle who knows how the application works, about the workload and other topics. Developers should say how much is needed in the beginning. After some time it’s possible even for you:

  • We add 25% more storage, it should be enough for 24 hours.

with l as (select distinct bytes/1024/1024/1024 as siz from v$log),
c as (select max(cnt) cntl from (select count(1) cnt,trunc(FIRST_TIME,'DD') sdate from v$archived_log where dest_id=1 group by trunc(FIRST_TIME,'DD')))
select l.siz*c.cntl*1.25 "Size for arch dest for 24h" from l,c ;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s