Oracle refers to a transaction log as a redo log. When a transaction is in progr

Question

Oracle refers to a transaction log as a redo log. When a transaction is in progress, changes in data items are recorded in memory in a redo log buffer. When any transaction commits, the redo log buffer (which contains changes that all transactions have made) is written to a redo log file. Curiously, the commit does not force anything to be written to the actual database files. Occasionally, a background process called Database-Writer will write modified buffers out to database files.

Suppose the system crashes. How could the redo log file be used to undo a transaction that was only partially completed and had not committed?

Suppose the system crashes. How could the redo log file be used to ensure committed transactions are actually written to the database files?

Explanation / Answer

In every database system, the possibility of a system failure is always present. Should system failure occur, you must recover the database as quickly, and with as little detrimental impact on users, as possible.

Recovering from any type of system failure requires the following:

The goal is to return to normal as quickly as possible while insulating database users from any problems and the possibility of losing or duplicating work.

The recovery process varies depending on the type of failure and the files of the database affected by the failure.

The redo log is a set of operating system files that record all changes made to any database buffer, including data, index, and rollback segments, whether the changes are committed or uncommitted. The redo log protects changes made to database buffers in memory that have not been written to the datafiles.

The first step of recovery from an instance or disk failure is to roll forward, or reapply all of the changes recorded in the redo log to the datafiles. Because rollback data is also recorded in the redo log, rolling forward also regenerates the corresponding rollback segments.

Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. Rolling forward usually includes online redo log files and may include archived redo log files.

After roll forward, the data blocks contain all committed changes as well as any uncommitted changes that were recorded in the redo log.

Recovery with an undo log

• Identify U, the set of active transactions at time of crash – Log contains , but neither nor • Process log backward – For each where T is in U, issue write(X, old_value), output(X) • For each T in U, append to the end of the log

Redo logging

• Basic idea – Every time you modify something on disk, record its new value (which you are writing) – If system crashes, redo the writes of committed transactions and ignore those that did not commit

Recovery: analysis and redo phase

• Need to determine U, the set of active transactions at time of crash

• Scan log backward to find the last end-checkpoint record and follow the pointer to find the corresponding

• Initially, let U be S

• Scan forward from that start-checkpoint to end of the log – For a log record , add T to U – For a log record , remove T from U – For a log record , issue write(X, new) – Repeats history!

Recovery: undo phase

• Scan log backward – Undo the effects of transactions in U – That is, for each log record where T is in U, issue write(X, old), and log this operation too (part of the repeating-history paradigm) – Log when all effects of T have been undone

• An optimization – Each log record stores a pointer to the previous log record for the same transaction; follow the pointer chain during undo

• Is it possible that undo overwrites the effect of a committed transaction? – Not if strict 2PL!

Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.