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

On Aug 4, 12:11 am, Xavier Maillard <x..._at_gnu.org> 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