A successful synchronous commit can survive loss of instance memory while a changed table block still awaits a data-file write. Oracle saves the required change information in persistent online redo log files. Recovery rebuilds recorded changes and reverses unfinished transactions.
Oracle DBA Lesson 9 Part 1 — How Oracle Recovers Committed Changes
Redo, undo, and their locations
Redo records describe changes to database blocks and contain the information recovery can apply to reconstruct them. An ordinary update changes table blocks and undo blocks. Both kinds of change generate redo.
Undo retains the earlier information used to reverse a transaction and provide consistent reads. Working undo blocks live in the database buffer cache. Permanent undo has physical storage in the undo tablespace's data files. Recording undo-block changes in redo lets recovery reconstruct undo after memory has been lost. See memory architecture.
The video labels the table block B1 and the transactions' undo blocks U1 and U2 so their paths remain recognizable. The redo entries represent change information, rather than copies of the original SQL or complete replacement blocks.
| Structure | Location and purpose |
|---|---|
| Database buffer cache | RAM: working copies of table, index, and undo blocks |
| Redo log buffer | RAM: temporary generated redo records |
| Data files | Physical disk files: saved table, index, and undo blocks |
| Online redo log files | Physical disk files: redo available for instance recovery |
LGWR, the log writer, transfers redo from the RAM buffer to the online redo log files. DBWn, the database writers, save modified blocks to data files. Associated redo must become persistent before a changed data block is written. That is the write-ahead rule. See process architecture and managing the redo log.
LGWR writes during activity as well as for commits, so saved redo can include transactions that are still in progress.
The commit acknowledgment
COMMIT WRITE WAIT;WAIT makes the successful return depend on the required redo becoming persistent in the online redo log. That includes the commit record. Changed data blocks can reach their files before or after the commit. See COMMIT.
The application can receive success while the table buffer remains dirty. Recovery has the durable information needed to reconstruct the committed change. The command resolves the current transaction in the connected session.
If a failure interrupts the response after the redo write, the application may be uncertain whether the commit succeeded. Check the business result before retrying a change that could be applied twice.
Instance recovery
Assume one stock row begins at quantity 10. The transactions occur sequentially. The first reserves one item and commits successfully using WAIT. The second reserves another item and remains uncommitted.
Redo for both updates and the first commit record have reached the online redo log files. The table block saved on disk still contains 10 when the instance fails.
| Moment | Quantity and evidence |
|---|---|
| Starting state | 10 in the working copy and the saved table block |
| First reservation | 9 in RAM; required redo and the commit record saved |
| Second reservation | 8 in RAM; redo saved; transaction unfinished |
| Failure | RAM lost; saved table block 10 and the physical redo files intact |
| Roll forward | Recorded changes rebuild 10→9→8 and reconstruct undo |
| Transaction recovery | Rebuilt undo reverses the second change 8→9 |
| Recovered result | 9: the completed first reservation remains |
Oracle automatically performs instance recovery when this single-instance database restarts. Damage to physical data files, or loss of required redo, needs a different recovery procedure. See the database instance.
The checkpoint position identifies where instance recovery starts in the redo. Some later changes may already exist in the data files. Recovery uses the needed redo and the block state.
Rolling forward reapplies the needed recorded changes, including committed and uncommitted changes, and rebuilds undo blocks. Read the saved table and undo blocks into RAM, then apply their redo. The table working copy advances from 10 to 9 to 8. Rebuilt U1 retains the earlier quantity 10. Rebuilt U2 supplies the earlier quantity 9.
Transaction recovery, or rolling back, uses U2 to reverse the unfinished second update: 8 returns to 9. The first transaction's commit record establishes its completed outcome. Its reservation remains. Oracle also reconstructs transaction status. The quantity alone cannot identify the outcome.
Applying undo changes blocks and generates further redo. The log writer saves the associated redo before the database writer saves those changed blocks. The video then shows the recovered table and undo blocks written to their respective data files. Background writes and transaction rollback can overlap in an actual database. Copies in RAM and physical files remain separate objects. Undo is stored in database blocks, rather than in separate external "undo log" files. See logical storage structures and rollback and redo.
The recovered quantity is 9. This phase diagram explains dependencies. Oracle need not finish every terminated-transaction rollback before providing database access.
Interpret the waiting stage
| Wait | Meaning | Useful evidence |
|---|---|---|
log buffer space | Session needs room for redo while generation outpaces draining | Redo volume, buffer capacity, and LGWR draining behavior |
log file sync | Commit session waits for the required flush and notification | Commit frequency, workload timing, scheduling, and redo-write evidence |
log file parallel write | Completion of online redo-file writes | Storage path and write latency |
Extra buffer capacity can absorb a burst. A synchronous commit still waits for the required persistent write, and that disk latency remains relevant. Compare the workload and the storage evidence before choosing a change. A wait name identifies a stage. It is not a complete diagnosis. See wait events.
Commit visibility and compensation
Use separate normal READ COMMITTED sessions, and leave client autocommit disabled.
In SQL*Plus, begin each session with:
SHOW USER
SHOW CON_NAME
SET AUTOCOMMIT OFF
SELECT employee_id, salary
FROM employees
WHERE employee_id = 101;Record the original salary and confirm exactly one row. In the updating session:
UPDATE employees
SET salary = salary + 1
WHERE employee_id = 101;
COMMIT WRITE WAIT;Confirm that the update affected one row before committing. After the expected commit, issue a fresh SELECT in the second session. It should see the recorded baseline plus one.
Restore the original salary with a new compensating transaction in the updating session:
UPDATE employees
SET salary = salary - 1
WHERE employee_id = 101;
COMMIT WRITE WAIT;Verify each row count, and confirm from both sessions that salary equals the recorded baseline. ROLLBACK reverses uncommitted work. A committed change requires a new restoring change and a commit.
This demonstrates visibility and restoration.
Recap
A successful COMMIT WRITE WAIT returns after the required redo, including the commit record, is persistent in the online redo log. The changed table block can still be dirty. LGWR also writes during ordinary activity, so saved redo can include a transaction that has not committed. In the stock example the saved table block still contains 10. Roll forward rebuilds 10 to 9 to 8 and the undo. Transaction recovery uses rebuilt U2 to reverse the unfinished second reservation, so the recovered quantity is 9. A larger redo buffer does not remove the disk latency of the required durable write. log file sync is the commit session's wait for that flush and its notification.
No comments:
Post a Comment