In Oracle database what is redo log and how to size it properly ?
Any change that happens in a database is recorded as change vector in redo log. These are essential for database recovery in case of any crash or failure.
To know more about redo log please check the Oracle documentation - Oracle database Admin guide - Redo Logs.
Ideally you would want the redo log switch to happen every 20 minutes .
If you see multiple consecutive log switches in the alert log, which are every few minutes or so, then you will have performance issues and it also tells you that your redo log is undersized.
So how do you size the redo log .
a) you can check the redolog switches and the file size .
Say you have 1 log switches every 2 minutes and your generated archive log is 500K ,
than ideally you want 1 log switch every 20 minutes . So the total would be :
500k * 20 minutes / 2 minute per log switch = 5MB
For higher load systems and Exadata systems, the sizing is around 4GB per redo log, which can always be tweaked higher if needed.
b) Use Enterprise manager , redo sizing advisor as show below .
Go To the appropriate database page.
Select Adminitration > Storage > Redo Log Groups
In Actions Select the Sizing Advice.
This exercise can be done every 3 to 6 months to determine if the sizing is still appropriate for the system peak loads.
References :
Master Note: Troubleshooting Redo Logs and Archiving (Doc ID 1507157.1)
How to Estimate Size of Redo Logs (Doc ID 1038851.6)
Any change that happens in a database is recorded as change vector in redo log. These are essential for database recovery in case of any crash or failure.
To know more about redo log please check the Oracle documentation - Oracle database Admin guide - Redo Logs.
Ideally you would want the redo log switch to happen every 20 minutes .
If you see multiple consecutive log switches in the alert log, which are every few minutes or so, then you will have performance issues and it also tells you that your redo log is undersized.
So how do you size the redo log .
a) you can check the redolog switches and the file size .
Say you have 1 log switches every 2 minutes and your generated archive log is 500K ,
than ideally you want 1 log switch every 20 minutes . So the total would be :
500k * 20 minutes / 2 minute per log switch = 5MB
For higher load systems and Exadata systems, the sizing is around 4GB per redo log, which can always be tweaked higher if needed.
b) Use Enterprise manager , redo sizing advisor as show below .
Go To the appropriate database page.
Select Adminitration > Storage > Redo Log Groups
In Actions Select the Sizing Advice.
This exercise can be done every 3 to 6 months to determine if the sizing is still appropriate for the system peak loads.
References :
Master Note: Troubleshooting Redo Logs and Archiving (Doc ID 1507157.1)
How to Estimate Size of Redo Logs (Doc ID 1038851.6)
Comments
Post a Comment