What is Undo and redo in Oracle database

undo oracle

Many people often ask me the questions

What is Undo and redo in Oracle database?  What is it uses?

I am trying to clear all the doubt about it here in the post

What is Undo?

Undo is stored in Undo tablespace in the database.It stored the before values of changed data blocks whenever we issue a insert, update or delete statement (DML operation) . When we issue any DML statement the changed blocks are stored in buffer cache and the before values for those changed blocks in UNDO segments. Whenever we issue a ROLLBACK command it uses the undo segment to rollback to previous value .

It is just like same as Undo in Notepad or Microsoft word

The undo for a delete is more for than that for an insert.That is because the undo for a delete  is actually the re-Insert of the whole row. So, it has preserve values for each of the columns of the row to be able to “Undo” the delete.
Conversely, the undo for an insert is a Delete of the row by ROWID – which does not have to preserve the values being deleted.

Undo also provides us  the read consistency to a user as he can only see the previous values until a transaction is committed.To explain, I would give one example

User A start the select cursor on the table at time t .The cursor will run for some time

User B start updating the same table at time t’.

Now  user A should get the read consistent version of the table at time t.  But the blocks are getting updated  by User B after some time.

So Oracle uses Undo segments to restore the before image of  update for those block from Undo segments to get the read consistent views

Undo are not destroyed immediately after the commit or rollback as they will be used in Read consistent mechanism.

undo oracle


They are used in flashback queries to see the old committed data using undo restore.

How long a undo data stored in the database?
Oracle provides flexibility of how long should undo data be stored with the help of undo_retention parameter. We can set undo_management parameter to automatic for oracle to manage undo retention(default), or even set this value manually and it’s value should be greater than the time taken by the longest running query in your database.In 11g Onwards Oracle tuned this parameter automatically according to the size of the undo tablespace

What is Transaction ?
A transaction is collection of SQL data manipulation language (DML) statements treated as a logical unit.
The Failure of any statement results in the transaction being “undone”.If all statements process, SQLPlus or the programming application will issue a COMMIT to make database changes permanent.Transactions implicitly commit if a user disconnects from Oracle normally.
Abnormal disconnections result in transaction rollback.The command ROLLBACK is used to cancel (not commit) a transaction that is in progress.

What is redo?

Redo stands for recording the works what has been done,So that work is not lost .REDO logs are used in recovery.  Whenever we do insert in  the database,it does following things

  1. Insert the row in the buffer
  2. generate the undo in the buffer
  3. Write the redo changes i.e insert rows in redo logs
  4. Write the redo changes for undo segments in the redo logs


So every DML operation done in the database is recorded in the redo logs for recovery purpose

Redo are first written in redo log buffere in SGA.The log writer (LGWR) process writes these changes from ‘redo log buffer’ to redo log files in disk

Redo logs help in restore any dataloss due to human mistake,OS fault ,hard disk failure.

Redo logs are critical  for the operation of the database as it provide fault tolerance.

Before writing the changes to the tables  in the datafile,Oracle first write the redo buffer to the redo logs file .


redo oracle

we have option of nologging available for certain operation  not to generate redo buffers.

How Redo Logs Work
The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under
the following situations:
• When a transaction commits
• When the redo log buffer becomes one-third full
• When there is more than a megabyte of changed records in the redo log buffer
• Before the DBWn writes modified blocks in the database buffer cache to the data files
Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log sequence number that is overwritten each time the log is reused.LGWR writes to the online redo log files sequentially. When the current online redo log group is filled, LGWR begins writing to the next group. This is called a log switch.When the last available online redo log file is filled, LGWR returns to the first online redo
log group and starts writing again.