Oracle Redo Log Buffer – Introduction

What is a Redo Log Buffer?

The redo log buffer is a part of the Shared Global Area (SGA). This shared memory structure holds information about changes made to the database, called redo. Redo is generated by each transaction in the database as it makes a change (e.g. insert, update, delete, or any DDL operations). These redo entries contain the information needed to reconstruct any changes that have been made to the database. Redo is ultimately needed to recover the database in the event of a failure.

As changes occur in the database, the redo generated by those changes is stored in the redo log buffer. When certain conditions occur, the redo log buffer will be flushed by Oracle to the online redo logs that exist on physical disk by the Oracle background process called the Log Writer (LWGR). The conditions where the log buffer is flushed include:

• A session issues a commit or a rollback command.
• The log buffer becomes 1/3 full.
• A timeout (every 3 seconds) occurs.
• A checkpoint occurs.

The initialization parameter log_buffer determines the size of the redo log buffer. The default is set to four times the maximum data block size for the host operating system.

How Oracle Allocates Space in the Redo Log Buffer

As each user session makes changes to the database, these changes are first recorded in the redo log buffer. The following steps approximate the process:

• The session will acquire the redo allocation latch.
• The session will allocate the memory it needs from the redo log buffer for the copy of the redo.
• The redo allocation latch is released and a redo copy latch is acquired, if available. If there is not a redo copy latch available, then the redo allocation latch is held until the end of the operation.
• The redo is copied to the redo log buffer.
• The redo copy latch is released.

The redo allocation latch is acquired to allocate memory space in the log buffer. The number of redo allocation latches is determined by database parameter LOG_PARALLELISM (this parameter has been deprecated in Oracle 10g). The redo allocation latch allocates space in the log buffer cache for each transaction entry. If transactions are small, or if there is only one CPU on the server, then the redo allocation latch also copies the transaction data into the log buffer cache. If a log switch is needed to get free space, this latch and the redo copy latch are released.

How Oracle Handles the Buffer Copy to the Online Redo Logs

The LGWR background process must go through the following steps to create a redo record in the buffer.

• A latch is acquired preventing any additional redo from being written to the redo log buffer.
• Find the space available to write the redo record to the online redo log. If there is no space, issue a log switch.
• Copy the redo record from the log buffer to the online redo log.
• Release the latch.

The redo writing latch prevents multiple processes from signaling the LGWR process to do a log switch simultaneously. A process that needs free space must acquire the latch before deciding whether to post the LGWR to perform a write, execute a log switch or just wait. The waits for the redo writing latch can be found in V$LATCH as seen in this query:

SQL> column name format a30
SQL> select name, gets, misses, wait_time from v$latch
   2 where name like ‘%redo%’

NAME                                 GETS     MISSES  WAIT_TIME
—————————— ———- ———- ———-
redo on-disk SCN                        0          0          0
redo writing                        29935          0          0
redo copy                              44          0          0
redo allocation                     10271          0          0