Redo log

From Oracle FAQ
(Redirected from Redo Log)
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;

Redo Log Writer process[edit]

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[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')
ORDER BY 4
/

Also see[edit]