What's inside a redo record in Oracle's Redo log?

Question

While reading the Redo log wiki page, I was confronted with the following statement:

For example, if a user UPDATEs a salary-value in a table containing employee-related data, the DBMS generates a redo record containing change-vectors that describe changes to the data segment block for the table. And if the user then COMMITs the update, Oracle generates another redo record and assigns the change a "system change number" (SCN).

In what way do change-vectors describe changes to the data? Do they contain a copy of the old and the updated data, or just the SQL statements? I also don't understand the difference between the first generated redo record and the one generated after the update is commited.

Answer 1

When an update statement is executed on a record, the old value will go to "UNDO" segment and a change information which includes the new value, the link to old value in undo tablespace and meta data about the changed table/row etc is stored in "REDO" buffer/log

At this point of time, the user has not issued COMMIT.

Other users who are executing a select statement on the same table will read the old value from "UNDO" segment to maintain read consistency.

After sometime when the original user finally issues COMMIT statement, then oracle will create another "REDO" entry which will include all the information as the first REDO + SCN (system change number) number.

This SCN number signifies that the change is committed.

During Instance recovery, first all changes in REDO are applied on the data and then for uncommitted changes (redo without SCN) rollback is performed by reading old value from UNDO

Answer 2

From the Oracle 21c Concepts Guide:

Structure of the Online Redo Log

Online redo log files contain redo records.

A redo record is made up of a group of change vectors, each of which describes a change to a data block. For example, an update to a salary in the employees table generates a redo record that describes changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

The redo records have all relevant metadata for the change, including the following:

• SCN and time stamp of the change

• Transaction ID of the transaction that generated the change

• SCN and time stamp when the transaction committed (if it committed)

• Type of operation that made the change

• Name and type of the modified data segment

See Also:

"Overview of Data Blocks"