Adding Online Redo Log Groups- Managing Control Files, Online Redo Logs,and Archivelogs

If you determine that you need to add an online redo log group, use the ADD LOGFILE GROUP statement. In this example, the database already contains two online redo log groups that are sized at 200M each. An additional log group is added that has two members and is sized at 200MB:

With ASM, the command is just to add a group, and it will create the two files for the group on the disk group:

In this scenario, it is recommended that the log group you add be the same size and have the same number of members as the existing online redo logs. If the newly added group doesn’t have the same physical characteristics as the existing groups, it is harder to accurately determine performance issues. If a larger size is preferred, the new group can be added at the larger size; then the other groups can be dropped and re-created with the larger size value to keep the size of the redo logs the same.

Resizing and Dropping Online Redo Log Groups

If you have two log groups sized at 200MB and you add a new log group sized at 500MB, this is likely to produce the “Checkpoint not complete” issue described in the previous section. This is because flushing all modified blocks from the SGA that are protected by the redo in a 500MB log file can potentially take much longer than flushing modified blocks from the SGA that are protected by a 200MB log file.

So, let’s take a look at how to change the size, because you cannot directly modify the size of an existing online redo log as you would a data file. To resize an online redo log, you have to first add online redo log groups that are the size you want and then drop the online redo logs that are the old size.

Using our example, first you add new groups that are 500MB, using the ADD LOGFILE GROUP statement. After you have added the log files with the new size, you can drop the old online redo logs. A log group must have an INACTIVE status before you can drop it. You can check the status of the log group, as shown here:

You can drop an inactive log group with the ALTER DATABASE DROP LOGFILE GROUP statement:

If you attempt to drop the current online log group, Oracle returns an ORA-01623 error, stating that you cannot drop the current group. Use the ALTER SYSTEM SWITCH LOGFILE statement to switch the logs and make the next group the current group:

After a log switch, the log group that was previously the current group retains an active status as long as it contains the redo that Oracle requires to perform crash recovery. If you attempt to drop a log group with an active status, Oracle throws an ORA-01624 error, indicating that the log group is required for crash recovery. Issue an ALTER SYSTEM CHECKPOINT command to make the log group inactive:

Additionally, you cannot drop an online redo log group if doing so leaves your database with only one log group. This will throw an ORA-01567 error and informs you that dropping the log group is not permitted because it would leave you with fewer than two log groups for your database.

When using ASM, the cleanup of the redo files happens automatically. However, using file systems, dropping an online redo log group does not remove the log files from the OS. You have to use an OS command to do this. Before you remove a file from the OS, ensure that it is not in use and that you do not remove a live online redo log file.

For every database on the server, issue this query to view which online redo log files are in use:

Before you physically remove a log file, first switch the online redo logs enough times that all online redo log groups have recently been switched; doing so causes the OS to write to the file and thus give it a new timestamp. For example, if you have three groups, make sure you perform at least three log switches:

Tip: Practice these steps of adding and removing redo logs before turning over a new database to production.