2

The following is an excerpt from an ORACLE book

During cache recovery, Oracle replays the transactions from the online redo log files since the last checkpoint. During this roll forward operation, both committed and uncommitted changes are applied to the data files. At the end of the roll-forward operation, the data files will have committed changes, uncommitted changes that were written to the data files to free up buffer cache space and uncommitted changes applied by the roll-forward operation. The database can be opened as soon as cache recovery is complete.

The sentece in bold above is the one I don't understand. How can a redo log file generate uncommited data since all its information are recorded after COMMIT ?

Further it says

In the transaction-recovery phase of instance recovery, Oracle applies undo blocks to roll back uncommitted changes in data blocks that were either written before the instance crash or made by the roll-forward operation during cache recovery.

I understand that if dirty blocks were written to disk without a prior COMMIT (if at all possible), during transaction recovery, undo blocks can be used to roll them back. BUT how can undo blocks can be used to rollback uncommitted changes (what arethose changes anyaway...) introduced by replaying redo logs during cache recovery ?

Hmmm I don't get it since online redo log, which should only contain committed information, create uncommited information during instance recovery.

  • 1
    Why do you think that the online redo logs contain only commited information? This is not true. It is the other way round. A transaction can only be commited if the online redo log contains the whole information. – miracle173 Jan 3 '17 at 23:53
  • @miracle173 Reading the answer below I think I get it. It's not only an explicity COMMIT that flushes the log buffer to the redo log file (for instance 3 seconds after last flush). Hence it may contain uncommited actions. – Jason Krs Jan 4 '17 at 7:33
  • 1
    @a_horse_with_no_name Hello a_horse_with_no_name. Glad to see you again. I do mean cache recovery. Crash recovery (or instance recovery) is made in 2 steps : cache recovery (or rolling forward) and transaction recovery (or rollback)... See this glossary for cache recovery -> docs.oracle.com/cd/E11882_01/server.112/e40540/… – Jason Krs Jan 4 '17 at 11:47
4

How can a redo log file generate uncommitted data since all its information are recorded after COMMIT ?

LGWR process flushes redo logs from redo log buffer cache to online redo log files when-

  1. A user fire commit explicitly.
  2. One third of space in redo log buffer is filled.
  3. Three seconds of time has passed since last redo flush.
  4. DBWn process writes dirty buffer to disk.

These all triggers redo flush which may include uncommitted transaction.

BUT how can undo blocks can be used to rollback uncommitted changes (what are those changes anyway...) introduced by replaying redo logs during cache recovery ?

Let me give you an example,suppose you have a data block which contains one row with single column with value 5. You simply try to update it using update tbl set col1=6. During this process undo is generated to reproduce it's original value which is 5 and redo is generated to reply the transaction, means to reproduce the latest value which is 6.

Next, if this transaction is uncommitted and unfortunately instance crashes, during the next startup, Oracle(Specifically SMON process) takes the blocks which need to be recovered into the buffer cache and re-apply the changes using redo logs and if someone tries to read the row that you had modified before, Oracle simply rolled back the data block containing the row to its original value which is 5 using undo.

Update: The following alert log excerpt is extracted from this blog.

ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 80458 KB redo, 1721 data blocks need recovery
Started redo application at
Thread 1: logseq 490, block 52626
Recovery of Online Redo Log: Thread 1 Group 1 Seq 490 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 491 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 492 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 15.26MB
Completed crash recovery at
Thread 1: logseq 492, block 31015, scn 6082434
1721 data blocks read, 1721 data blocks written, 80458 redo k-bytes read
Sun Jan 09 00:50:27 2011

This clearly shows that block number 1721 needs recovery. And in order to do that the block is loaded into the memory(buffer cache) and redo is applied. The block is written back to data file after the recovery.

This is also explained in(Chapter-9 Redo and Undo, How Redo and Undo Work Together, Page ) Expert Oracle Architecture Third Edition book by Thomas Kyte.

From ASKTOM

Hi Tom,

  1. To do the crash recovery , Database needs datablocks(needed recovery) to be brought into SGA. May i know which process will bring them?.

Thanks

Followup

smon will read them and apply redo to them during the recovery phase.

Instance/Crash Recovery

  • @miracle173 Thanks for your reply, I think those blocks needs to be recovered should be be loaded into buffer cache and re-apply redo logs to recover it. And yes, Oracle doesn't build same buffer cache as it was before. – JSapkota Jan 4 '17 at 1:19
  • Thank you so much. You've helped me clear my doubts. But your example was fine to me until you said "the blocks which need to be recovered into the buffer cache and re-apply the changes using redo logs" I read, like @miracle173 said, that Oracle directly writes to datafile since the goal is to make the DB consistent again – Jason Krs Jan 4 '17 at 7:43
  • @JasonKrs: As per my understanding, the data block which needs to be recovered should be loaded into the cache to apply the redo logs and should be flushed to the disk in order to make it permanent. – JSapkota Jan 4 '17 at 8:27
  • I undertand. It makes perfect sense. In Oracle doc ->docs.oracle.com/cd/E11882_01/server.112/e40540/…, scroll down to "Instance recovery phases" : it says "....reapplying .....to data files" on the first sentence. This is what made me think it was a direct write to data files. But making changes in buffer seem more efficient and follows Oracle logic. I'll AskTom this and if I get an answer I'lll let you know – Jason Krs Jan 4 '17 at 8:59
  • I think, my previous commment is wrong and @JSapkota is right: During recovery Oracle works as usually: Blocks are modified in the buffer cache and written to disk by the dbwriter processes. This is the better way for the same performance reasons as in the usual case. I did not find anything about buffer cache size in the backup manuals. ... – miracle173 Jan 4 '17 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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