Oracle: Understanding REDO

In oracle, REDO concepts are not same as UNDO functionality. Like Microsoft word we cannot just REDO any operation after UNDOing. The scope of REDO is limited to the recovery purpose and syncing standby databases. REDO logs don’t hold any data, means REDO logs are actually statements fired by users as part of DML operations. These statements are mapped with SCN(System Change Number). SCN is unique number helps oracle to identify specific REDO operation. In Oracle, REDO logs are initially stored in the Memory (Redo log buffer part of SGA) and whenever user issues commit, Oracle flush all entries to the disk since last SCN change. The activity also takes place every 3rd second and when REDO log buffer is 1/3rd full. This is achieved by Log Writer process (LGWR) runs in the background.

The database must have more than 1 REDO log file and it should be multiplexed. If we do not have more than one file then Oracle will start overwriting same log file again and again. In the case of recovery then we will have only recent data available, therefore we must have more than one REDO log file. Multiplexing means creating more than 1 copy of REDO log files. So if one file set gets corrupted, then another can be used as backup options. This needs to happen parallel for Synchronization otherwise, both log files will have the different set of data which will not be having any use for recovery purpose. Apart from this, we must archive REDO logs files as well for higher recovery options. To achieve this database needs to be open in ARCHIVELOG mode.

How REDO logs are used then? During database recovery process, we can only recover database till the time when the last backup was taken. After this REDO logs are used to REDO all transactions sequentially till crash point. In the case of incomplete Recovery scenario, as well same technique followed to recover data. DBA uses SCN numbers to identify safe point for the recovery process.

REDO logs are also used to bring Standby(Disaster Recovery Site) databases in sync with the production database. There are various sub-categories in this as to how we can transfer data to the Standby sites depends upon how frequently active database is getting updated and impact of data non-availability on the business.

In summary, REDO logs are one of the core aspects of the recovery process and no functionality directly available to the end user usage like UNDO such as rollback or flashback etc.