Re: archive logs grow too fast

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 27 Feb 2003 07:13:59 -0800
Message-ID: <2687bb95.0302270713.7a8cc537@posting.google.com>

usenet_at_sein.sportwetten.spb.ru (Oleg Paraschenko) wrote in message news:<70f723dc.0302262137.514b54ab_at_posting.google.com>...
> Hello all,
>
> I'm interesting if there are method to calculate disk space required
> for archive logs. High accuracy is not required. I will be grateful
> for any recommendations how to start to investigate what and why I
> have in archive logs.
>
> Regards, Oleg

Oleg, if you system is already built and in archive log mode you could query v$archived_log and calculate how much data you are writing. If your system is in noarchivelog mode then this veiw is empty, but you can count the number of log switches in the alert_sid.log and multiple by the defined log size.

set echo on
select
  sum(blocks)
from
  v$archived_log
where trunc(completion_time) = trunc(sysdate - 1) /

SUM(BLOCKS)

---

     705994

select 705994 * 512 from dual
/

705994*512

---

 361468928

The block size came from v$archived_log.

If your system does not yet exist then you have to resort to estimating how much DML activity your database is going to experience.  In general I want to size my redo logs such that 24 to 48 logs per day are archived. That is an average of 1 every half-hour to hour.

HTH -- Mark D Powell -- Received on Thu Feb 27 2003 - 09:13:59 CST