Operating System - HP-UX
1748181 Members
3370 Online
108759 Solutions
New Discussion юеВ

Re: What should be the Redo Log Members size?

 
SOLVED
Go to solution
Eric Antunes
Honored Contributor

What should be the Redo Log Members size?

I have my Redo Log Members sizes set to 100Mb but I'm affraid this may be too high: is there any rule for this?

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
19 REPLIES 19
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

And what about log_checkpoint_interval? It should be twice or half the Redo Log Members size??
Each and every day is a good day to learn.
Jean-Luc Oudart
Honored Contributor

Re: What should be the Redo Log Members size?

Eric,

Not really, I suppose the associated question is how often you switch log file ?

Regards
Jean-Luc
fiat lux
Patti Johnson
Respected Contributor

Re: What should be the Redo Log Members size?

Eric,

Depending on the number of datafiles in your database a checkpoint can become expensive. At minimum you must checkpoint when you perform a log switch. There is no correct size, but a good suggestion is to size the redo log such that it can hold about 30 min of activity during normal operations.

Check pointing more often reduces recovery time in the event of a failure, but the trade off is a performance hit during normal operations.

Check your alert log to determine how often you are performing a logswitch and use that to decide if the redolog files are too large or small.

Patti
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

Hi Jean-Luc,

How often you switch log file depends on the redo log members size and the init.ora related parameters, right?

I have read documents advising that checkpoint should trigger at half the redo log members size and others advising a log_checkpoint_interval set to twice the redo log members size (in Kb)...

Eric
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: What should be the Redo Log Members size?

Hi Patti,

Here's the situation:

Thread 1 advanced to log sequence 23993
Current log# 3 seq# 23993 mem# 0: /disc1/.../log3a.dbf
Current log# 3 seq# 23993 mem# 1: /disc2/.../log3b.dbf
Fri Dec 2 13:58:59 2005
Thread 1 advanced to log sequence 23994
Current log# 1 seq# 23994 mem# 0: /disc1/.../log1a.dbf
Current log# 1 seq# 23994 mem# 1: /disc2/.../log1b.dbf

And that last checkpoint was forced by "alter system checkpoint" command. So I suppose I'm not having enough checkpoints...

Eric
Each and every day is a good day to learn.
Jean-Luc Oudart
Honored Contributor
Solution

Re: What should be the Redo Log Members size?

Check attachment

I probably ignore tha fact that say the redo must be sized so redo log switch happens very 30mms (or is it every hour ?).
It depends on activity.
get the right checkpoint frequency so yuo have enough but not too many.
In my view the size of thr redo depends mostly on how long does it takes to archive one redo log file when IO activity is fairly high. If it takes between 30sec and 1mn, you may not allowed the redo log switch frequency too be below say 3mns.

Regards
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: What should be the Redo Log Members size?

see also this link
http://www.ixora.com.au/tips/tuning/log_switch.htm

Regards
Jean-Luc
fiat lux
Patti Johnson
Respected Contributor

Re: What should be the Redo Log Members size?

Eric,

From your post I can't tell how often you switch logs as I only see one timestamp.

If you want to know how ofter you checkpoint you can add
log_checkpoints_to_alert = true
to your init.ora, then you will log checkpoints in additions to log switches to the alert log.

If you have enable archivelog mode (and I assume you would in a production db) then looking at the timestamps on the archive log files will also give an indication on how often you switch log files. The time needed to archive the online log file should be much shorter than the time to fill an online log file.
These init.ora parameters control the frequency of checkpoint so set them accordingly if you wich to checkpoint more frequently than at log switch.

log_checkpoint_interval
log_checkpoint_timeout

You need to know how much redo information you generate in a given period of time to determine the best size for your logs.

If you force a log switch after 30 min, then note the size of your archive log file, that will give you an idea of how much redo is generated in 30min. There are other was to calculate this info, but I think thats the easiest.

Patti

Yogeeraj_1
Honored Contributor

Re: What should be the Redo Log Members size?

hi Eric,

You should work it out yourself.

This is how we started:

3 groups of 2 members of 25meg a piece

Then, working up from there, you may end in either adding more groups or making the members larger until you have enough online redo log to get you through at least 15-20 minutes of work without switching.

Note that there will be times when 15-20 minutes is too long and too short. You need to pick your time and go from there.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)