Maintaining Redo Log Files

Maintaining Redo Log Files

Purposes of the Redo Log Files

Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions that have not yet been committed, undo segment information, and schema and object management statements. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are used only for recovery.

Structure of the Redo Log Files

The database administrator can set up the Oracle database to maintain copies of online redo log files to avoid losing database information due to a single point of failure.

Online Redo Log Groups

  -->A set of identical copies of online redo log files is called an online redo log group

  -->The LGWR background process concurrently writes the same information to all online redo log files in a group.

  -->The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.

Online Redo Log Members

-->Each online redo log file in a group is called a member.

-->Each member in a group has identical log sequence numbers and the same size. The log sequence number is assigned each time the Oracle server starts writing to a log group to identify each redo log file uniquely. The current log sequence number is stored in the control file and in the header of all data files.

Creating Initial Redo Log Files

The initial set of online redo log groups and members are created during the database creation.

How Redo Logs Work

The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits

• When the redo log buffer becomes one-third full

• When there is more than a megabyte of changed records in the redo log buffer

• Before the DBWn writes modified blocks in the database buffer cache to the data files

Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

Log Switches

LGWR writes to the online redo log files sequentially. When the current online redo log group is filled, LGWR begins writing to the next group. This is called a log switch. When the last available online redo log file is filled, LGWR returns to the first online redo log group and starts writing again.

Forcing Log Switches

Log switches are automatically done at certain points in the operation of the database as identified previously, but a DBA can force a log switch to occur.

Sql> ALTER SYSTEM SWITCH LOGFILE;

Adding New Redo Log Groups

To add new Redo Logfile groups to the database give the following command

SQL>alter database add logfile group 3
    ‘/u01/oracle/ica/log3.ora’ size 10M;

Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.

Adding Members to an existing group

To add new member to an existing group give the following command

SQL>alter database add logfile member
    ‘/u01/oracle/ica/log11.ora’ to group 1;

Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile

Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.

 

Dropping Logfile Group

You can drop logfile group only if the database is having more than two groups and if it is not the current group.

SQL>alter database drop logfile group 3;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Dropping Members from a group

You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command

SQL>alter system switch logfile;

The following command can be used to drop a logfile member

SQL>alter database drop logfile member         ‘/u01/oracle/ica/log11.ora’;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Resizing Logfiles

You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.

Renaming or Relocating Logfiles

To Rename or Relocate Logfiles perform the following steps

For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’, then do the following

-->Shutdown the database

SQL>shutdown immediate;

-->Move the logfile from Old location to new location using operating system command

$mv /u01/oracle/ica/log1.ora  /u02/oracle/ica/log2.ora

-->Start and mount the database

SQL>startup mount

-->Now give the following command to change the location in controlfile

SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;

-->Open the database

SQL>alter database open;

Clearing REDO LOGFILES

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 3:

Sql>ALTER DATABASE CLEAR LOGFILE GROUP 3;

This statement overcomes two situations where dropping redo logs are not possible:

• If there are only two log groups
• The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

Sql>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Viewing Information About Logfiles

To See how many logfile groups are there and their status type the following query.

SQL>SELECT * FROM V$LOG;

 To See how many members are there and where they are located give the following query

SQL>SELECT * FROM V$LOGFILE;