What is an Online Redo Log? : A Back-To-Basics Guide

25 Mar What is an Online Redo Log? : A Back-To-Basics Guide

In an Oracle Database, the Online Redo Log is a physical structure consisting of a minimum of two files, which can, in turn, be multiplexed into two or more identical copies. These identical copies are known as members of a Redo Log group. As mentioned, the Online Redo Log consists of at least two files. This allows Oracle to write to one Online Redo Log file while the other is being archived (when we say “archived,” we mean if the database is in ARCHIVELOG mode).

Online Redo Logs store redo records, which are made up of change vectors. Each of these vectors describes the changes made to a data block.

All redo-type records contain relevant metadata about the change, including:

• SCN (System Change Number) and timestamp of the change
• Transaction ID that generated the change
• SCN and timestamp of when the transaction was committed (if it was committed)
• Type of operation that made the change
• Name and type of the modified data segment

Online Redo Logs are only used during the database recovery process.

Basically, what you need to understand is that whenever a DML (insert, update, or delete) or a DDL (alter, create, drop) operation happens in the database, Oracle records the changes in memory—in a buffer called the Redo Log Buffer. There is a process associated with this buffer called LGWR (Log Writer).

The LGWR process is responsible for writing from the Redo Log Buffer (memory structure) to the Online Redo Log files.

It’s important to know the conditions under which LGWR writes to the Online Redo Log:

• When a user commits a transaction
• When a log switch (change of Redo Log file) occurs
• When three seconds have passed since the last LGWR write to the Online Redo Log
• When the Redo Log Buffer is one-third full or contains more than 1MB of data
• When the DBWn (Database Writer) process needs to write data from the Database Buffer Cache to disk

LGWR writes to the Online Redo Log files in a circular manner. When it reaches the last available Online Redo Log file, LGWR goes back and starts writing to the first Online Redo Log file again.

Now that we’ve seen what it is and how it works, as mentioned earlier, Online Redo Logs are only used during the database recovery process.

In the recovery process, two key operations occur: applying committed changes that were not yet written to the datafiles—this is known as Roll Forward, and removing uncommitted changes that were written to the datafiles—this is known as Roll Back.

It might sound a bit confusing, but it’s really not. The only thing you need to know is that when a commit is issued, Oracle adds a Commit Marker in the redo log buffer. That’s how Oracle knows which data is committed and which isn’t.

Here’s a simple algorithm that outlines the recovery process. I got this from Arup Nanda’s blog—I’m not taking credit for it, I’m just posting it here:

1. Read the redo log entries, starting from the oldest
2. Check the SCN (System Change Number) of the change
3. Look for the Commit Marker
4. If the marker is found, then the data has been committed
5. If committed, look for the change in the datafiles (using the SCN)
   1. Are the changes reflected in the datafiles?
      1. If yes, then skip
      2. If not, apply the changes to the datafiles (Roll Forward)
6. If the marker is not found, the data is uncommitted. Look for the changes in the datafiles
   1. Are the changes reflected in the datafiles?
      1. If not, then skip
      2. If yes, update the datafiles with the original (pre-change) data (Roll Back)

To view the information contained in the redo logs, you can use a LogMiner session—we’ll cover that in another post. But for now, here’s an example of the kind of information you can see.

In a session with the HR user, I’ll create a table named BLAH, and then view the transaction information. Once I see that information, I’ll issue a commit to finalize the transaction.

TESTDB >create table blah( name varchar2(100), num number);

Table created.

TESTDB >insert into "HR"."BLAH"("NAME","NUM") values ('Texto Nada Mas Probar Que Inserto','60671');

1 row created.

TESTDB >select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
14.08.27504

TESTDB >commit;

Commit complete.

Now I use the LogMiner utility to view this information from the redo log using the XID of the transaction mentioned above, and here we can see the information from SQL_REDO and SQL_UNDO.

column sql_redo format a30 word_wrapped
column sql_undo format a30 word_wrapped
column seg_owner format a12
select seg_owner,SQL_REDO,SQL_UNDO FROM V$LOGMNR_CONTENTS where XIDUSN=14 and XIDSLT=08 and XIDSQN=27504

SEG_OWNER SQL_REDO SQL_UNDO
------------ ------------------------------ ------------------------------
set transaction read write;
HR insert into delete from "HR"."BLAH" where
"HR"."BLAH"("NAME","NUM") "NAME" = 'Texto Nada Mas
values ('Texto Nada Mas Probar Que Inserto' and "NUM"
Probar Que Inserto','60671'); = '60671' and ROWID =
'AAATuqAAEAAAA+YAAA';
commit;

Conclusion
I hope that with this brief information about what a Redo Log is, along with the previous post about what an undo record is, you can now see the difference between the two and their specific uses.