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)
  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) (click on video below)
  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.


Lecture – What Oracle Database does when DML is Executed

Oracle Instance Recovery Tutorial


Module 10 – What Really Happens when DML is Executed


>> John:  What I’ve been describing, the difference in the instance and the database. The instance has real time data but the datafiles may be either ahead of or behind of reality. Remember. Why is that? Because the database writer will not write data to disc and this has got a good reason to. Commit is not considered a good reason. It is a good reason for the log writer for the change vectors, but not for the database update.


The datafiles may well be missing incomplete transactions, be out of date. They may be storing incomplete transactions being ahead of reality. Remember you’ve got the case of rollback and that means the rollback will have to update things on disc as well. 




The online log has the change vectors – committed or not – including change vectors applied to undo blocks. 




I’ve been attempting to draw animations. I’ll try to give a graphical description of how this actually works. In this series of slides, it’s not really a proper animation here. What I’m trying to draw at this point – up here I’ve got a system global area. This is my instance. There are two major structures – database buffer cache, redo log buffer. I’m not bothered with the shared pool at the moment.




Down on disc, I’ve got the database. The database consists of the online redo log files and my data files. The database, the online log. I put in just two background processes – the database writer (of which you might have several) and the log writer. These are the two background processes that connect the instance and memory up here with the database down here on disc.


Current situation, everything is pretty static. I’ve got a datafile with tables and datafile with undo segments. I’m interested in just two blocks of the datafile. 


As a block containing a record for Susan – it says salary is 40 and a block containing a record for John. It says his salary is 10.




In my undo tablespace datafile, I’ve got a couple of blocks that are available for use. I put them down here as being completely empty.




What’s in the buffer cache? Nothing at all.




That’s my starting point for talking about what happens on disc and in memory. I demonstrated it, now we cover it in theory.


First step, Mr. Blue issues a DML statement “Update emp set sal=20 where ename=john.” That’s the statement his user process generates and turns to his server process for execution. Remember, grant server splits use a process such as SQL*Plus generates, SQL server process executes it.




That’s the statement we send to the server process. How do we then execute it?




I’m not bothered with parting. The first stage of execution, we write to the log buffer the change vectors that we are going to apply to the buffer cache. We are going to apply two change vectors.




We are going to update John’s row to 20. We’re going to write the old version of his salary to the undo segment.


How do we do that? We put the change vectors in the log buffer and we copy the blocks into memory. We copy the block that says John’s salary is 10. It’s got John’s right up there. We find an available block, I’ll undo. Let me copy that up.




What have we achieved so far? We’ve written the change vectors to the log buffer and we’ve got the data in memory. Next stage, we do the updates. 




We write the change to John’s row. He’s now on salary at 20. And we write the old version of his salary to the undo segment.




What’s on disc? Nothing at this point. Uncommitted transaction. Of course, if Mr. Blue queries the emp table, he’ll see John’s salary is 20. If anybody else queries the emp table, Oracle constructs a read-consistent image using the redo saying that John’s salary is 10. Transaction isolation.




Make things a bit more complicated now and we’ll start a second transaction.




Mr. Red logs on. Mr. Red says, “Update emp set sal=50 where ename=susan.” 




Same process. We find Susan’s block on disc. Susan’s salary is 40. Copy it up. We find a block of undo on disc. We copy it up.




We write the change vectors to the log buffer. We’re going to update the table to 50. We’re going to update the undo segment to say Susan’s salary=40. Then we make the changes in memory.


Right now, I have two uncommitted transactions. What have I got on disc? Nothing. Remember, log writer does in fact write change vectors in very nearly real time. Almost inevitably, these four change vectors will by now be in the online log almost inevitably. But let’s take it as slowly as we can.




What will happen next? What if Mr. Blue commits his transaction? 




He issues the commit. At that point, his session is going to hang. This happens.




We write the commit record to the log buffer. We fill the log buffer sequentially. It’s actually a circular buffer but I couldn’t work out how to draw a circular buffer so I’ve drawn just filling from the bottom up.


We put the commit record in the log buffer. My session is hanging while log writer wakes up and wham the entire log buffer to disc. At this point in the online log, we have the change vector applied to John’s row. The change vector applied to undo segment that lets us roll that back should we need to. We’ve got the change vector applied to Susan’s row and the change vector applied to the undo segment that would let us roll that back to. And we’ve got the fact that the blue transaction is committed. So the online log has both committed and uncommitted transactions as I demonstrated.


What’s in the database? The datafiles, no change.




Does that matter? No. Because in memory, we’ve got the real time information. We can construct read-consistent images necessary and we know what’s committed and what isn’t. But maybe we then have a disaster.   




Before we have the disaster, take it a step further. Sorry, I was at this stage. I’ve just committed.




Next step, database writer for whatever reason, remember every three seconds you need to free some space in the buffer cache, whatever. Database writer wakes up and decides to copy a block to disc. Which block does it copy to disc?




It has decided to copy Susan’s block to disc. In the previous slide, that was the situation. This slide, boom. Database writer has written the uncommitted change to disc. This is my ultimately corrupted database. It is not storing John’s committed change. It is storing Susan’s uncommitted change.




No problem because in memory everything is fine.



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!