how often should a database (10g) archive its logs
A reader, September 03, 2010 - 4:02 pm UTC
some document says every 15 minutes, or even some says every 30 minutes.
my question is does this really matter from a performance point of view? for example, one database switches its log every 2 minutes, and one switches its log every 15 minutes. does the 2 minutes one impact the database performance? why? (let's say we have 6 groups of redo files)
September 09, 2010 - 7:29 pm UTC
every time you switch logs - we initiate a checkpoint.
checkpoint often, the more time you spend writing to disk. If you checkpoint less often, we can cache blocks longer.
checkpoint not often enough, the more time you'll spend during instance recovery - your mean time to recover from a failure will be loooonnnnnggggger.
There is a fine balance between reducing the frequency of writing blocks to disk and bounding your recovery time.
are you suffering from lots of waits on IO that are in some part caused by DBWR writing to disk with a small archive switch period? If so, the switch period could be part of the problem. if not, then the switch period is fine.
are you suffering from long startup times after a failure and is this impacting your ability to do business? then your switch period might be too long (or even better - your fast_start_mttr_target parameter should be looked at...
as long as you are not seeing "checkpoint not complete" or "archive not complete" in your alert log - your switching is probably fine unless you fall into the above category.
Link gets a 404
Suz Olliver, October 03, 2017 - 9:51 pm UTC
I tried following the link but it gave a 404 not found.
October 05, 2017 - 4:08 pm UTC
I've updated the link.