Re: Huge generation of archivelog: how to tweak that ?
Date: Tue, 4 Aug 2009 05:20:00 -0700 (PDT)
On Aug 4, 12:11 am, Xavier Maillard <x..._at_gnu.org> wrote:
> 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
> 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.
David Fitzjarrell Received on Tue Aug 04 2009 - 07:20:00 CDT