0

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.

3
  • 2
    I can not explain that, just recall that this book contains explanation: Oracle Core: Essential Internals for DBAs and Developers, Autor: Jonathan Lewis. Redologs contain old and new version of data in binary format.
    – ibre5041
    May 6, 2023 at 21:30
  • 1
    It's rather complex. Lewis book Oracle Core is one of the best resources there is for this kind of detail. I'd recommend you buy it and read chapter 2, "Redo and Undo".
    – Paul W
    May 7, 2023 at 2:12
  • I think that before the COMMIT it's an UNDO and after it's a REDO!
    – Vérace
    May 7, 2023 at 3:54

2 Answers 2

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

2
  • 1
    I'm pretty certain that the redo entry at commit time does not "include all the information as the first redo", but rather records the change made to the transaction table in the rollback segment header which is what constitutes a transaction being committed. But like I said above, serious internals discussions are best done by referencing those who have dug deeply into them or Oracle's own publications. Most experienced DBAs have a "generally good enough" understanding of how it works, not a precise and comprehensive one.
    – Paul W
    May 8, 2023 at 0:51
  • yes agreed. For exact information, oracle's own publications is needed.
    – Aakash
    May 8, 2023 at 6:30
1

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.