Differences between UNDO and REDO in Oracle

1. To better understand the differences between the two, see the following table:
UNDO REDO

Record	How to undo a change	How to reproduce a change
Used	Rollback, Read-Consistency	Rolling forward DB Changes
Stored in	Undo segments	Redo log files
Protect Against	Inconsistent reads in multiuser systems	Data loss

In simple terms, UNDO records how to cancel transactions and ensure read consistency. REDO records database rollback (REDO) to prevent data loss.
The following describes the relationship between undo and redo through an instance: 1. We will prove the following facts:-Redo in oracle contains undo;-checkpoint will cause dirty data to be written to datafile;-buffers will be written to the current undo tablespace. 2 operation steps:-create one undo tablespace: undotbs2-create a tablespace: test_undo-create a table in the tablespace test_undo: test_undo_tab (txt char (1000)-insert two records to the table test_undo_tab: txt-teststring1, teststring2, execute the manual checkpoint operation-manual log switching, undo tablespace switching-update teststring1 to teststring_uncommitted, and do not submit-new session update teststring2 to teststring_uncommitted and submit-Check Before and After update values are recorded in the current redo log-check that the undo tablespace does not contain the value before the update-perform manual checkpoint, in this way, the undo information will be written to the disk-check that the undo tablespace contains the value before Update 3. Specific implementation: -Query the current undo tablespace.

SQL> show parameter undo_tablespaceNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_tablespace                      string      UNDOTBS1

-Create the Undo tablespace undotbs2:

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/undotbs2.dbf'  2  size 100m;Tablespace created.

-Create a tablespace test_undo

SQL> create tablespace test_undo datafile '/u01/app/oracle/test_undo.dbf '2 size 128 k; Tablespace created.-Create the test table test_undo_tab:

SQL> create table test_undo_tab(txt char(1000)) tablespace test_undo;Table created.SQL> insert into test_undo_tab values ('teststring1');1 row created.SQL> insert into test_undo_tab values ('teststring2');1 row created.SQL> commit;

-Execute a manual checkpoint to write the preceding changes to the data file:

SQL> alter system checkpoint;System altered.

-Set undotbs2 to the current undo tablespace:

SQL> alter system set undo_tablespace=undotbs2;System altered.SQL> show parameter undo_tablespace;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_tablespace                      string      UNDOTBS2

-Switch the log so that the current log does not contain the string teststring

SQL> alter system switch logfile; System altered.-Search for the current log

SQL> col member for a30SQL> select member, l. status from v $ log l, v $ logfile f 2 where l. group # = f. group #3 and l. status = 'current'; member status ------------------------------ ----------------/u01/app/oracle/oradata/orcl/r CURRENTedo02.log-Update a row in the test table and do not submit

SQL> update test_undo_tab set txt = 'teststring _ uncommitted' 2 where txt = 'teststring1'; 1 row updated.-Create a new session to update another row and submit it.

SQL>  update test_undo_tab set txt = 'teststring_committed'      where txt = 'teststring2';      commit;

-Check that the redo log at this time should contain redo and undo (submitted and unsubmitted Data Information)

[oracle@dylan ~]$  strings /u01/app/oracle/oradata/orcl/redo02.log | grep teststring

teststring_uncommitted                                                                                  teststring1                                                          teststring_committed                                                 teststring2

-Check that the current data file does not contain the updated value (only the pre-update data) because the check point has not been triggered.

[oracle@dylan ~]$ strings /u01/app/oracle/test_undo.dbf | grep teststring

Teststring2 teststring1-The checkpoint is triggered at this time.

SQL> alter system checkpoint;

-Check the data file again and find that the data is the latest value (submitted and unsubmitted values)

[Oracle @ dylan ~ $ Strings/u01/app/oracle/test_undo.dbf | grep teststringteststring_committed, teststring_uncommitted-Check the Undotbs2 tablespace and find that it contains the value before update.

[oracle@dylan ~]$ strings /u01/app/oracle/undotbs2.dbf | grep teststringteststring2                                                                  teststring1

-Clear created objects

SQL>drop tablespace test_undo including contents and datafiles;    alter system set undo_tablespace=undotbs1;    drop tablespace undotbs2 including contents and datafiles;

III. Further discussion:

Let's see what will happen if undo is stored in redo logs only.

What if I only store undo information in redo logs?

A redo log can be reused once changes protected by it have been written to datafiles (and archivelogs if database is in archivelog mode ).

It implies that if I make a change and do not commit it
-Change is written to a redo log. If the changed data is not submitted, the Change will be recorded in the redo log.
-Checkpoint takes place checkpoint
-Uncommitted change is written to datafile. uncommitted data is written into the data file.
-I decide to rollback the change. At this time, I plan to roll back.
-If redo log has not been overwritten If redo log is not overwritten
. Search entire redo log for the undo and then rollback, then search the entire redo log for rollback.
Else (redo log has been overwritten)
. Undo information is not available for rollback. Otherwise, the undo information cannot be rolled back!

One might argue that if somehow a redo log is not allowed to be overwritten until it contains active undo, we might be able to manage with undo stored in redo logs only. this solution is not feasible
-Size of redo logs will grow enormously large very soon as thet contain both undo and redo (a user might decide not to end a transaction for months)
-To rollback a change, enormous amount of data in redo logs (both redo and undo) will have to be searched leading to degraded performance
-There will be contention on redo logs as they are being used for both
. Writing redo and undo
. Reading to rollback a change

Some people may argue that redo logs are not allowed to overwrite undo information until they contain new undo logs. In this way, redo logs become abnormal and affect system performance!

Hence, undo information has to be stored separately from redo and is used for rolling back uncommited transactions. The undo stored in undo buffers/undo tablespace is additionally used
-Read consistency: read consistency
-Flashback query
-Flashback version query: flashback version query

Reference: http://oracleinaction.com/undo-and-redo-in-oracle/ http://oraclenz.wordpress.com/2008/06/22/differences-between-undo-and-redo/

--------------------------------------- Dylan Presents.