Redo log

From Oracle FAQ
Jump to: navigation, search

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[edit]

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[edit]

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:


Redo Log Writer process[edit]

SQL> select spid from v$process where program like '%LGWR%';
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[edit]

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')

Also see[edit]