Is my understanding regarding transactions correct ?

I'm struggling to understand how transactions, redo and rollback all fit together.

My understanding of the process is as follows:


I issue an insert/update/delete, basically some DML. This grabs a slot in the transaction table slot in a rollback segment header which is initially flagged as ACTIVE (i.e. uncommitted) so the undo shouldn't be overwritten.

The "before" image of the blocks that are changed by the DML statement are written into blocks in the rollback segment.

Because these constitute changes to the rollback segment blocks these change vectors are written to the redo log buffer.

An ITL entry is created in the headers of the data blocks that are changed by the DML. This ITL contains a pointer to the transaction table slot in the rollback segment.

These changes to the data blocks are written to the redo log buffer.


Let's now say the user has not yet committed these changes, and LGWR now writes the redo log buffer contents to the online redo log files.

At this point I think I am OK. If the instance crashed then, on recovery SMON would firstly reconstruct the undo from the change vectors to the rollback block and reconstruct the changes to the data blocks but would then rollback the changes because the transaction table slot in the rollback segment header shows that this transaction is not comitted.

Is this correct?


OK moving on - what exactly happens when the user issues a COMMIT? The 9i concepts states that it writes the SCN to online redo log files? How exactly is this achieved - is it just a "line" in the redo log file and not a change vector?

My guess is that this is what happens:


When the user issues a commit, the transaction table slot in the rollback segment is changed from ACTIVE (uncommitted) to INACTIVE (committed). This constitutes a change to the block containing the rollback segment header. And this change is written away to the redo log buffers.

So when rolling forward using the online redo log files, for this transaction we have:

1) rollback segment header(uncommitted)
2) change vectors to reconstruct "before image" undo blocks
3) change vectors to change data blocks
3) rollback segment header(committed)


I'm sure this is an over simplified (and probably wrong) version. Can someone please clarify this for me?

My other major area of confusion in all of this is where do SCN's fit in, when are they allocated?

1) First question IS IT CORRECT - to my knowledge - yes it is
2) Moving on - I have never seen detailed explanation what exactly Oracle writes to the redo logs on commit. I accept it is just a line and not a redo vector. I believe no one out of the people that have created the algorithm knows
3) SCN (system change number) is just a number, which gets incremented at specific period of time ( in 10g I think it is 3 sec, in earlier versions it is 15)
It is used by the database to mark WHEN specific event occurs and to know which event happens after which. (in this case, if the SCN of your transaction is greater than the SCN of another one, that means your transaction came after the other). Oracle says this is "internal timestamp' Obviously the system time cannot be used for that, since someone can change it and mess up the entire database, so that's what Oracle does, get a counter, increment it each 3 seconds, and use it as a timestamp for everything.
Just to mention, for some period of time, Oracle saves in the control file the SCN to time mapping, so u can easily map a time to a closest SCN when doing say flashback or point in time recovery

Regards

Interesting Questions:

1. Yes this is correct to my knowledge as well.
2. What happens at a commit?
Your guess is indeed correct. But not only that, it is almost the same change vectory for a rollback.

3. SCN consists of actually a wrap, base and a Sequence values. And are used as bore has described.

Quote:

---

My other major area of confusion in all of this is where do SCN's fit in, when are they allocated?

---

SCN is generated after the commit statement is executed.