Oracle online redo log files

The online redo log files are needed for instance recovery and media recovery.

The online redo log file contains redo data (which is a stream of redo records).

The online redo log files are created by the create database statement.

The location of the redo log files is specified with the log_archive_dest_n init parameter and stored in the control file.

The redo log files might be stored in the fast recovery area.

Log file groups and group members

Every database has as least two redo log file groups.

Each group has at least one member. Each member is a physical file.

The entirety of these log files (all members) are referred to as log files.

Exactly one group is current. This is the group to whose members the Logwriter Process (LGWR) writes redo data.

A DBA might decide to have multiple members in a group and store each member on different disks for redundancy reasons. Thus, if one disk fails or one of the members becomes corrupt, there is still another log file with the precious data needed for recovery.

Each log file group is recorded in v$log. All members are recorded in v$logfile.

Active and inactive redo log files

An active log file might be needed for (a possibly future) instance recovery.

Analyzing the redo log

Oracle LogMiner exposes an SQL interface to analyze the entries in the redo log files.

Size of redo log file

The minimum size of a redo log file is 4 MB. The maximum size is operating system dependent, typically 2 TB.

SQL Statements

alter database MY_DB add logfile group 2 ('/u01/logs/MY_DB/redo02.log','/v01/logs/MY_DB/redo02.log') size 100m blocksize 512 reuse;