Re: Huge generation of archivelog: how to tweak that ?

From: ddf <>
Date: Tue, 4 Aug 2009 05:20:00 -0700 (PDT)
Message-ID: <>

Comments embedded.

On Aug 4, 12:11 am, Xavier Maillard <> wrote:
> Hi,
> we are creating standby databases (physical) for several
> databases (9i). All in all it works perfectly except one thing:
> for one of them archivelog generation is totally out of control.

I think it's not out of control, it's simply far more than you expected.

> We have multiplexed archivelog destinations sized at 8Gb (which,
> based on our estimations was something unlikely to happen below
> one full production day). Today, these 8Gb are hitten in *one*
> hour only; to be more precise, this happens for at least one
> program: a purge.

One should expect that, depending upon the volume of data 'purged'.

> Today, this is a no-go for our whole dataguard platform since
> every hour we must delete manually archive logs manually to
> permit the purge to finish correctly (thus breaking our standby
> database).
> What I am trying to figure out is this:
> - why do we hit such archivelog production ?

You're deleting data (including index entries) and every such change is recorded in the redo logs.

> - what is exactly stored in an archived redo log ?

Redo records, composed of change vectors describing changes made to database blocks. Each change vector describes a single transactional change to a database block. Change vectors also are stored for changes to the rollback segment data blocks and the transaction table of the rollback segments.

> - how can we distingly disminish this archive log generation ?

Stop purging data?

> - what could be done in order not to break our standby database ?

Increase the size of your standby archivelog destinations to handle this increased workload. Frankly, 8 GB is small for the volume of work you're doing.

> - is there a "best practice" our developers should follow to code
>  his purge system (# of commit, commit frequency, DLL to avoid
>  using, ...

Deletes generate redo, period, regardless of how many per second you execute. Commit frequency has nothing to do with how full your redo logs are. Archivelogs are generated when a redo log is filled and a log switch occurs. The issue is how quickly you're filling the redo logs and a large purge can do that very rapidly.

> I googled hard but found nothing. Any help would be greatly
> appreciated here !
> Thank you in advance.
> Xavier

David Fitzjarrell Received on Tue Aug 04 2009 - 07:20:00 CDT

Original text of this message