Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: log_archive_interval

Re: log_archive_interval

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 30 Nov 2004 19:09:15 +1100
Message-ID: <41ac2aac$0$17541$afc38c87@news.optusnet.com.au>


John Wood wrote:
> Hi there,
>
> We are running Oracle 9.2 on Window 200 environment. I found that the
> log_archive_interval for our database is set to 0. Somehow, I read some
> book, saying that we should never set that parameter to 0.

Help me out here... try for accuracy rather than quantity. Your O/S is Windows 2000 or Windows 2003?

And the parameter of which you speak would, perhaps, be archive_lag_target? Or are you referring to log_checkpoint_interval? Because log_archive_interval doesn't actually exist.

> In the normal hour of the day, I can see the redo log files get switched at
> around every 2 hours. When the daily script to purge some tables is run,
> the log files switch within a minute for two or three times. The purging
> deletes about 20K rows of data. I have 3 log groups; each log file is 100M
> in size.
>
> The question is: do I need to specify a value for the log_archive_interval ?
> The Two hour log switching seems to be too long.

On what basis do you make that claim?

If two hours is too long, why is that? And what time interval do you think you'd be happy with? And why?

If the parameter you are actually asking about is log_checkpoint_interval, then that is deprecated in 9i, because the fast_start_mmtr_target computes it and sets it automatically (along with fast_start_io_target). If you set your own values for the parameter, that prevents mttr_target from doing its job.

> Also are 3 log groups good enough to prevent the situation that the LGWR has
> to wait for ARC process to finish ?

How long is a piece of string?

Come on: no-one can answer your question. On my laptop, two logs is fine. On a busy database, perhaps 5, 6 or more groups would be needed. The only way to know is to keep an eye on your alert log and your wait events.

> I plan to map a drive through network
> as an optional destination for the Archive Log destination.

Don't. It will be a recipe for disaster. And it's totally unsupported. Archiving should be to a local destination unless you specifically implement log_archive_dest_n='Service=XXXX' technology. At which point, you need Oracle networking functioning properly, and a standby instance to receive the redo.

If you must, then use Windows tools to schedule a periodic copying across of a local destination. Do not use Oracle to archive to a remote destination unless it's with the 'authorised' mechanisms.

> I noticed in
> this news group that some user use 4 log group for their system, I wonder
> under what situation the system need to configure so.

When three is insufficient and five is excessive!

There are NO magic answers. You work it out for your own database by using the rich array of diagnostic tools that Oracle provides.

HJR
> Thanks for the advise in advance.
>
> JW
>
>
>
Received on Tue Nov 30 2004 - 02:09:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US