Redo Logs consist of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has associated online redo logs to protect the database in case of an instance failure. Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Finding redo log file members

You can find the redo log file members from v$logfile view as shown below:

SQL> col member format a50
SQL> select GROUP#,TYPE,MEMBER from v$logfile;

GROUP#       TYPE     MEMBER
----------  -------  --------------------------------------------------
 3           ONLINE  /oracle/boston/origlogA/boston/redo03a.log
 3           ONLINE  /oracle/boston/mirrlogA/boston/redo03b.log
 2           ONLINE  /oracle/boston/origlogA/boston/redo02a.log
 2           ONLINE  /oracle/boston/mirrlogA/boston/redo02b.log
 1           ONLINE  /oracle/boston/origlogA/boston/redo01a.log
 1           ONLINE  /oracle/boston/mirrlogA/boston/redo01b.log

Finding redo log file size

Redo log file size can be determined from v$log view as shown below:

SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP#      THREAD#   SEQUENCE#  BYTES/1024/1024 MEMBERS   STATUS
---------- ---------- ---------- --------------- ---------- ----------------
 1              1        10        100                 2       INACTIVE
 2              1        11        100                 2       INACTIVE
 3              1        12        100                 2       CURRENT

Combined view of both v$logfile & v$log:

SQL> column REDOLOG_FILE_NAME format a50
SQL> SELECT
 a.GROUP#,
 a.THREAD#,
 a.SEQUENCE#,
 a.ARCHIVED,
 a.STATUS,
 b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# SQL> 2 3 4 5 6 7 8 9 10 11
 12 ;

GROUP#       THREAD#   SEQUENCE# ARC    STATUS            REDOLOG_FILE_NAME                          SIZE_MB
---------- ---------- ---------- --- ---------------- --------------------------------------------- ----------
 1            1           10     YES   INACTIVE         /oracle/boston/origlogA/boston/redo01a.log    100
 1            1           10     YES   INACTIVE         /oracle/boston/mirrlogA/boston/redo01b.log    100
 2            1           11     YES   INACTIVE         /oracle/boston/origlogA/boston/redo02a.log    100
 2            1           11     YES   INACTIVE         /oracle/boston/mirrlogA/boston/redo02b.log    100
 3            1           12     NO    CURRENT          /oracle/boston/origlogA/boston/redo03a.log    100
 3            1           12     NO    CURRENT          /oracle/boston/mirrlogA/boston/redo03b.log    100

Redo Log Data Dictionary Views

The following views provide information on redo logs:

View Description
V$LOG Displays the redo log file information from the control file
V$LOGFILE Identifies redo log groups and members and member status
V$LOG_HISTORY Contains log history information