Transaction Understanding from SGA to Database

** I am sure this topic shall be interesting **

Guys, I have a thought running in me to understand the "Plumbing" circuit of a database transaction from the SGA to database level. Could someone comment on my understanding and questions. Kindly correct me if I am wrong:


1)When a user makes a transaction (UNCOMMITTED), say an UPDATE to a record - the original data gets inside the rollback segment. (Does it go through the SGA to the Rollback or directly into the Rollback Segment?)

2)Simeltaneously, the updated data gets inside the redo-log buffer (SGA) first. I believe the updated data also gets inside the database buffer (SGA)in parallel (Correct me if I am wrong).

3)Either at check point or at a COMMIT, the LGWR writes the UPDATED data from the redo-log buffer into the redo-log file. Also, during this, the DBWR writes the UPDATED data from the database buffer to the Database files. Besides, at this juncture, the Rollback segment gets cleared on COMMIT.

Is this understanding of mine correct?

----------------------------
----------------------------

I also was confused by another theory which differs slightly:

When an UPDATE happens, the updated record doesn't go to the database buffer but to the datafile directly with a marked as "For Update". The original record will still be there in the database besides this cloned record (with a flag as "For Update"). Upon commit, the original record is deleted and the UPDATED record's flag is reset to NORMAL indicating it as a normal permanent record.

Please folks, lets all discuss this to understand the operations. This is a pivotal concept in Oracle and I believe we must possess 100% clarity in the understanding.

Awaitng all of your interactions upon this.

Thanks in advance.



[Edited by dbafreak on 03-09-2002 at 01:00 AM]

Strange that none so far are interested in this topic.

I think it works like this :

a change is made to a block.

The new and the old block are recorded in the rbs.
--> changes to the rbs are logged in the redo log buffer -> written to the redo file
--> the rbs is not directly changed, but db_block_buffer blocks is updated to reflect the change to the rbs.
--> when oracle sees fit, it writes the dirty buffers to the db files

The data block is changed
-> again logged in redo log buffers -> redo log files
-> block changed in db_block_buffers -> later written to data file.

when a commit is done, the data that is already written in the block_buffers and/or data files stays there. The only thing that happens is oracle marking the transaction as commited ( do not know how )

When a rollback is done, the 'old' blocks are read from the rbs and the blocks are updated with the 'old' values
--> written to block_buffers -> written to data file
--> also logged in red olog

This is how I think it works

Also eager to see many reactions.

Gert

Interesting! Would like to see more replies. This has been one of my favorite areas of discussion and believe me I've asked this question to atleast 15 DBAs and each gives a different explaination:-)

Originally posted by dbafreak:

Quote:

---

(Does it go through the SGA to the Rollback or directly into the Rollback Segment?)

---

There is no area in the SGA catering for Rollback segments. Rollback has the old data written on it. The new data gets directly written to the concerned db block (which has been read into the buffer for update) and later gets transcribed to hard disk on checkpoint/ timeout. In case the user wants to rollback the transaction, oracle reinstates data from the rollback segment.

Originally posted by dbafreak:

Quote:

---

When an UPDATE happens, the updated record doesn't go to the database buffer but to the datafile directly with a marked as "For Update". The original record will still be there in the database besides this cloned record (with a flag as "For Update"). Upon commit, the original record is deleted and the UPDATED record's flag is reset to NORMAL indicating it as a normal permanent record.

---

The marking thing is all bull.
Considering that a part of datafile (the concerned db block) is read into the db buffer, you can loosely say that the changes are made directly to the datafile. However, to say that updated record does'nt go the database buffer is wrong. The data gets changed in the database buffer and later these 'dirty' block buffers are written to disk.

[Edited by Raminder on 03-11-2002 at 12:35 AM]

Very nice Raminder. Thanks for clearing the issues.

Another interrogation:

When the server crashes or say during a power shut down, assuming ONLINE Backup -- Can we get the uncommitted transactions from the redolog files? I believe that redo logs only have committed transactions. In which case, whats the difference between the backed-up DATA FILES and backed up REDO LOG FILES? Please explain.

OK. Before I start, one careless error crept in my earlier post (since edited), that data is written to datafile on checkpoint/commit. Commit has got nothing to do with writing data from db buffer to datafile. (Although it does cause LGWR to write data from log buffer to online redo logs). DBWR process (es) write dirty buffers to disk on checkpoint/ timeout or when the number of dirty buffers cross a threshhold.
Originally posted by dbafreak:

Quote:

---

When the server crashes or say during a power shut down, assuming ONLINE Backup -- Can we get the uncommitted transactions from the redolog files? I believe that redo logs only have committed transactions. In which case, whats the difference between the backed-up DATA FILES and backed up REDO LOG FILES? Please explain

---

Uncommitted transactions will be lost.
Redolog files have all transactions which have been written to online redologs by LGWR (Both committed and uncommitted, less those still in log buffer), stored transaction-wise along with their SCN.
Datafiles have the data upto the last Checkpoint. In case of a crash, and subsequent startup, SMON detects that instance recovery is required because checkpoint sequence numbers in datafile headers are not synchronised with online redologs.redo is applied first from the redo logs (roll forward) followed by rollback upto last committed transaction.

Thanks Raminder. But just a clarification:

Your first statement says UNCOMMITTED will be lost. The very next immediate statement says Redo Logs have both COMMITTED and UNCOMMITTED. Can you pls substantiate. Otherwise whatever has been shared from you has been of valuable importance. Thanks again.

Since there will be rollback after roll-forward, all uncommitted transactions will be purged when the database opens-up after an instance crash. Of course, redologs might contain several transactions uncommitted at the the time of crash, but can they be recovered? I am not sure.

Very interesting Raminder.

However, if uncommitted cannot be recovered from redo logs, what advantage has redo logs over datafiles? Am I missing anything here Raminder? Kindly advise.

I would also request other gurus here to give their inputs.

[Edited by dbafreak on 03-11-2002 at 11:31 PM]

Datafiles do not contain all the committed transactions upto crash. During normal DML, the user is working in the db buffer (which makes transactions quick). Oracle guarantees that committed transactions will not be lost upon instance failure. This is achieved through redo logs.

Redologs and archived redo logs also contain info for media recovery in case of data corruption or loss of datafiles. This is the advantage of redo logs.

Thanks Raminder. That should help me.