Redo log

A redo log is a file that is part of an Oracle database. When a transaction is committed, the transaction's details in the redo log buffer is written to a redo log file.

Redo log buffer

A circular buffer in the SGA that contains information about changes made to the database. The LGWR process writes information from this buffer to the redo log files.

Redo log files

A set of files that record all changes made to an Oracle database. A database MUST have at least two redo log files. Log files can be multiplexed on multiple disks to ensure that they will not get lost.

Query redo log details:

SELECT * FROM v$log;

To see the logfile members:

SELECT * FROM v$logfile;

Note that a redo log can have different states:

• CURRENT: redo records are currently being written to the group. Only one group is current at a time.
• ACTIVE: redo group that contains redo's of a dirty buffer (not yet committed transaction).
• INACTIVE: log that can be overwritten.
• UNUSED: initial state after creation, when it's still empty.

The point at which Oracle stops writing to one redo log and starts writing to another is called a log switch. You can force the log switch with:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Redo Log Writer process

SQL> select spid from v$process where program like '%LGWR%';
 
SPID
------------
29867
 
SQL> ! ps -ef | grep 29867
 oracle 29867     1  0   Sep 26 ?        7:59 ora_lgwr_o102

Find the database users that generate the most redo

It is sometimes necessary to find the processes that generate the most redo entries, as they may cause excessive database archiving. This query will help:

SELECT s.sid, s.username, s.program, t.value "redo blocks written"
  FROM v$session s, v$sesstat t
 WHERE s.sid = t.sid
   AND t.value != 0
   AND t.statistic# = (SELECT statistic# FROM v$statname
                        WHERE name = 'redo size')
ORDER BY 4
/

Also see

• Datafile
• Tempfile
• Controlfile