Online Redo Log Files

Online Redo Log Files:

Online Redo log files have the following characteristics : 

• Record all changes made to data
• Provide a Recovery mechanism
• Can be organized into groups
• At least two groups required 

Online redo log files provide the way to redo transactions in the event of a database failure. Every transaction is written synchronously to the Redo Log Buffer, then gets flushed to the online redo log files in order to provide a recovery mechanism in case of media failure. (With Exceptions such as direct load inserts in objects with the NOLOGGING clause enabled.)

This includes transactions that have not yet been committed, Undo segment information, and schema and object management statements. Online 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 online redo log files are used only for recovery.

 

Structure of the Online Redo Log Files:

                          Online Redo Log File Structure 

The DBA 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 File Groups :

• A set of identical copies of online redo log files is called an online redo log file 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 File Members :

• Each online redo log file in a group is called a member.
• Each member in a group has identical sequence numbers and are of the same size. The log sequence number is assigned each time that the Oracle Server writes to a log group to uniquely identify each online redo log file. 
• The current log sequence number is stored in the control file and in the header of all the data files.  

Creating Initial Online Redo Log Files :

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

The following parameters limit the number of the online redo log file : 

• The MAXLOGFILES parameter in the CREATE DATABASE command specifies the absolute maximum of online redo log file groups.
• The maximum and the default value for MAXLOGFILES are dependent on your operating system.
• The MAXLOGMEMBERS parameter used in the CREATE DATABASE command determines the maximum number of members per group. The Maximum and default value for MAXLOGMEMBERS is dependent on your operating system.
  

How Online Redo Log Files Work:

• Online Redo Log  Files are used in a cyclic fashion.
• When an online redo log file is full, LGWR (background Process ) will move to the next log group.

- Called a log Switch

- Checkpoint operations also occure

- Information Written to the control file

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 the current online redo log file group by the LGWR process. LGWR writes under the following situations.

• When a transaction commits
• When the redo log buffers 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.

Online redo log files are used in a cyclic fashion. Each Online 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 file 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 file group and starts writing again.

Checkpoints :

During the checkpoint below activity happens and can say characteristics of Checkpoint : 

• DBWn writes a number of dirty database buffers, which are covered by the log that is being checkpointed, to the data file.
• The checkpoint backup ground process CKPT updates the control file to reflect that is it has completed a checkpoint successfully. If the checkpoint is caused by a log switch, CKPT also updates the headers of the data file.

Checkpoints can occur for all data files in the database or only for the specific data file.

In the following situation, checkpoints occur, for Example :

• At every log switch
• When an instance has been shut down with the normal, transactional, or Immediate option.
• When forced by setting the FAST_START_MTTR_TRAGET initialization parameter.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL | READ ONLY | BEGIN BACKUP] command causes checkpoint on specific data files.

Information about each checkpoint is recorded in the alert_SID.log file if the LOG_CHECKPOINTS_TO_ALERT initialization parameter is set to TRUE. The default value of FALSE  for this parameter does not log checkpoint.

Forcing Log Switches and Checkpoints :

• Forcing a log Switch by using Command :

 SQL> ALTER SYSTEM SWITCH LOGFILE;

• Checkpoint Can be Forced by :

- Setting FAST_START_MTTR_TARGET parameter.

FAST_START_MTTR_TARGET = 600; 

• ALTER SYSTEM CHECKPOINT  command.

SQL> ALTER SYSTEM CHECKPOINT;i

Log Switches and checkpoints are automatically performed at certain points in the operation of the database as identified previously.  However, a DBA can force a log switch or a checkpoint to occur.

Forcing Checkpoint :

FAST_START_MTTR_TARGET   Parameter replaces the deprecated parameters:

• FAST_START_IO_TARGET
• LOG_CHECKPOINT_TIMEOUT

These deprecated parameters must not be uses if the parameter FAST_START_MTTR_TARGET is used.

In the above example, the FAST_START_MTTR_TARGET parameter has been set 600 seconds, so that instance recovery should not take more than 600 seconds. The Database will adjust the other parameters to this goal.

Thank you !!