Oracle Concepts - Online redo log management Oracle Tips by Burleson Consulting

Administering Oracle Online Redo Logs

In an earlier chapter we introduced you to the Oracle redo log files. If you recall, they are files that are like Oracle?s little tape recorder, and Oracle records (almost) everything that happens inside the Oracle database. Oracle uses these redo log groups to recover the database, so they are pretty important.

Each individual redo log is assigned to a group. Oracle writes to only one online redo log group at a time. Once the online redo log(s) in that group are filled then Oracle will switch to writing the next online redo log group, and so on in a circular fashion.

Each online redo log is assigned a unique sequence number. No online redo log will ever have the same sequence number in a given database unless a new incarnation of that database is created. A new incarnation of a database will occur as the result of certain incomplete recovery operations, and you will not normally need to worry about conflicts in thread numbers unless you have performed an incomplete recovery of your database. We will discuss recovery in more detail later in this book.

We can multiplex each online redo log group. This means that each redo log group can consist of more than one online redo log file. Each file is known as a member. Each member should be located on a different disk, to protect the group from losing all of its members in the event a disk failure should occur. Oracle writes to those members in parallel, to ensure that the database is always recoverable while maintaining performance.

The online redo logs are first created when the database is created, and the database cannot live without them. If all members of the active redo log group are lost, the database crashes, and worse yet, there will be data loss. Hence, it is very important to preserve these files. There are two kinds of redo logs that we will consider in this section, the online redo logs and archived redo logs.

First we will address administration of online redo logs. This includes creation and removal of an online redo log group, as well as the addition and removal of redo log group members.

Create Online Redo Log Groups

You use the alter database command to add an online redo log group, and its associated members, to the database. Here is an example of the creation of a new online redo log group:

alter database add logfile group 4 ?c:\oracle\oradata\booktst\booktst\redo04.log' size 50m;

You can also add multiple members at the same time:

alter database add logfile group 5 (?c:\oracle\oradata\booktst\booktst\redo05a.log',

 ?d:\oracle\oradata\booktst\booktst\redo05b.log') size 50m;

Drop an Online Redo Log Group

Of course, there are times that you will want to drop a given redo log group. This is commonly done if you need to increase or reduce the size of the online redo logs. You drop the online redo log group using the alter database drop logfile group command as seen in this example:

alter database drop logfile group 5;

There are a few things to note concerning the dropping of redo log groups.  For starters, you cannot drop the CURRENT redo log group; meaning, if the log group you wish to drop is currently being written to, it will not be allowed.  In this case, you can switch to the next redo log group and then drop the old one.  You can do this with the following command:

SQL> alter system switch logfile;

Another thing to note is that when you drop the redo log group from the database, it is not dropped on the file system.  You must remove it manually from the file system AFTER it has been removed gracefully from the database.

Add a Member to an Online Redo Log

Sometimes we realize that we need to add an additional member to an online redo log. Usually this is when we discover to our horror that we have only one member in each of our online redo groups (if you find that this is the case and you are not horrified, then you don?t yet understand why we multiplex online redo logs).

You use the alter database command to add a member to a redo log group as seen in this example:

Drop a Member from an Online Redo Log

Mistakes happen, and sometimes you want to remove a member from an online redo log group. You can use the alter database command to perform this operation too:

alter database
drop logfile member 'c:\oracle\oradata\booktst\booktst\redo03a.log' ;

Again, when you drop a member of a redo log group, the file will not be dropped from the OS itself.  You must do this manually at the operating system level.

Online Redo Log Data Dictionary Views

Oracle provides data dictionary views for the online redo logs as seen in this table:

v$log - Lists information about each member of each online redo log group.

v$logfile - Provides redo log file name information

If you query v$log, you will see information about each redo log group, including the size of each member and how many members in each log group.  Most importantly, you can find out which log group is the CURRENT redo log group.  Remember the alter system switch logfile command in the case that you want to change it.

Here is a dictionary query to display the redo logs:

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.