What would be the correct size of redo logs?

Question

I installed and put in production a 11g database. I was adviced to size redo logs to 200MB each one. Now I see that my archived redo logs are each one 200 MB and all of them occupy a lot os disk space. Now I would like to change that size to 100MB or 50MB.

My question is: what would be the OK size for a redolog, depending on what?

Answer 1

Changing the size of your redo logs won't affect how much space is occupied by archived redo logs. If you reduce the size of your redo logs from 200 MB to 100 MB, you'll end up with twice as many archived redo logs each of which is half the size. The volume of archived logs that you generate is dependent on the amount of REDO your system generates not on the size of your redo logs.

In general, you want the redo logs to be large enough that you are not constantly swapping logs (generally not more than every 15 or 20 minutes) and small enough that you aren't risking too much data should there be a catastrophic server failure (assuming that archived redo logs are written to a different server and that you aren't using something like DataGuard to replicate the redo to a backup server in real time).

Answer 2

There are no strict recommendations. For sure, you want to have the largest redo logs possible for you. The sizes that you cite will negatively impact performance. One rule of thumb is that under the largest load, you shouldn't see more than three redo switches per hour. (Redo switch messages are normally included in your alert*.log)

For the large redo logs (multi-gigabyte size) I can have three limitations, but they are easily fixable:

• they take extra disk space (for redologs, not for archivelogs: the total size of archivelogs depends on volume of data changes and does not depend on size of redologs at all)
• you should check if RMAN backup switches redo at the start (it does by default in the recent versions), otherwise your backup would lack a lot of recent data
• if you use standby database (DataGuard) you should use LGWR type destination, not the ARCH type, otherwise your standby would lack a lot of recent data