Philosophy of Log files

[johng]

I have a few questions.

I have now set up 16 log files at a size of 32MB each. I have a transaction intensive database with about 75 concurrent users. In this new setup, my logfile switches about once a day.

Is that too infrequent?

I read through a couple of the Oracle Press books and must be missing it, but in a recovery process, what is the command I use to runt he tranascations from the log file?

Thank you

John

[jt]

The rule of thumb is that the log switch should occur about every 15 min.

[ckolik]

I've seen suggestions of at least twice an hour (same difference).

[johng]

but why? I have been reading thorugh several books, but they dont tell me why? Does anyone know?

john

[pwoneill]

I would say to minimize the cost of the loss of one or more logfile. If your logs are keeping a full day's worth of transactions, and for some reason or another you lost an entire logfile and all mirrors, that's a days worth of transactions lost.

If they keep 15 minutes then you will only lose 15 minutes worth of transactions.

Too small is a more often answered question, I/O reasons, time to apply logs to the standby database before another is generated, etc.

[pando]

32MB of LOG, one switch a day, this means not many transactions. How´s your checkpoint interval? Hope it´s doesnt occur at log switch only :D

[jgmagnus]

I hope you have a member for each log file as well. That seems like a LOT of members tho ... (32 total if you do 1 member for each Log File).

- Magnus

[johng]

Pando-i have a lot of tranascations...must be they are small...what would you recommend that my checkpoint interval be?

magnus- umm...i am drawing a blank, I will look it up in the books, buts what a member?

john

[pando]

optimal would be that the checkpoint occurs when the log switch occurs but with your case that would be too infrequent, remember checkpoint syncronize the datafiles, logfiles and control files...
Log switch shoudl occur every 30 minutes, so the checkpoint.
If your checkpoint is so infrequent you will have a bad time when you have to do an instance recovery, Oracle will have to read 32MB log to recover an instance failure.

[pwoneill]

I think magnus meant having a mirrored member for each group.

When you said 16 log files, is that 16 groups? Or 8 groups of 2 mirrored log files, or some other permutation?

If your 16 log files are in 16 groups you should consider adding another member to each group so they are mirrored in different locations. Otherwise if you lose one copy of the log you're out of luck. You could also consider lowering the number of groups and mirroring the logs, (like 8 groups and 2 logs per group or something), I'm not sure why you would need 16 groups if you only switch once a day.

However you might also try to make log switches a bit more frequent.