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 in Oracle?
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.
They are also 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 Onward 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 in Oracle?
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
- Insert the row in the buffer
- generate the undo in the buffer
- Write the redo changes i.e insert rows in redo logs
- 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 buffer 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 data loss 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 .
we have option of no-logging 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.
Related Articles
how to find session generating lots of redo
alter system switch logfile v/s archive log current
recreate redo log files in Oracle database
senior oracle dba interview questions