UNDO AND REDO IN ORACLE

In this post, 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

– oracle redo contains undo
– Checkpointing causes
. dirty blocks to be written to datafiles
. buffers containing undo to be written to undo tablespace

Overview:

– Create a tablespace test_undo
– Create a table test_undo_tab in tablespace test_undo
– Insert two rows with txt – teststring1, teststring2 in test_undo_tab and perform manual checkpoint
– Switch logs so that new log does not contain string teststring1
– Switch undo tablespace to undotbs2
– Update the column value from teststring1 to teststring_uncommitted and do not commit
– In another session, update the column value from teststring2 to
teststring_committed and commit
– check that both new and old (undo) values have been written to current redo log
– 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
– Perform manual checkpoint so that buffers containing undo information are flushed to disk
– check that undo tablespace contains pre update values i.e. teststring1 and teststring2

Implementation:

– Find out name of the current undo tablespace –

sql>sho parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- 

------------------------------
undo_tablespace                      string      UNDOTBS1

– create an undo tablespace undotbs2

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/undotbs2.dbf' 

size 100m;

– 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;

– 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;

– 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;

– Perform manual checkpoint so that above changes are written to the datafile

SQL>alter system checkpoint;

- Set undotbs2 as current undo_tablespace

SQL>alter system set undo_tablespace = undotbs2;
    sho parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- 

------------------------------
undo_tablespace                      string      UNDOTBS2

– Switch logs so that the current log does not contain string teststring

SQL>alter system switch logfile;

– 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

– In one session Update one row and do not commit –

SQL>  update test_undo_tab set txt = 'teststring_uncommitted'
      where txt = 'teststring1';

– In another session, Update the second row and commit –

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

– 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

– 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

– 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

– Perform manual checkpoint so that buffers containing undo information are flushed to disk

SQL> alter system checkpoint;

– check that datafile does contains updated values (both committed and uncommitted) i.e. teststring_committed and teststring_uncommitted as checkpointing has taken place and dirty buffers have been flushed to disk

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

teststring_committed                                                                                                               ,
teststring_uncommitted

– 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

– Cleanup –

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

Thus, it can be concluded that :

– 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.

References:

Expert Oracle Database Architecture by Thomas Kyte

http://oraclenz.wordpress.com/2008/06/22/differences-between-undo-and-redo/
https://forums.oracle.com/forums/thread.jspa?threadID=1016183&start=0&tstart=0
https://forums.oracle.com/forums/thread.jspa?threadID=672861

https://jonathanlewis.wordpress.com/2015/04/20/manuals-2/
Does Select Statement Generate Redo?
Hot Backup Mode Causes Excessive Redo
https://www.dbametrix.com/download/Redo-Log-In-depth.pdf
Delayed Block Cleanout In Oracle

—————————————————————————————-

Related links:

Home

Database Index

Consistent Reads In Oracle : Part-I
Consistent Reads In Oracle : Part-II
Flush Buffer Cache
Oracle Checkpoints
Uncommitted Data In Datafiles

 

—————————————————————————————-

 

27 thoughts on “UNDO AND REDO IN ORACLE

  1. Redo and undo concepts clarified very well. Nice job. Now do not have to refer multiple sources for clarity.

  2. I was looking explanation for long time and your post is exactly what I need,precise and succinct.I still have a small doubt.

    I was under impression that once a data change is committed either after checkpoint or flush buffer_cache the corresponding undo entry is removed from undo tablespace?

    example

    sql>alter table sample set name=’testing’
    where name=’first';
    1 row updated;
    sql>alter system flush buffer_cache;

    In linux host(thre is only one default undo tablespace)

    $ strings undo* | grep first
    $ first

    sql>commit;;
    sql>alter system flush buffer_cache;
    sql>alter system checkpoint:(I know this step is not necessary)

    In linux
    $ strings undo* | grep first
    $ first

    Here I was expecting no output i,e “first” is cleared since it was committed(i read otn forums according to their explanation undo table space contains only undo blocks and cleared after a transaction is committed)

    1. Hi Sam,

      Once a txn has been committed, undo is now called expired undo. Expired Undo info will be retained in undo tablespace till space is available. It is available to be overwritten in case space is needed by some new transactions.

      Hope it helps.

      Regards
      Anju Garg

  3. When we put standby DB in maximum performance mode archives are transferred from primary DB to standby DB.
    suppose user A connects to primary DB and does some tx and commit, again he does some tx but this time he gets disconnected
    so PMON will perform clean up at primary DB using undo(primary). but this info will be in arch as arch include commited as well as noncommitted tx(transaction)
    and the same arch is applied to standby DB. doesn’t it corrupt our Standby DB??????
    Why this does not happen?? how standby manage to be consistent to primary using primay DB arch files???
    Since stand by does not have undo of primary how does it revert this tx(uncommitted, which pmon cleaned up)

    ——————————————————————————————————————
    I think there is one possibility…..when PMON performed clean up at primary site it’s tx must have been written into log file…so when we apply arch that tx cancell the previous one(that i think was making Stdby inconsistent)…It seems more conducive..

  4. What happens if like from your example the things go this way:

    I first execute:

    update test_undo_tab set txt = ‘teststring_committed’
    where txt = ‘teststring2′;
    commit;

    LGWR wrote on commit and we have the redo entry in redo log.

    After this we enter the:

    update test_undo_tab set txt = ‘teststring_uncommitted’ where txt = ‘teststring1′;

    For sure there is undo info in buffer cache as well as dirty buffer .

    Let’s say the instance crashes just before the LGWR would write to redo log as from 3s programming .

    We do not have undo either in tablespace either in redo log.

    Is this undo ignored as in fact it isn’t present anywhere … I mean it would not impact the recovery as it was an operation just in memory?

  5. Hello mam,

    I have one question, How Archive log stores data different from redo log.
    lets say one trx runs for 2 hrs and generates some redo, when we apply that archive log while recovering how long that trx will take to recover?
    could u plz explain this concept in detail?

    Thanks

    1. Hello Rajat,

      Archivelogs are just offline copies of redo logs. They store data in the same manner as redo logs. Archive logs are applied during:
      – Instance recovery: Time taken to apply archive logs during instance recovery depends on the number of concurrent recovery processes you set using parameter RECOVERY_PARALLELISM.
      – Media recovery: Time taken to apply archive logs during media recovery depends on the number of processes used during recovery which by default equals the number of CPUs on the system. (pls refer to http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osrecvry.htm#BRADV99983)

      Hope it helps
      regards
      Anju

  6. Hi mam,

    Which process writes the undo information to undo tablespace,whether its a dbwr writer or server process

  7. Hi Dear Anju Garg,
    Your explanations are cool , but bit of confusion persists.
    Can you please explain me the same concept by using numbers .
    Say, we take 100 as a data in the column to be updated by adding 20 to it. So the result is 120.
    1)Server process writes the data (100) from data file to DB buffer cache. At the same time does server process writes data(100) into Redo log buffer ? If no, when adding 20 to the data(100), available in DB buffer cache, a) who writes from DB buffer to redo log buffer? b)what are the contents in redo log buffer at this stage? is it 120 only ? or is it 100,+20 ? or is it 100,+20,120 ?
    2) With your demonstration above “– check that datafile does contains 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 “…. Why doesnt it show ‘teststring1′ in the result. since this is not committed.

    Thanks and regards

    1. Hi Mohamed,

      Thanks for your time and feedback.
      1) Server process reads the data (100) from data file and writes into DB buffer cache.
      Before dirtying the contents of the buffer, server process writes original contents of buffer (100) into undo buffers in buffer cache and redo log buffer
      (https://docs.oracle.com/cd/B28359_01/server.111/b28318/process.htm#i7241)
      Server process writes corresponding change vector to redo log buffer
      Contents of the buffer in buffer cache are dirtied i.e. changed to 120

      2) Data file can contain both committed and uncommitted data. When checkpoint occurs, all the dirty buffers are written to data files whether corresponding changes are committed or not.

      Hope it helps

      Regards
      Anju Garg

      1. Dear Anju Garg
        Thanks for your concern, and much is the patience to reply for a topic posted , dated July 8, 2013.
        …1)And so now at this stage the redo log buffer contains 100(undo) and the change vector. What is the change vector ? is it +20 or the DML command for to add 20…?

        Thanks and regards
        Mohamed.

Your comments and suggestions are welcome!