Managing Oracle Redo Log Files


The online redo logs store all changes made to the database. Each database instance has its own redo log groups or thread of online redo. Where one instance access a single set of datafiles only a single thread is present. In a parallel server environment two or more instances can concurrently access a single set of datafiles. Each instance is an individual thread of online redo.

If you operate your database in NOARCHIVELOG mode, your redo log consists of on-line redo log files only. If you run your database in ARCHIVELOG mode, your redo log consists of both on-line and archived redo log files. The archived redo log files can be either off-line, stored on a tape device for instance, or on-line on disk.

The diagram below shows a 3-way mirror of the redo log files.

Online redo log files are the only files in the database which cannot be recovered using database controlled backup utilities. Thus firstly, maintain multiple sets of redo logs on separate disks using multiple sets of mirrored redo log files. Any restore will use the acrhive logs to restore the database upto the current redo log. If the current redo log file can be recoved from a mirrored copy thus the database can be completely restored to the point in time immediately prior to a crash.

It is also sensible to store online redo logs on a separate disk to that of the datafiles. Note that online redo logs should be stored on a separate disk to that of highly active tablespace datafiles such as the SYSTEM tablespace, the rollback, active data and index (transactional) tablespaces. Online redo logs contain records of all database activity. All database activity involving database access and database updating produces disk I/O activity. Online redo logs are disk I/O intensive aswell. Therefore online redo logs and active tablespace datafiles placed on the same disk produce significant disk I/O contention.

Online redo logs should be on a separate disk to that of active tablespace datafiles for the same reason that DATA and INDEX type tablespace datafiles should be separated onto separate disks to avoid I/O disk contention.

Redo Logs

Contents of the Online Redo Log

Each redo log files contains redo record entries. Each redo record entry is a set of changes. Each change is a description of changes made to a single block for a data segment block and a rollback segment block. These changes allow reconstruction of the database by being reappliable in the future. The online redo log allows recovery of both data and rollback data. On transaction commit the LGWR writes redo records from the redo log buffer to one of the online redo log files. A system change number (SCN) is assigned to be able to identify each redo record in the future.

The Process of Online Redo Log File Writes

Oracle needs atleast two online redo log files. When the last available online redo log is complete then the LGWR cycles to the first online redo log file. Note that only the active redo log files are required for recovery. Whenever a log switch occurs a new log sequence number is assigned to the redo log. Thus if and when the redo log is archived the log keeps that log sequence number.

Sequence of Storage

Online Redo Log Failure

In the case where the LGWR cannot write to the current redo log the redo log is marked as STALE. Normal redo log files are marked as ACTIVE, INACTIVE or CURRENT.

Multiplexing Redo Logs

Redo logs can be duplicated in real-time. This is done by setting the parameters in the init.ora file called log_archive_dest_1 to 5. The redo logs can be multiplexed five times. Each separate redo log file is called a log group. Eaach member within each log group is a multiplixed or duplicate of each other member within its parent log group. The members can be placed on different disks.

Howmany Redo Logs

The required minimum is two redo logs. The best option is to maintain as few redo logs as possible without restricting the LGWR. The most restricting effects involve the LGWR waiting for completion of redo log archiving or checkpointing. There are a number of CREATE DATABASE parameters which can restrict the maximum redo log configuration.

Archived Redo Logs

Do not backup redo logs. Archive the current redo log and then backup the archived redo logs as shown below.

ARCHIVE LOG ALL;

ARCHIVELOG/NOARCHIVELOG

In NOARCHIVE LOG mode the database can only be restored and not recovered. All recovery is performed from the archive logs and not the redo logs. When the database is not in archive mode only full database backups are available, ie. individual tablespaces cannot be backed up and restored.

Standby Databases

Standby databases can be kept current by conctinual application of archived redo logs to the standby database.

Multiplexing Archive Logs

Archive logs can be duplicated in real-time by setting multiple archive log destinations using the init.ora parameters LOG_ARCHIVE_DEST_1 .. 3.

Archive Destination States

Archive Performance

ARchiving performance can be improved by creating multple archiver processes (ARCn). Set the LOG_ARCHIVE_MAX_PROCESSES parameter in the init.ora file between 1 and 10. Multiple archive buffers (LOG_ARCHIVE_BUFFERS) can help to force the ARCn processes to read the archive log at the same time that they write the output log.