<

Go Premium for a chance to win a PS4. Enter to Win

x

UNDO AND REDO IN ORACLE

Published on
3,798 Points
798 Views
Last Modified:
This post first appeared at Oracleinaction by Anju Garg (Myself).

I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasons for duplicating the storage of undo.

First, let’s demonstrate that
  • The Oracle redo contains undo
  • Checkpointing causes
    • Dirty blocks to be written to datafiles
    • Buffers containing undo to be written to undo tablespace
Overview:

Steps
  1. Find out name of the current undo tablespace 
  2. Create an undo tablespace undotbs2
  3. Create a tablespace test_undo
  4. Create a table test_undo_tab in tablespace test_undo
  5. Insert two rows with txt – teststring1, teststring2 in test_undo_tab
  6. Perform manual checkpoint
  7. Switch logs so that new log does not contain string teststring1
  8. Find out name of current redo log
  9. Switch undo tablespace to undotbs2
  10. Update the column value from teststring1 to teststring_uncommitted and do not commit
  11. In another session, update the column value from teststring2 to teststring_committed and commit
  12. Check that both new and old (undo) values have been written to current redo log
  13. Check that undo tablespace does not contain pre update values i.e. teststring1 and teststring2 as undo information is still in buffer cache and checkpoint has not taken place yet
  14. Perform manual checkpoint so that dirty buffers as well as buffers containing undo information are flushed to disk
  15. Check that datafile does contain updated values (both committed and uncommitted)
  16. Check that undo tablespace contains pre update values i.e. teststring1 and teststring2
Implementation:

1.  Find out name of the current undo tablespace
sql>sho parameter undo_tablespace
NAME                                  TYPE        VALUE
----------------------------------   ------      -------
undo_tablespace                      string      UNDOTBS1

Open in new window


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

Open in new window


3.  Create a tablespace test_undo
SQL> drop tablespace test_undo including contents and datafiles;

     create tablespace test_undo datafile '/u01/app/oracle/test_undo.dbf' size 128k;

Open in new window


– Create a test table test_undo_tab in the tablespace created above
SQL> drop table test_undo_tab purge;

     create table test_undo_tab(txt char(1000)) tablespace test_undo;

Open in new window


4.  Insert two rows in test_undo_tab and commit –
SQL> insert into test_undo_tab values ('teststring1');

     insert into test_undo_tab values ('teststring2');

     commit;

Open in new window


5.  Perform manual checkpoint so that above changes are written to the datafile
SQL>alter system checkpoint;

Open in new window


6.  Set undotbs2 as current undo_tablespace
SQL>alter system set undo_tablespace = undotbs2;

    sho parameter undo_tablespace

NAME                                 TYPE        VALUE
---------------------------------   -------     ---------
undo_tablespace                      string      UNDOTBS2

Open in new window


7.  Switch logs so that the current log does not contain string teststring
SQL>alter system switch logfile;

Open in new window


8. Find out name of current redo log
 
SQL> col member for a30
     select member, l.status from v$log l, v$logfile f where l.group# = f.group# and l.status = 'CURRENT';

MEMBER                         STATUS
---------------------------    ----------------
/u01/app/oracle/redo04.log     CURRENT

Open in new window


9.  In one session Update one row and do not commit –
SQL>  update test_undo_tab set txt = 'teststring_uncommitted' where txt = 'teststring1';

Open in new window


10. In another session, Update the second row and commit –
SQL>  update test_undo_tab set txt = 'teststring_committed' where txt = 'teststring2';

      commit;

Open in new window


11. Commit and then check that redo log contains both redo and undo for both the DML’s (committed and uncommitted)–
[oracle@node1 oracle]$ strings /u01/app/oracle/redo04.log|grep teststring

teststring_uncommitted                                                                                  
teststring1                                                          

teststring_committed                                                

teststring2

Open in new window



12. Check that datafile does not contain updated values i.e. teststring_committed and teststring_uncommitted as checkpointing has not taken place yet and dirty buffers have not been flushed to disk
[oracle@node1 oracle]$ strings /u01/app/oracle/test_undo.dbf|grep teststring

teststring2                                                                                                                              ,
teststring1

Open in new window


13.  Check that undo tablespace does not contain pre update value i.e. teststring1 and teststring2 as undo information is in buffer cache and checkpointing has not taken place yet
[oracle@node1 oracle]$ strings /u01/app/oracle/undotbs2.dbf|grep teststring

Open in new window


14.  Perform manual checkpoint so that buffers containing undo information are flushed to disk
SQL> alter system checkpoint;

Open in new window


15.  Check that datafile does contain updated values (both committed and uncommitted) i.e. teststring_committed and teststring_uncommitted as checkpointing has taken place yet and dirty buffers have been flushed to disk
[oracle@node1 oracle]$ strings /u01/app/oracle/test_undo.dbf|grep teststring

teststring_committed                                                                                                               ,
teststring_uncommitted

Open in new window


16.  Check that undo tablespace contains pre update values i.e. teststring1 and teststring2
[oracle@node1 oracle]$ strings /u01/app/oracle/undotbs2.dbf| grep teststring

teststring2                                                                  
teststring1

Open in new window



Cleanup

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

Open in new window


Conclusions

  • Log writer writes both undo and redo for every DML in a transaction (committed/uncommitted) irrespective of whether checkpoint has taken place or not.
  • On checkpoint,
    • Undo present in buffers in database buffer cache is written to undo tablespace .
    • Dirty buffers containing committed/uncommitted data are written to datafiles.
It implies that in a running database,
  • - datafiles may contain uncommitted data (buffers dirtied by uncommitted transaction and checkpoint takes place)
  • - committed data may not be there in datafiles (checkpointing not done after commit)
  • - redo/undo for committed changes will always be there in redo logs (Lgwr writes on commit).
  • - redo/undo for Uncommitted changes may be there in redo logs(Lgwr writes every 3 seconds)
In addition to redo logs, undo information
  •     may be present in buffer cache (checkpoint has not taken place after change)
  •     will be written to undo tablespace on checkpoint
  •     will never be overwritten until a transaction is active
Now the question arises why undo information is being stored both in undo tablespace and redo logs.

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

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
  • Checkpoint takes place
  • Uncommitted change is written to datafile
  • I decide to rollback the change
  • If redo log has not been overwritten
    • Search entire redo log for the undo and then rollback
  • Else (redo log has been overwritten)
    • Undo information is not available for rollback.
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 as
  • 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
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 for
  • Read consistency
  • Flashback query
  • Flashback version query
Now, let’s see what will happen if undo is stored in undo buffers and undo tablespace only.

SCENARIO – I
  • I make a change and do not commit
  • The redo for the change is present in redo logs
  • The undo for the change is present in buffer cache
  • The undo for the change is not present in undo tablespace as checkpoint has not taken place
  • The instance crashes
  • Undo information present in buffer cache is wiped out
  • As part of instance recovery, redo is applied and datafiles contain uncommitted data
  • The database cannot be opened as undo information needed to rollback uncommitted change is not available leading to an inconsistent database.
SCENARIO – II
  • I take hot backup of a tablespace.
  • The datafile for the tablespace is lost or corrupted.
  • I take the tablespace offline and restore the datafile from backup.
  • I recover the datafile
  • The redo logs and archivelogs contain redo data for both committed and uncommitted transactions
  • The redo logs and archivelogs do not contain undo data (as per our assumption).
  • As part of recovery, redo for all the committed/uncommitted changes is read from online/archive redo logs and applied
  • The tablespace cannot be brought online as the undo information needed to rollback uncommitted changes is not available
Hence, to resolve above problems, undo is stored in redo logs also. During roll forward phase of instance/media recovery, as changes (redo in redo logs) are applied to datafiles, undo stored in redo logs is used to generate undo segments. These segments are subsequently used to rollback uncommitted changes during rollback phase of instance/media recovery.
 
0
Comment
Author:Anjugarg66
  • 2
2 Comments
 
LVL 1

Author Comment

by:Anjugarg66
I have completed the setup for Google authorship.

Kindly do the needful.

Regards
Anju Garg
0
 
LVL 1

Author Comment

by:Anjugarg66
Hello mlmcc,

    I have made the required change.

Regards
Anju Garg
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month