Redo Logs

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.

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.

 

How Redo Logs Work
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.

Checkpoints
During a checkpoint:

  • A number of dirty database buffers covered by the log being checkpointed are written to the data files by DBWn. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified.
  • The checkpoint background process CKPT updates the headers of all data files and control files to reflect that it has completed successfully.

Checkpoints can occur for all data files in the database or for only specific data files.
A checkpoint occurs, for example, in the following situations:
• At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN
BACKUP] cause checkpointing on specific data files.

ADDING ONLINE REDO LOG MEMBERS

ALTER DATABASE ADD LOGFILE MEMBER
‘$HOME/ORADATA/u04/log1c.rdo’ TO GROUP 1,
‘$HOME/ORADATA/u04/log2c.rdo’ TO GROUP 2,
‘$HOME/ORADATA/u04/log3c.rdo’ TO GROUP 3;

Obtaining Log Group and Member Information
V$LOG

The following query returns information about the online redo log file from the control file:

 
SQL> SELECT group#, sequence#, bytes, members, status
2 FROM v$log;
</span>

<span style="font-family: 'Times New Roman'; font-size: small;">GROUP# SEQUENCE# BYTES MEMBERS STATUS
--------- ---------- -------- --------- ------------------------
1              688                 1048576    1           CURRENT
2              689                 1048576    1           INACTIVE</span>

<span style="font-family: 'Times New Roman'; font-size: small;">

The following items are the most common values for the STATUS column:


  • UNUSED indicates that the online redo log group has never been written to. This is the state of an online redo log file that was just added.
  • CURRENT indicates the current online redo log group. This implies that the online redo log group is active.
  • ACTIVE indicates that the online redo log group is active but is not the current online redo log group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
  • INACTIVE indicates that the online redo log group is no longer needed for instance recovery. It may or may not be archived.

 

Archived Redo Log Files

  • Archiving redo log files is accomplished by ARCn (Archiver) or manually through SQL statements.
  • An entry in the control file recording the archive log name, log sequence number, and high and low
  • SCN number is made whenever a redo log is successfully archived.
  • A filled redo log file cannot be reused until a checkpoint has taken place and the redo log file has been backed up the ARCn process.
  • Archived redo log files can be multiplexed.
  • Archived redo log files must be maintained by the DBA.

 

COMMANDS

ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM CHECKPOINT
ARCHIVE LOG LIST
ALTER DATABASE ADD LOGFILE
ALTER DATABASE ADD LOGFILE MEMBER
ALTER DATABASE RENAME FILE
ALTER DATABASE DROP LOGFILE
ALTER DATABASE DROP LOGFILE MEMBER
ALTER DATABASE CLEAR LOGFILE

Dynamic Performance Views

V$THREAD
V$LOG
V$LOGFILE
V$DATABASE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: