Thank you!
Nitin, March 04, 2011 - 11:33 am UTC
Thanks a lot for confirming this and more importantly, proving it.
Your query will help in predicting the amount of redo being generated for "very large quarterly/annual processes" that the DBAs need to be notified about in advance. They ask about the size of additional archivelogs that these occasional processes will generate so that they can ensure adequate disk space to store this additional volume of archivelogs.
Using this query, we can have better projections on the size of archive logs for PROD (from our tests in STAGING).
reason for the need of redo
A reader, March 09, 2011 - 5:25 am UTC
I have read your book EXPERT ORACLE DATABASE ARCHITECTURE(chapter 9 redo and undo)and i have made an assumption(as that is not touched in the book)that redo should not be generated for rolling back(Im wondering what is the need to do so).
but this post is contradicting.
I have confused a lot in understanding this please help me in understanding.
Lets take an example.
an active transaction T1 is proceeding.now the following will happen.
1. Redo to roll the transaction forward in case of failure.
2. redo for undoing the transcation in case of failure.
3. Undo(in block buffer cache)to rollback or flashback or read consistency.
4.the modified data block(this will also be in block buffer cache)
5. rollback is issued which will mark the end of the transaction(I assume that a transaction ends when a commit or rollback is issued. please validate this assumption too).
6. LGWR will write to disk that this transaction has rolled back.
7.using the undo generated the data block will be modified(T1 will be rolled back).
8.now the database encounters an instance failure(before flushing it to the disk).
my question is even if we didnt generate the redo while rollingback we can roll forward the transaction and also the undo for the transaction when the database is started next time.
from the log we will come to know that the transaction is rolledback and we can undo it.
Then what is the need for generating redo while rollingback? (Please help in understanding even if the question is silly.)
March 09, 2011 - 9:37 am UTC
and i have made an assumption(as that is not touched in the book)that redo
should not be generated for rolling back(Im wondering what is the need to do
so).
change your assumption. Redo is generated for all operations that need to be re-done in the event of a crash. If you rollback, you are making changes - a rollback is a logical operation - if you insert a row, rolling back involves deleting it. If you delete a row, rolling back involves inserting it. And so on.
Look at the answer to this very question. It demonstrates that REDO is generated for ROLLBACK.
when you make a change - we generate redo to redo (physically) that change. We also generate undo to undo (logically undo - not physically).
We modify the block in the buffer cache.
If you rollback - we again modify the block - generating more redo to redo the undo application.
We have to do that because the block could be modified by someone else AFTER we start rolling back, but before we finish rolling back.
Every block can have a big mixture of completed transactions, in flight transactions and 'rolling back'.
You are thinking too "single user", thing hundreds of concurrent users all intermingled. We have to sequentially replay everything that happens to a block during recovery - including the undo that was applied.
Please remember - always remember:
Things work the way they work, not the way we THINK they should work, not the way we THINK we would have coded it. When faced with pretty compelling evidence that our assumptions are incorrect - we need to re-adjust them :)
Thanks
A reader, March 10, 2011 - 6:18 am UTC
Thank u TOM.I dint expect the reply this much sooner.Thanks for ur time.
The most important thing i missed out is a multi user environment.
The book is great to read especially for a beginer of database or Oracle like me.
Hope you write books on DATABASE CONCEPTS as well.
Rollback and Commit
Kangy, April 05, 2011 - 5:59 am UTC
Hi Tom,
Can you please confirm my understanding about rollback and undo?
1. The rollback means, modify the blocks (by applying undo) to bring it back as it was and then commit it?
2. the rollback also increments the SCN?
3. If a transaction is long, part of the redo records may be written to redo log files even before commit/rollback. At the end, if the transaction is rolled back, it will not remove those already written redo records from the redo log files.
Thanks,
Kangy
April 12, 2011 - 12:11 pm UTC
correct on all counts.
technical nitpick on #1 however, we apply undo to logically bring back the block as it was - it will not be physically, bit for bit, byte for byte the same - but logically it will be the same block.
Redo/undo
aruna, July 28, 2011 - 5:07 am UTC
Hi Tom,
Thank you very much four help in clarifying doubts posted the people all over the world .
I have question regarding REDO and UNDO.
Before starting the execution of a transaction – Server process
1.Will find the addresses of the blocks required to be read to execute the statement.
2.Will get the address of FREE UNDO segment/blocks
3.will make up the REDO Record in PGA for UNDO blocks first – Is it done for each statement in the transaction (or) is it done for each block ?
And then it is copied to Redolog buffer
4.then copy the UNDO blocks into buffer cache and record the changes(before execution) on the UNDO blocks
5.will will make up the REDO Record in PGA for DATA blocks– Is it done for each statement in the transaction (or) is it done for each block ?
And then copy them to Redolog buffer
6.then get the DATA blocks into Buffer Cache and update them
Is it the above work flow correct ? and is this process repeated at BLOCK level (or) Statement level (or) Transaction level ?
Once again many thanks for your time and help
July 28, 2011 - 7:24 pm UTC
why not just understand it like this:
when you make a change to a block we
generate undo to be able to roll back that change
generate redo to redo the change to the block in question as well as the undo you generated
Your sequence has to be wrong. How can we generate the redo before we create the undo block we are generating redo for AND most important - how could your step 6 be dead last :) How would we know what undo or redo to generate if we never had the block in the first place.
Step back, just understand it at the level I've put it.
A reader, July 28, 2011 - 7:31 pm UTC
Sorry Tom,nI still could not understand it .. Could you please explain in detail?
Apologies for the trouble ..but please Sir ..
July 28, 2011 - 7:55 pm UTC
do you have access to my book "Expert Oracle Database Architecture"? if so, read the chapter on undo and redo - I go into some detail there. Don't know how to say it any easier than:
when you make a change to a block - we generate UNDO to undo the changes if need be and we generate REDO to redo them if need be.
You will change the block before you generate the full undo bits and bytes, you will change the block before we can generate all of the redo bits and bytes - your ordering is wrong above.
the only concept you need to know however is:
when you make a change to a block - we generate UNDO to undo the changes if need be and we generate REDO to redo them if need be.
best oracle tutor
subhasish, August 13, 2015 - 9:03 am UTC
hi tom ur explaination really helped but cn u explain the statement
during an insert - we have to generate undo equivalent to "delete+rowid"
during an update - we have to generate undo equivalent to "update this rowid to have these before image values" (undo = rowid + column before images)
during a delete - we have to generate undo equivalent to "insert at this rowid these values"