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;
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 /