Tip:
Highlight text to annotate it
X
Oracle Instance Recovery Tutorial
Module 5 - Demo - What Oracle Database does when DML is Executed
>> John: I want to run through what actually happens when we execute DML, what happens
on disc, what happens in memory?
There's some assumed knowledge here by the way. I assume you are all database administrators.
This is some assumed knowledge. What I think I'll do at this stage is demonstrate what
happens first and then return to the slide.
[pause]
This is actually standard 11g 11203 database by the way. Nothing special about it at all.
I'll connect to my database as user Scott and I'm going to create a little tablespace.
Create tablespace. Now I have a small tablespace.
[pause]
Datafile.
[pause]
I'll call the datafile
[pause]
Small1.dbf*size 128k. It can't get much smaller than that.
You'll see why I'm creating a tablespace specifically for this demonstration in a second. Then within
that tablespace, I'll create a very simple table.
[pause]
A very simple table t1 with one column c1 of varchar2 type.
[pause]
Now, insert a row into the table. Insert into t1 values and I'll insert myself.
[pause]
I'll even commit myself. So I am now committed. But what's actually happened on disc? Let's
take a look.
[pause]
There's my datafile small1.dbf. I've inserted myself and I've committed myself - is there
anything on disc at this stage? I'll use the strings utility to extract just the text strings
from a binary file and I'll string small1.dbf and grep out john.
[pause]
It's not there. That's the first example of data corruption. I've got a committed transaction
and it does not exist in the database. It simply isn't there.
[pause]
Right now my database is missing a committed transaction. That's straightforward corruption.
But it gets worse. I'll insert another row. I shall insert a row. In this case, I'll put
in Susan. Susan can join my database. Nice.
[pause]
Uncommitted transaction. I've got a committed transaction - apparently it's not in the database
- and uncommitted transaction as well. Is that in the database? It certainly shouldn't
be. You see if Susan exists in the datafile and sure enough she doesn't. But what if Oracle
decides to write some data to disc? I'll go through the algorithm with you shortly.
Let's just assume - I'm going to force Oracle to write to disc at this point. Alter system,
checkpoint. Force a writer buffers to disc. What's happened now? Susan is there.
[pause]
She exists in the datafile. This is bad a corruption as you can get. You think, "What's
happening?" My committed transaction that inserted john was not on disc. My uncommitted
transaction that inserted Susan is in the database. This is a pretty serious state of
affairs. Is it a problem? SQL*Plus disconnects from another session.
[pause]
If I select * from t1, everything is fine. I'm getting proper transaction isolation.
My transaction has inserted John. We don't know how durable it is, but apparently just
pretty durable. It's there. It's visible even though it is not in the datafiles and my uncommitted
transaction is insert Susan is totally isolated so nobody else can see her. Of course, from
the session that did the insert that session is of course allowed to see the uncommitted
transaction.
[pause]
Take a look at the next stage. What has happened within the redo log? I'll go through the redo
log contents and then we'll see how this is being implemented.
Select * from v$log
I want to see which is my current log file group. My current log file group is group
1. Group 1 consists of that file there, redo01.log. Let us see what there is in redo01.log.
[pause]
I'll grep out John. Does he exist?
[pause]
Yes, he does. So the change vector that inserted John does appear to be in there.
What about the change vector that inserted Susan?
[pause]
She's in there too.
Maybe you can make things a bit more complicated. Build up a bit more of an idea of what's happening
on disc and then run through the theory.
[pause]
Let's say in this session, I shall update t1 set c1 =. I'll put Dave in my database
instead of me. C1 = john. I do that update.
[pause]
Uncommitted.
[pause]
What do we now see in the redo logs, things get a bit more complicated.
[pause]
John has two entries now in the redo string. One will be the entry when he was inserted
into the table. The other will come from the undo segment when I updated the row, which
we'll come to in just a second. Of course, Dave is in there too.
[pause]
What's on disc? Right now, if we look at John as the person who exists in the datafile.
[pause]
That's a checkpoint to force a disc write. Alter system checkpoints.
[pause]
What's in the datafile now?
[pause]
John's gone. He's no longer in the datafile, but Dave is. Now I have my uncommitted update
that's apparently wiped John out from the database.
[pause]
What's going to happen at this point if we have a problem and we grep the database?
[pause]
What I've demonstrated, we have the log writer, we have the database writer. The log writer
writes on a very, very aggressive algorithm. Change vectors are written to the online log
in very nearly real time. When you say "commit," it is real time. As you've seen, pretty much
as soon as I did any change, the change vector immediately went to the online log - immediately.
So I'll just commit that transaction, stop any more confusion. Do another update.
[pause]
I'll change "Dave" to "Lynn."
[pause]
That's uncommitted.
[pause]
We get this ready in advance.
[pause]
Grep Lynn. No mention of course. Do my change. Set c1 = lynn where c1 = dave.
[pause]
In virtually real time, the change vector of the update is there. Alter the change vector
to Dave.
[pause]
He was there as well. Right. What's going on?
Log writer, as demonstrated, writes in a very aggressive algorithm. Your change vectors
go to the log file in nearly real time. When you say commit, believe me, it is real time.
The session hangs when you say commit until the vectors have gone to the online log.
[pause]
Note the online log change vectors are both committed and uncommitted changes.
[pause]
Database writer by contrast, also as demonstrated, writes in a very lazy algorithm. I've changed
a row to Lynn. But those Lynn exist in the database, not at all.
[pause]
Database writer writes it on a lazy algorithm as little as possible, as rarely as possible.
Why? Because disc are always back to performance. Blocks are written only if they conform to
two criteria. They've been changed - there's no point in writing data that hasn't been
changed - and they're idle, i.e. no one is using them.
This choice of which blocks to write is not related to commit. End result? Your database
is always corrupted as demonstrated. The datafiles on disc may be storing uncommitted changes.
They've been missing committed changes.
[pause]
Does this matter? No. It doesn't, unless the instance crashes.