Now let’s take a look at how redo and undo work together in various scenarios. We will discuss, for example, what happens during the processing of an INSERT with regard to redo and undo generation and how Oracle uses this information in the event of failures at various points in time.

An interesting point to note is that undo information, stored in undo tablespaces or undo segments, is protected by redo as well. In other words, undo data is treated just like table data or index data—changes to undo generates some redo, which is logged (to the log buffer and then the redo log file).
Why this is so will become clear in a moment when we discuss what happens when a system crashes. Undo data is added to the undo segment and is cached in the buffer cache, just like any other piece of data would be.

Example INSERT-UPDATE-DELETE-COMMIT Scenario
For this example, assume we’ve created a table with an index as follows:
create table t(x int, y int);create index ti on t(x);

And then we will investigate what might happen with a set of statements like this:
insert into t (x,y) values (1,1);update t set x = x+1 where x = 1;delete from t where x = 2;

We will follow this transaction down different paths and discover the answers to the following questions:
•\ What happens if the system fails at various points in the processing ofthese statements?
•\ What happens if the buffer cache fills up?
•\ What happens if we ROLLBACK at any point?
•\ What happens if we succeed and COMMIT?

The initial INSERT INTO T statement will generate both redo and undo. The undo generated will be enough information to make the INSERT “go away.” The redo generated by the INSERT INTO T will be enough information to make the INSERT “happen again.”

After the INSERT has occurred, we have the scenario illustrated in Figure 9-1.

Figure 9-1.  State of the system after an INSERT

There are some cached, modified undo blocks, index blocks, and table data blocks.
Each of these blocks is protected by entries in the redo log buffer.
Hypothetical Scenario: The System Crashes Right Now
In this scenario, the system crashes before a COMMIT is issued or before the redo entries are written to disk. Everything is OK. The SGA memory area is wiped out, but we don’t need anything that was in the SGA. It will be as if this transaction never happened when we restart. None of the blocks with changes got flushed to disk, and none of the redo got flushed to disk. We have no need of any of this undo or redo to recover from an instance failure.