Hi friends,
When does the commited transaction in the redo log file be written to the data file.
cheese
anandkl
Printable View
Hi friends,
When does the commited transaction in the redo log file be written to the data file.
cheese
anandkl
Committed transactions are not written from Redo Log files to datafiles.
It is written from database-blocks of the "database buffer" in the SGA to the datafiles by the DBWR background process.
This happens during timeout or whenever "dirty buffers" get filled in the "Dirty Buffer List."
Committing Transactions
Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.
Before a transaction that modifies data is committed, the following has occurred:
Oracle has generated rollback segment records in rollback segment buffers of the system global area (SGA). The rollback information contains the old data values changed by the SQL statements of the transaction.
Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.
The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction actually is committed.
***Note: ****
The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits.
--------------------------------------------------------------------------------
/* AND ALSO TO YOUR PREVIOUS Q ABOUT THE TRANSACTION DEFFERING,ALTHOUGH IT IS CNSTR DEFERRING.......... */
Discrete Transaction Management
Application developers can improve the performance of short, nondistributed transactions by using the BEGIN_DISCRETE_TRANSACTION procedure. This procedure streamlines transaction processing so that short transactions can execute more rapidly.
During a discrete transaction, all changes made to any data are deferred until the transaction commits. Of course, other concurrent transactions are unable to see the uncommitted changes of a transaction whether the transaction is discrete or not.
The following events occur during a discrete transaction:
Oracle generates redo information, but stores it in a separate location in memory.
When the transaction issues a commit request, Oracle writes the redo information to the redo log file along with other group commits.
Oracle applies the changes to the database block directly to the block.
Oracle returns control to the application after the commit completes.
This transaction design eliminates the need to generate undo information, since the block is not modified until the transaction is committed, and the redo information is stored in the redo log buffers.
There is no interaction between discrete transactions, which always generate redo, and the NOLOGGING mode, which applies only to direct path operations. Discrete transactions may therefore be issued against tables that have the NOLOGGING attribute set.
COURTESY......
http://otn.oracle.com/docs/products/...5trans.htm#222
IT IS INETERESTING!
Tarry: You said "The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits"
Does this mean that -- those transactions that went to the disk (datafiles) before getting committed are still available say after a Power Failure? Can you kindly substantiate this?
Where would u find the info of these transactions which were written before being committed?
Or more specific asnwer to anand's Q about oracle's write ahead logging logic.
The database writer writes modified blocks from the database buffer cache to the datafiles. Since Oracle uses write-ahead logging, DBWn does not need to write blocks when a transaction commits. Instead, DBWn is designed to perform batched writes with high efficiency. In the most common case, DBWn writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.
Tarry: Since your post mentioned that even UNCOMMITTED data gets inside datafiles --
Lets say for instance you INSERTED 10 Rows into EMP Table and they were NOT COMMITTED. But lets assume these records went to the datafiles (as you mentioned).
Assume a power failure at this juncture. Next time when you start the database and select from EMP, do you think those rows would be there?
This question arised basically because you mentioned that even UNCOMMITTED rows go to datafiles, besides COMMITTED.
Hope I've made the question clear.
when checkpoint occurs
there are uncommited data on datafile and in case of a power failure smon will do instance recovery which includes applying redo logs, rebuilds RBS and rolls back uncommited transactions
They won't be SEEN. but the instance recovery initiated by the SMON will do the following.................
Due to the way in which DBWn writes database buffers to datafiles(TARRY SAYS: here i would assume that the write ahead logging forced by sga flooded with subsequent queries,and limited space in the db buffer,will prompt the DBWn to start writing modifictions the last firsts ...how don't ask me this is what oracle says, maybe the experienced oracle guys can explain why that happens) being read at any given time a datafile may contain some tentative modifications by uncommitted transactions and may not contain some modifications by committed transactions. Therefore, two potential situations can result after a failure:
Data blocks containing committed modifications were not written to the datafiles, so the changes may only appear in the redo log. Therefore, the redo log contains committed data that must be applied to the datafiles.
Since the redo log may have contained data that was not committed, uncommitted transaction changes applied by the redo log during recovery must be erased from the datafiles.
To solve this situation, two separate steps are always used by Oracle during recovery from an instance or media failure: rolling forward and rolling back.
Rolling Forward
The first step of recovery is to roll forward, that is, reapply to the datafiles all of the changes recorded in the redo log. Rolling forward proceeds through as many redo log files as necessary to bring the datafiles forward to the required time.
If all necessary redo information is online, Oracle rolls forward automatically when the database starts. After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log.
Rolling Back
The roll forward is only half of recovery. After the roll forward, any changes that were not committed must be undone. After the redo log files have been applied, then the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. This process is called rolling back. Oracle completes this step automatically.
Hope that explains it.
Wonderful Tarry. Amazing explaination. Thanks for the same.
However, this leaves another Question for me: If redo log contained committed and uncommitted data -- Why did they design such that Oracle Rolls-back after Roll Forward? Why not retain even the uncommitted?? Wouldn't this have been a big advantage during catastrophic instances? Tarry, I would really appreciate if you could shed some light over this.
dbafreak read Oracle Concepts, it's all explained there
Go to index and search for instance recovery
The real why would and might be debatable but consider it as a THEOREM.
No but ok here goes.....
The first step of recovery from an instance or disk failure is to roll forward, or reapply all of the changes recorded in the redo log to the datafiles. Because rollback data is also recorded in the redo log, rolling forward also regenerates the corresponding rollback segments. This is called cache recovery.
Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. Rolling forward usually includes online redo log files and may include archived redo log files.
After roll forward, the data blocks contain all committed changes. They may also contain uncommitted changes that were either saved to the datafiles before the failure, or were recorded in the redo log and introduced during roll forward.
Rollback segments record database actions that should be undone during certain database operations. In database recovery, rollback segments undo the effects of uncommitted transactions previously applied by the rolling forward phase.
After the roll forward, any changes that were not committed must be undone. After redo log files have reapplied all changes made to the database, then the corresponding rollback segments are used. Rollback segments are used to identify and undo transactions that were never committed, yet were either saved to the datafiles before the failure, or were applied to the database during the roll forward. This process is called rolling back or transaction recovery.
TARRY SAY: Thus the rolling bckwd first and forward later will defeat the purpose to attain transactional consistency.Something which is a matter of life and death by organizations.
Pando's right!
The concepts is a very good read! A real eye opener.
I read it every now and then to refresh my funda's(Fundamentals) during my exam preps..
But it's good to have Q's like these coming up they prompt you to look into the book from the practical viewpoint.
Tarry Singh
Thanks for the explaination TARRY. I feel we are missing something here.
When you said : "Thus the rolling bckwd first and forward later will defeat the purpose to attain transactional consistency.Something which is a matter of life and death by organizations"
I didn't mean the "order" of the processes....I never questioned on the ordering.
My fundamental question was "Why Rollback after Rollforward? Why not stop at the Rollforward point?? Why not retain the uncommitted data??"
The very motive behind this interrogation is -- If uncommitted data was available before the failure, why not still retain the uncommitted after the recovery? Why rollback?? Why doesn't Oracle stop after Roll Forward??
Hope I've made myself clear.
Maybe I am missing something very trivial here. I would appreciate if TARRY or anyone can shed some light on this.
Pando: I'm sure server concepts has a lot a knowledge in it (perhaps almost everything we discuss here). This is an interactive forum and makes it 100 times better than mere reading......my personal opinion.
[Edited by dbafreak on 03-20-2002 at 12:24 AM]
the thing is what are explained is almost exactly same as ceoncepts guide, it wont take you more than 2 minutes go to technet, click con documentation link then click on 8i then concept guide then index and look for instance recovery
Sure Pando. As I menioned earlier, IMHO it makes a world of difference between "interacting" and "reading".Quote:
Originally posted by pando
the thing is what are explained is almost exactly same as ceoncepts guide, it wont take you more than 2 minutes go to technet, click con documentation link then click on 8i then concept guide then index and look for instance recovery
Also -- "Interacting" saves time. For EXAMPLE -- Take the case of this very question I have asked in my previous reply to this post. Is there an easy way to find the answer in the concepts manual? NO! But here, there is; someone who has the knowledge will share it. Do you see any problems with my thinking here:-)
Besides, I don't see any reason for you get uneasy about it. Its been a healthy and enjoyable discussion between me and TARRY. Take it easy Pando. There's lots more to it than we think.
[Edited by dbafreak on 03-20-2002 at 03:08 AM]
what you are doing is ask for people look information for you.... I dont mind or care but I just see too many questions lately where people are just too lazy to just spend 5 minutes and find out themselves
mind you, did you try to find out yourself before ask?
Quote:
Originally posted by pando
what you are doing is ask for people look information for you.... I dont mind or care but I just see too many questions lately where people are just too lazy to just spend 5 minutes and find out themselves
It is still members' own choice. isn't it :-) C'mon Pando, I don't have to say this: No-one is asking anyone to go to concepts giude, do research and study and let the answer know. When we post questions here - we believe someone who knows the answer already will post the reply.
No. I didn't. Somehow I don't feel I should for this question. I have the liberty to ask, I ask. If someone knows the answer and feels like replying he will. If someone feels to go through the concepts manual, dig the answer and then reply - he still will. All these are individual interests. Why does it bother anyone like you in anyway....REALLY FAIL TO UNDERSTAND.Quote:
Originally posted by pando
mind you, did you try to find out yourself before ask? [/B]
Can someone please advise Pando to mind his own business!?!
PS: Remember Pando, you are only a moderator for this forum. It doesn't qualify you to interfere in one's own interests. Besides, questions like I've posted only fosters for a better growth of the board. It will never disspiate adverse effects. Does anyone dis-agree with me (moderators?)
[Edited by dbafreak on 03-20-2002 at 03:48 AM]
well if this is the way you use to learn the stuffs I am afraid it´s not the best if you rely on other people answering for you :)
Well dbafreak I think your fame will go before you then ... be yourself, I think is not a good idea to use others to answer things you don't want to find. All people will be bored of you quickly :D
Hello dbafreak
After going through Tarry reply u forgot to look under it which explains a good quotation
ONE MUST LEARN TO WALK BEFORE (S)HE CAN FLY
I hope u will end confronting with senior DBA's now.
Thank u
To accept "ethics" one needn't be a Senior DBA. One has to be "human"...that is all is necessary.Quote:
Originally posted by akhadar
Hello dbafreak
I hope u will end confronting with senior DBA's now.
Thank u
Its a shame that such seniors here are made moderators.
I shall not intervene any further. I have known certain senior members like Sambavan to be very matured. I just hope they will appreciate and accept my view points here.
As for others, I shall ignore them. No hard feelings, I shall resign out of this....its already developed too far (which I didn't intend).
I remember some 10 years back an engineer on board started a discussion about "what is it to be a great brain surgeon???Or a super Psychologist???
He argued how can one human brain judge another???
I read also somewhere about the IQ tests that it is adjusted by the examining authorities in order to compensate the male and the female rationalization techniques(again based on study,cases etc ,again judged by one or more humans).So that both genders can score reasonably.
I started writing a book in 97(it's still in the pipeline..no story telling like jk rowling or contused like rushdie but about hindu mythology,human brain,etc). It'll probably help us to look at every definition that is a definition from the 6th dimension.
Bottom Line:Don't judge people , knowledge is up for grabs, now more than ever. Ofcourse pando's advise is good but so is dbafreak's opinion of interactive learning. Oracle is one of the many manifestations of a human brain, the power of reasoning.
Lets just give each other space and keep picking the good things out of all the inputs.
Tarry Say: One who seeks will find!
http://www.dbasupport.com/forums/
Very well said Tarry. Now, lets come back and get life to this wonderful thread. Could you please spare some time for my question. I am struck with this here. Kindly explain.
Listen, you just sleeped to some principial considerations, here, and this isn't right ok, because the purpose of a forum is people to communicate. If somebody have something to ask, asks, and if somebody feels like wants to answer, answers. Of course, the questionner takes the risk to ask too frequently simple or boring thinks, but who we are to judge??? Maybe he/she reads tones of documentation, and still doesn't understand!
It is simplier to say that:
IF a commited transaction isn't reflected in data written on disk, because of the non-syncronous wrtiting policy of DBWRn, it HAVE to commit at recovery time. If an uncommited transaction IS reflected in data on the disk, it HAVE to rollback those data changes. This is where the rollback phase happens.
The commited transactions never rollback.
Maybe would be more usefull if we introduce the expression "finished transaction" - this meaning either rollbacked or commmited.
A failure may occure at any time, in very different situations. All you have to know is that the DBWR, LOGWR and server processes are mostly INDEPENDENT one of another. Only checkpoints are introducing some consistency here, but this consistency last VERY short time on a highly loaded database.
Oracle needed a very safe mecanism to manage the sincronization and the consistency, or let's say, transaction processing, a very safe and simple mecanism, but a performant one. The DBWR HAVE to be asincronous, because this is what a database engine does!
In think know very well which is the missunderstanding of dbafreak. And, in my oppinion, the correct hint for him/her is: think about recovery ONLY FROM THE LAST CHECKPOINT! And you'll undestand, I think.
If you still don't understand, put the question in more specific way, because I can't give you a very ellaborated answer to a non-ellaborated question.
I hope I was for help. Bye!
Thanks for the time and patience ovidius. I shall make my question very clear. Kindly answer me:
1)Lets assume I have updated 100 rows of data, but did not commit. Assume a power failure happens or say the DB crashes etc.
2)Now we have redologs. So we first Roll Forward to bring the database forward. At this juncture the database contains both committed and uncommitted data.
3)The next step we do a rollback, so that the database cleans the uncommitted transactions and retains only the committed.
I understand this is the way Oracle is designed.
MY QUESTION:
Observe points (2) and (3). At the juncture of point (2), we have both committed and uncommitted data in the database. Its exactly the same state as when the DB crashed / Power failed. That is -- the 100 rows I updated are still available at this juncture (Assuming they were written to the datafiles).
Now by executing point (3) - which is what Oracle does - I lose all these updates as its rolled back. Which means I have to redo all my 100 updates again.
SO -- Why doesn't Oracle stop with step (2) itself? That way I can retain my uncommitted data. Why does Oracle have to rollback thereby forcing me to redo all my updates again.
Hope my question is clear. Kindly answer anyone who knows the answer.
Thanks in advance.
ok
now assume that you've executed a script for updating two tables which are virtually linked together. now when the power failure occurs, 100 rows of first table are modified but only 50 rows of second tables are modified and 50 are still pending. so do u think that ORACLE should not rollback this transaction. cause the last state i.e. before updation started is a consistent state and where the oracle is running without any inconsistency and now after the partial updation the database is not in a consistent state.
to give more expn, oracle is said to be in consistent state when the SCN is same in control files, redo records and data file headers. i'm i right??
if i'm still missing something let me know
It is very simple: the session which made the changes must either commit or rollback. Even the same oracle user can't commit changes made by this same oracle user in another session. Even the most superpower user SYS can't commit other's sessions transactions.
If the session that made the changes unexpectedly dies, those changes must automaticaly be rolled back by PMON, because Oracle knows that session will never be established again, so the original author of the changes will not be able to commit or rollback. So the only safe method is to make the system look like those changes were never actually entered.
Now after instance recovery if Oracle would stop after rollforward, there would be some changes pending there forever, as the sessions that made them are dead and will never be alive again. So what other choice does oracle have but to rollbackj all those changes? It can't simply commit them for many reasons. For example, because of the possible referencial inconsistencies caused by unfinished transactions. Or because maybe you as the author of those changes might decide that you actually want to rollback those changes if the instance had not crashed...
Jmodic and Santosh: Thanks a lot for the answers. This is what I was looking at exactly. Thanks again. This answers my question very nicely.
very well explained Jurij. now thats what i call a very technical answer.
Oh, dbafreak, I now understood your point. We didn't understood at the begening because NOT any single RDBMS server would react like you'd like :). An unfinished transaction is NEVER commited by the server itself, by its willing. It is a deontological problem, a principle problem, here. The server commit only the explicitly commited transaction, or the transaction that the technology EXPLICITLY explined to you they will do an implicitly commit (like DDL, for instance).
The database server could NOT know how long should your transaction be. If it would commit an unfinished transaction, it by itself would be putting the data it is keeping as a main task into an UNCONSISTENT state. Maybe the rows you just changed have some business rules associated, as they modification implies willy-nilly some other modifications that didn't occur because of that power failure, and from the business rules point of view, the data are inconsistent. And keeping data is what a database do. It is unacceptable for a server to commit itself some changes. WE don't want it to do so. This is why the transactions were invented!
And for this purpose, the ONLY solution, accepted by ANY RDBMS system, is to rollback all the uncommited transactions in course at the failure moment.
From my knowledge, there isn't any RDBMS system so smart that is capable to RESUME on another further connection an unfinished transaction.
It could be possible to be some High-Availability cluster systems that could take the load if one server fails, but I don't know if this kind of systems resume by itself a transaction, or the application itself is written in a way that let the system to rollback and THEN re-initialise the transaction on the other server. I don't know.
I hope I was clear now. If not, ask again.
Bye,
ovidius