Difference between undo segment and redo logs

A point of confusion for a DBA would be the distinction between undo and redo.

–> Basically undo tablespace stores 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 and even UNDO provides the read consistency to a user as he can only see the previous values until a transaction is committed, for example a user 1 may run select statement on a table being modified by user 2, while user 1 can only see the unchanged values until user 2 commits his DML statement.

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.

–> REDO logs are used in recovery. Every uncommitted or committed change made to the data is stored in redo logs. The log writer (LGWR) process writes these changes from ‘redo log buffer’ to redo log files in disk. The main advantage of redo logs is that we can prevent data loss, for example if we dropped a table by mistake then we can recover it from the redo log files, or even we can recover a data file in case of losing it.

In this way UNDO and REDO guarantees the ACID(Atomicity,Consistency,Isolation,Durability) properties in RDBMS.

Leave a Reply

Your email address will not be published. Required fields are marked *