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

From: joel garry <joel-garry_at_home.com>
Date: Tue, 4 Aug 2009 10:16:03 -0700 (PDT)
Message-ID: <ca911c03-2f40-48fd-bb1a-0a84b9449105_at_i4g2000prm.googlegroups.com>



On Aug 3, 10:11 pm, 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.
>
> 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.
>
> 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 ?
> - what is exactly stored in an archived redo log ?
> - how can we distingly disminish this archive log generation ?
> - what could be done in order not to break our standby database ?
> - is there a "best practice" our developers should follow to code
>  his purge system (# of commit, commit frequency, DLL to avoid
>  using, ...
>
> I googled hard but found nothing. Any help would be greatly
> appreciated here !
>
> Thank you in advance.
>
> Xavier

What percentage of data in tables are being purged? If all, truncate. If most, select what you want to save to another table nologging, truncate, reload from other table, truncate that table. You will generate much less redo with the nologging (if you do it right, there are rules you must follow), but data won't get to standby until you reload, so if you crash you need to restore to time before truncate.

If you are 50/50, suck it up and buy more hardware. (Actually, you should do more analysis, but if you don't know the basics, you need the basics first. Most performance problems come from poor application design).

Why are you breaking the standby? You ought to be able to keep it working, just slowing down because of the db waiting while you delete archived logs. How is your standby configured?

jg

--
_at_home.com is bogus.
http://www.cnn.com/2009/US/08/03/new.york.jobless.graduate/
Received on Tue Aug 04 2009 - 12:16:03 CDT

Original text of this message