This content has been marked as final. Show 8 replies
There are all there....
i went through the document.I have following doubts:
the document says befire commiting
1.Oracle has generated undo information. The undo information contains the old data values changed by the SQL statements of the transaction.
2.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.
3.The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction is committed
I could understant 1. But I could not understand that in redo log buffer what it maintains- the new value(updated one) or the transaction. Like if i modify a column by adding 2, say earlier it was 1 then after modificatin it will become 3. In this canse UNDO tablespace will contain the value 1, but I could not understand what value will REDO log buffer contain: 2 or 3.
Redo log contains change vectors. That is, the little bits of data that are changed in a given data block of a given file. so, if you add 2 to a value of 1, the new value is 3. So, the change vector will have the file id, the block number, the offset into the block, and the value '3', since that's the new value. This is of course a gross oversimplification, but I hope it gets the idea across.
Further to that,
the undo records before-image information to allow things to reverse to the 'before change vector' state as required by a rollback statement.
Interestingly, this requires a change vector to be created for the undo that is also recorded in the redo.
http://www.juliandyke.com/Presentations/TransactionInternals.ppt provides the details.
Good point, Hans.
The way I try to explain it is this:
Changes to UNDO are changes to blocks in the buffer cache, no different than changes to other segments, like table or index. Any change to the block of any segment must be protected by redo. So, redo must be written before the block change, regardless of if said block change is and undo block or other type of segment block.
Why we need undo, can't we rollback using Redo log buffer?
Redo is generated and stored sequentially. Suppose you start an update at 9.00am. Then you get called to a meeting, have a cupo of tea, finish the meting, go to lunch, and return to your transaction at 1.00pm. You then decide to rollback. If we handled rollback with redo logs, we'd now have to plod sequentially through every piece of redo generated since 9.00am... and plodding through 4 hours' of redo is not going to be quick.
Worse, some of that redo will have been copied out to archive logs. And although we know where we wrote those archives, we don't know they're still there: once an archive has been written, it's a dead file as far as Oracle is concerned, and there's nothing to stop you or I moving the files, deleting them, zipping them, putting them onto a tape or what have you.
So now not only do we have to plod through four hours of redo, we have to keep asking you 'please supply this log, now this one, now this one'.
It would, in short, be do-able but horrible.
Undo, on the other hand, is written into undo segments inside the database (so they can't go missing and we know where it's stored without having to ask you), and there is a direct link between your transaction and the location of its undo (the transaction slots in the data blocks you modify contain the undo segment address containing your undo), so instead of having to plod sequentially through every bit of undo that's ever been generated since 9.00am, you know exactly where to go to get to your undo. Access to undo is, in a sense, "direct random access", not sequential and slow guesswork.
A lot of commercial RDBMSes, it is true, have a single transaction log in which the equivalent of our undo and redo is stored as one thing. It was a stroke of sheer genius on Oracle's part to split the two into separate entities which behave very differently and make rollback such a (relatively) trivial exercise for Oracle.
One interesting side effect of that decision. In Informix and SQL Server, to name but two, your transaction is not allowed to generate enough transaction log information to occupy more than half of the transaction log. That's because if you were to decide to roll back, you'd have to generate the other half, and you can't generate new information on top of the old information you're still trying to read to work out how to roll back in the first place. So what those databases do is simply blow up your transaction when it generates the 50%+1 transaction record. It's called the "long-running transaction problem" and Oracle simply doesn't have an equivalent, precisely because undo needed to reverse a transaction is kept entirely separate from the redo needed to recover it. The worst than can happen in Oracle is that you run out of physical space for your archives or the undo tablespace... at which point you hang or are blown up: but then no-one can do anything to avoid purely physical constraints like that. The point still remains, though, that there's no artificial or arbitrary hard limit such as "50% of your current redo log" or "50% of your undo tablespace" on the size of your transactions.
Another stunnig explanation ...Thanks Howards
>>Why we need undo, can't we rollback using Redo log buffer?
They exists for different purposes. In addition to the explanation of the other members of this community, bear in mind that the redo log files are set of files that protect altered database data in memory that has not been written to the datafiles and are primarily used for instance recovery purposes that is the process of applying the contents of the online redo log to the data files in order to reconstruct any changes made subsequent to the most recent checkpoint. In fact, the redo log files contains information from which transactions and their rollback information can be created and these records contain information about each change to any block and are stored in the order in which these changes were made.