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


The Database ACID Test

Oracle Instance Recovery Tutorial


Module 3 – The Database ACID Test


>> John:  First, the rules to which a relational database must conform. There’s one overreaching rule, the database must not permit any damage to your data. No matter what happens it has to pass what they call the ACID test. 


Very briefly, I don’t want to spend more than a couple of minutes on this, because I’m sure all of you would’ve been in college much more recently than an old man like me and can possibly describe these things better. But just to make sure we are all on the same page of the manual as it were, I want to quickly to run through the ACID test. 


ACID. A stands for atomicity. 




One definition – a transaction must complete or fail in its entirety. It doesn’t matter whether the transaction is updating, one column and one row or updating a thousand rows and a hundred tables, the entire transaction should complete or fail. 




What is the transaction? The business [01:07 inaudible] will have defined the transaction structure and the database must guarantee that it cannot be broken no matter what happens. What could happen? Power cut, hardware failure, anything that can cause an instance crash. The database must guarantee that incomplete transactions are never seen and complete transactions are always seen. 


Second point, C for consistency. The database must guarantee that the data within it is always valid, valid in terms of business rules. At no point can there be anything visible in the database that breaks the integrity rules. Those rules and the integrity checks define declaratively by the DBA is a way they’re implementing the business rules within your organization. 




Then I for isolation. Any number of definitions of isolation, I’ll just pick up one or two of them here. The easiest – a transaction must be hidden until it’s complete. At which point, the transaction must be published instantaneously to all of the sessions. But until it’s complete, the changes must be isolated to the one session that’s seeing them. 




A follow on from this, a query must return the result set consistent with the committed state of the database when the query started. There are variations on this, but that’s Oracle’s definition. Recommitted and freeze the database as of the time the query started. 




Finally, D for durability. The database is not allowed to lose even one row of committed data. If it does, if your database can lose a row of committed data then you’re using a wrong database, perhaps even the wrong DBA. But that’s the rules of relational database. It must never lose data, it must never damage data. 




How do we implement these rules? Constraints of course are relational integrity rules. But the overall consideration, if it doesn’t conform to the ACID test then it’s simply isn’t a database.


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!