Oracle'sRedoLog

Oracle'sRedoLog

Each Oracle database has aredo log. This redo log records all changes madein datafiles.

Purpose

The redo log makes it possible to replay SQLstatements.

Before Oracle changes data in a datafile itwrites these changes to the redo log. If something happens to oneof the datafiles, a backed up datafile can be restored and theredo, that was written since, replied, which brings the datafile tothe state it had before it became unavailable.

The same technique is also used in a data guardenvironment(standby databases): One database (the primarydatabase)records all changes and sends themto the standby databases. These standby databases in turn apply(reply) the arrived redo which keeps the synchronized with theprimary database.

Archive Log vs NoarchiveLog

As Oracle rotates through its redo log groups, it willeventually overwrite a group which it has already written to. Datathat is being overwriten would of course be useless for a recoveryscenario. In order to prevent that, a database can (and forproduction databases should) be run in archive log mode. Simplystated, in archive log mode, Oracle makes sure that online redo logfiles are not overwritten unless they have been savely archivedsomewhere.

A database can only be recoverd from mediafailure if itruns under archive log.

See also Archivevs Noarchive Log Mode

LGWR writesthe redo log buffers to disk.

The background process in charge for archiving redo logsisARCn (ifautomatic archiving is enabled.)

In order to find out in which mode the instance runs, onecan use archivelog list fromwithin sql plus.

Log Buffer

All changes that are covered by redo is first written intothe logbuffer. The idea to first store it in thememory is to reduce disk IO. Of course, when a transaction commits, the redo log buffer must be flushed to disk,because otherwise the recovery for that commit could not beguaranteed. It is LGWR(Log Writer)thatdoes that flushing.

Determining amount ofgenerated redo log

select

  n.name,t.value

from

  v$mystat   tjoin

  v$statname n

on

  t.statistic# =n.statistic#

where

  n.name = 'redosize';

See also the package redo_diff

RBA (Redo ByteAddress)

The RBA consists of three parts and is ten byteslong:

·Logsequence number

·Block number within thissequence

·Offset within this block

The location of each redo log entry is identified throuhgan RBA. The RBAs are important for dirty dbblocks in thebuffer cache.

Log sequencenumber

Whenever Oracle (or more precisely, the logwriter process) writes to another onlineredo log file group (alsoreferred to aslogswitch), the logsequence numberincrements by one.

If the database is opened with resetlogs, the log sequence number is reset to1.

Determining optimal redolog size

The optimal size of redo log files can be queriedwith

select optimal_logfile_size fromv$instance_recovery;

Misc

See also managedstandby databases.

ASM simplifies the optimal layout of redo log files.

Minimizing generation ofredo

For a few operations, it is possible to minimize thegeneration of redo if the nologgingoption isset.

Redo transportservices

Redo transport services transport redo data from one server to another.

Notes

Redo is totally different from undo (rollback).

Thanks

Thanks to SeghirSouidi and HubertSavio whohelped improve this page.