Re: log_archive_interval

I think you mean the log_checkpoint_interval.

However, there is a parameter in 9.2 which is called the archive_lag_target. This may be the one you want to set if you don't like the 2 hour delay between log file switches - specific it in seconds, and a log switch will occur that many seconds (at worst) after the previous log switch.

The log_checkpoint_interval (and log_checkpoint_timeout) no longer have the meaning they did prior to 8.1 - they specify how long a data block can stay on the dirty queue before dbwr will have to write it out - the 'interval' measures in terms of redo blocks, the 'timeout' measures in seconds.

You may find that you have a default of 1800 seconds on the timeout. Modifying this will change the general background rate at which dbwr trickles blocks to disc, hence the impact of clearing the data buffer at checkpoints.

If you have "archiver not complete" warnings in the alert log, then it is perfectly reasonable to increase the number of redo log groups until they stop happening. You may also want to increase the size of the log files to reduce the frequency of log switches during the intense periods anyway.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"John Wood" <jwood_at_hotmail.com> wrote in message 
news:VEUqd.247110$9b.75520_at_edtnps84...


> 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.

>

> 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.

>

> Also are 3 log groups good enough to prevent the situation that the LGWR 

> has

> to wait for ARC process to finish ?  I plan to map a drive through network

> as an optional destination for the Archive Log destination.  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.

>

> Thanks for the advise in advance.

>

> JW

>

>

>