Oracle – Database Corruption After Instance Crash: Is It Possible?

oraclerecovery

I'm studying a little DBA stuff and I've come across instance failure/recovery.

Let's say User A does DML and User B also does DML.

At this point, I believe it's safe to assume DB buffer cache contains changed table blocks and undo segments, and also that log buffer contains redo information for the new table blocks value and the new undo segments value.

  • Case 1 : No user issues a COMMIT and no writes to disk by DBWn and instance crash. Result : DB not corrupted. It's as if their DML never occurred.

  • Case 2 : Only User A issues a COMMIT. User B's uncommitted changes are written to disk then the instance crashes. LGWR will have written enough data in the redo log , especially User B's undo segments, so that the recovery's rollback phase will make the database consistent again

  • Case 3 : No user issues a COMMIT but their changed table blocks are old enough to make DBWn write them to disk. Then BOOM! Instance crashes. How will SMON perform rollback to clear dirty blocks from datafiles here? Especially since there is no undo segment values for them in the redo log here.

Best Answer

A dirty block can only be written to disk if all changes until the modification time of this block was already written to the redo logs. The Oracle 12g2 Database Concepts Guide says

Before DBW can write a dirty buffer, the database must write to disk the redo records associated with changes to the buffer (the write-ahead protocol). If DBW discovers that some redo records have not been written, it signals LGWR to write the records to disk, and waits for LGWR to complete before writing the data buffers to disk.

So after a database was recovered it contains all modification until to a certain time and no modifications that where made after this time. If a transaction was committed the redo logs contain the modification oft his transaction and so its modifications where written to the data files during the recovery. Transactions that weren't committed either only modified memory structures (buffer cache) and so all there modifications are lost because of he crash, or they did some modifications of the file but then the undo information was also written to the files that contains the undo segments and this undo information will be used to undo the changes of this transaction.

case 3: If a dirty block of a data file is written back to disk then all changes done before the modification of this block is already in the redo logs and so the undo information is already in the redo logs

case 1: If no user issues a commit that does not mean that changes are written to data file or redo log. But if something was written to disk it can be undone as described above.

case 2: Uncommitted changes must not be written to disk. It is only guaranteed that committed changes are written to disk.

Related Question