Oracle Redo Logs – Create Group Insights

Recommendations

• Create a minimum of three groups with two log files each. A minimum of two groups of log files each are required for each Oracle instance (even in a parallel instance environment.) 
• In a situation where archive logging is being used, three groups are the absolute minimum per instance to allow one group to be active, one to be involved in being written to the archive media and one ready for use should the first group fill before the second group is finished archiving. 
• If you get indications in the ALERT log that LGWR is waiting for logs to complete checkpoints before switching, this indicates more groups are needed. This data is also available in the LGWR trace files.
• Each log file should be on a separate physical platter.

Archiving Impact

A factor that should be considered when creating Redo Logs is whether or not you are using archive logging. When a log fills up, a log switch occurs whereby the next log group is used. The log switch prompts ARCH to archive the filled redo log to the archive log destination. Until the redo log is archived, it cannot be used. 

If a log switch goes to a redo log that has not yet been archived, the database stops. This is why three is the minimum number of logs recommended for an archive situation, one in-use, one waiting to be used and one archiving. 

Generally, it is suggested that several be available for use. In several installations were the logs were archived to disk, during heavy use periods the disk filled causing archiving to be suspended. Once the available logs filled, the database stopped. Four redo logs seem to work well in most situations. Your may need more depending on your situation. 

With multiple logs, you can have time to respond before the database has to be stopped. This also points out that you should keep a close eye on disk space usage for your archive destination. 

If the redo logs or groups are archived to tape, ensure the log sizes are such that an equal number will fit on a standard tape to avoid space, time, and waste. For example, if you have redo logs that are 1 megabyte in size and your tape has 90 megabytes capacity, then 90 logs will fit on the tape (approximately) with little waste.

The combined size of the redo group needs to be considered since the entire group is archived as a unit. It is suggested that several smaller archive logs be used instead of fewer larger ones in a group. This allows you to size the group such that it is easily archived.