What are Redo Logs, Size estimation and How to Resize and/or Add Redo logs

What are Redo Logs?
Each Oracle database has atleast one redo log. This redo log records all changes made in datafiles and allows to replay the SQL Statements. Changes are written ti Redo Logs before they are written to datafiles. In case we need to restore a data file to the time before a change was done on the database we can use the redo information stored in Redo Logs to restore the data file.

How estimate the correct size Redo Logs?
The best way to estimate the size of Redo Logs is to check in alert log and see how frequently log switch occurs and the optimal switch time should be between 20-30 minutes to get better database performance. Oracle recommends a minimum redo log size of 100MB for any production database running 8.1.7 and higher releases of Oracle.

Check in the alert log and see for messages about how frequently the log sequence# are changing, log check pointing and any incomplete redo log switch completion messages. When you examine your alert.log for log switches, you should do it when there are heavier loads in the database. Below is a sample snapshot of redo log switches in alert log:

Fri Jan 27 09:57:40 2004
ARC0: Evaluating archive log 1 thread 1 sequence 174
ARC0: Beginning to archive log 1 thread 1 sequence 174
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/9.2.0/dbs/arch/arc_1_174.src'
ARC0: Completed archiving log 1 thread 1 sequence 174
Fri Jan 27 09:57:52 2004
Thread 1 advanced to log sequence 176
Current log# 3 seq# 176 mem# 0: /u02/oradata/redo/redo03.log
Fri Jan 27 09:57:52 2004
ARC1: Evaluating archive log 2 thread 1 sequence 175
ARC1: Beginning to archive log 2 thread 1 sequence 175
Creating archive destination LOG_ARCHIVE_DEST_1:
'/u02/app/oracle/product/9.2.0/dbs/arch/arc_1_175.src'
ARC1: Completed archiving log 2 thread 1 sequence 175
Fri Jan 27 09:58:07 2004

The above snapshot from alert log file shows that the current redo logs are not big enough.

Since the oracle recommended optimal log switch time is between 20-30minutes and if the log switch is occuring more frequently please check the v$log view for the current of redo logs and based on how frequently it is switching on an average, you will need to create redo log groups with bigger size and then switch the redo log a few times with “ALTER SYSTEM SWITCH LOGFILE;” so that the CURRENT redo log is one of the bigger redo log file and then drop the redo log groups with smaller size with "ALTER DATABASE DROP LOGFILE...." command.

The default redo log filesize on newer versions of oracle is 100MB by default and oracle recommends to spread the redo logs over multiple physical devices for optimal redo log switch performance.

Please keep in mind that too frequent checkpoints and log file switch completion problems will affect database performance. If there are waits on log file switch completion, you will need to add one or more redo log groups also to resolve this wait event in the Statspack report.


How To Resize and/or Add Redo Logs?

Note: Below steps are applicable from versions 9.2.0.1 and up

1. Review information on existing redo logs.

SQL> SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#;

2. Add new groups

SQL> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
SQL> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

3. Check the status on all redo logs again.

SQL> SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b
WHERE a.group#=b.group#4;

Drop the online redo log groups that are not needed. You must have the ALTER DATABASE system privilege.

Note: Before dropping an online redo log group, consider the following restrictions and precautions:

a. An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

c. Make sure an online redo log group is archived (if archiving is enabled) before dropping it. This can be determined by:

GROUP# ARC STATUS
--------- --- ----------------
1 YES ACTIVE
2 NO CURRENT
3 YES INACTIVE
4 YES UNUSED
5 YES UNUSED
6 YES UNUSED

d.Check that the group is inactive and archived before dropping it .

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
e. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files. For more information
refer to Note 395062.1


How to determine the optimal size for redo logs (revisited)?

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs to be at least this size.

Note: The redo log file size affects the MTTR. In some cases, you may be able to refine your choice of the optimal FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested optimal log file size.


Note: Some of the information presented in this post are in reference to Oracle Metalink notes: 1038851.6, 602066.1 and some based on knowledge. And the information was presented referring to people who do not have access to Oracle Metalink.