Oracle Database Instance Recovery Tutorial

Is It Possible to Corrupt an Oracle Database?

No, You Cannot – Not If It Is Properly Administered

Why? Because the mechanism of redo and undo is the best yet developed on this planet.

In this tutorial, Oracle Certified Master John Watson will explain redo, undo, commit, instance recovery and demonstrate why your database can never lose one row of committed data.

Learn what actually happens, in memory and on disc, when DML is issued. John will demonstrate that instance recovery is automatic, and unstoppable.

This is critical and essential knowledge for all Oracle Administrators and DBA students.

Presenter: John Watson, Oracle Certified Master

This free tutorial is segmented into fourteen separate lessons:

  1. Introduction to Oracle Certified Master John Watson and SkillBuilders (1:44)
  2. Tutorial Agenda (2:40)
  3. The Database ACID Test (3:09)
  4. Physical and Logical Corruption (1:37)
  5. Demonstration – What Does Oracle do when DML is Executed? (9:47)
  6. Commit and the Log File Sync Wait Event (1:18)
  7. When Why and How Does Oracle Write to Disk (1:58) (click on video below)
  8. What About Checkpoints? (1:16)
  9. Discussion about COMMIT NOWAIT (3:46)
  10. Lecture – What Oracle Database does when DML is Executed (7:22)
  11. The Oracle Instant Crash Recovery Mechanism (6:24)
  12. Principles to Remember (0:52)
  13. Oracle – The Incorruptible Database (1:54)
  14. Next Steps (0:47)

Date: Jun 4, 2013

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.


When Why and How Does Oracle Write to Disk

Oracle Instance Recovery Tutorial


Module 7 – When Why and How Does Oracle Write to Disk


>> John:  When the log writer write, as discussed, on commit. That’s to enforce transaction durability and that’s a straightforwardly Oracle’s definition of a committed transaction. The change vectors needed to recreate the transaction or to roll it back are in the online redo log. 


As we’ve seen, we get both change vectors for undo and redo do go to the online log. But apart from that, the log writer will also write until the log buffer is 1/3 full and that’s for performance reasons. That will force the log writer to string change vectors to disc in very nearly real time anyway. As we’ve seen, it’s virtually is real time but that would be another trigger to force it to write to disc. 


Most interestingly, it will also write the entire log buffer committed on uncommitted changes just before database writer writes anything. Why is that? It’s so that we can guarantee that if database writer decides to write an uncommitted transaction, log writer will always have the change vectors needed to rebuild the undo segments that can roll back that transaction. I will demonstrate and show in the next slide. 




So when does database writer writes? When we need to free up space in the buffer cache every 3 seconds. But remember for these reasons, it won’t write much. You might have 200,000 changed buffers in memory and database writer might write a few hundred of them. It writes as little as possible as rarely as possible. Why? There’s no point in writing data that’s really busy. 




And a checkpoint is the way I forcing it of course. All the system checkpoint and that writes all buffers to disc. There are some other reasons of course, but that will do for now.


Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!