Redo and undo for DML

When you issue an insert, update, or delete, Oracle actually makes the change to the data blocks that contain the affected data even though you have not issued a commit. To ensure database integrity, Oracle must write information necessary to reverse the change (undo) into the redo log file to handle any transaction failure or even a rollback. Recovery from media failure is ensured by writing the information necessary to replay all database changes (redo) to the database into the redo log file. So, undo and redo information needs to be written into the transaction log of the RDBMS as a logical consequence to protect the integrity of the data.

While the RDBMS logically would only need to write undo and redo into the transaction log, the undo portion must also be kept online (on disk and accessible to the RDBMS engine) to enable rollback of failed transactions, and as importantly, for read consistency—read consistency is actually the reason we have undo and redo separate. Oracle have undo especially for read consistency and just happen to use it instead of the transaction logs to rollback. If undo data was only stored in the transaction log, the log could get archived and the RDBMS would have to try to read it from the ARCHIVELOG file or tape. On some platforms, the tape could be sitting in the DBA's desk drawer, so there are practical problems with this solution. Every RDBMS must meet the basic requirement of online access to undo data, and Oracle does this by storing the undo data in what we call rollback segments (where rollback means undo).

Because Oracle places the undo data into a rollback segment and also must (logically) place this data into the transaction log, it is simpler to just treat the UNDO tablespace like any other tablespace from a log generation perspective. That is why Oracle generates redo for a rollback segment, which is logically the same as undo for a data block (that is your table, index, and so on).

Oracle's transaction log is really called the redo log because it only contains redo records. There logically must be undo records stored in the log, but they are stored in the form of redo for undo segments.

Before Oracle 12c for temporary tables, Oracle needed to use undo to facilitate a rollback, to save point, and also for read consistency; not only to reclaim a space that was used by the temporary table, but in Oracle 12c all will be kept in the TEMP tablespace itself due that undo for TEMP goes into TEMP now.

Redo and temporary tables

Before Oracle 12c, the number of redo log entries generated by a temporary table was approximately 50% of the redo log entries generated for permanent tables. However, you must consider that an INSERT command requires only a small amount of undo data, whereas a DELETE command requires a large amount of redo data. (INSERT just stores delete+rowid in the undo, UPDATE stores the before image of the modified columns+rowid in undo, and DELETE stores the entire row+rowid in undo.) If you tend to insert data into temporary tables and don't delete the data when you're done, the relative redo log generation rate may be much lower for temporary tables than 50% of the redo log generation rate for permanent tables. Now with Oracle 12c, temporary tables will not generate redo.

Redo generation and materialized views

Setting the NOLOGGING option during the materialized view creation does not affect this fact as the option only applies during the actual creation and not to any subsequent actions on the materialized view.

Enhancement requests have been raised to be able to turn off redo generation during a refresh, but these were rejected as doing this could put the database into an inconsistent state and affect options such as Data Guard, as well as backup and recovery.

The amount of redo generated during a complete refresh can be reduced by setting ATOMIC_REFRESH=FALSE in the DBMS_MVIEW.REFRESH option. The complete refresh will use a TRUNCATE+INSERT /*+APPEND*/ command to refresh, and this can skip all undo and redo.

Flashback and NOLOGGING

When using Flashback Database with a target time at which a NOLOGGING operation was made, a block corruption is likely to be produced in the database objects and datafiles affected by the NOLOGGING operation.

For example, if you perform a direct path insert operation in the NOLOGGING mode and that operation runs from 9:00 A.M. to 9:15 A.M. on July 7, 2013, and later you require to use Flashback Database to return to the target time 09:07 A.M. on that date, the objects and datafiles modified by the direct path insert may leave the database with block corruption after the Flashback Database operation completes.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, always perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation is done to ensure recoverability to a given point-in-time. If you expect to use Flashback Database to return to a point-in-time during an operation such as a direct path insert, consider to perform the operation in the LOGGING mode to ensure the recoverability of it.

Performance and recovery considerations

The NOLOGGING mode improves performance during direct path operations because it generates much less log data in the redo log files, helping eliminate the time necessary to execute the redo generation (latch acquisition, redo log writing, and so on). The user is responsible for backing up the data after a NOLOGGING insert operation in order to be able to perform media recovery.

Direct path load using SQL*Loader

To use direct path load in SQL*Loader, you must run the $ORACLE_HOME/rdbms/admin/catldr.sql script before your first load is run in the direct path mode. To run sqlldr in the direct path mode, specify the option direct=true.

To save time and space in the redo log file, use the SQL*Loader UNRECOVERABLE clause in the SQL*Loader control file when you load the data. An unrecoverable load does not record loaded data in the redo log file, instead it generates invalidation redo.

The UNRECOVERABLE clause applies to all objects loaded during the load session (both data and index segments). Therefore, media recovery is disabled for the loaded table, although database changes by other users may continue to be logged.

If media recovery becomes necessary on the data that was loaded with the UNRECOVERABLE clause, then the data blocks that were loaded are marked as logically corrupted. To recover the data, you will need to drop and re-create the data. It is a good idea to do backups immediately after a load as this is done to preserve the otherwise unrecoverable data.

The following is an example of specifying the UNRECOVERABLE clause in the control file:

UNRECOVERABLE
LOAD DATA
INFILE 'example.dat'
INTO TABLE test
(name VARCHAR2(10), number NU
MBER(4));

If a data or index segment has the NOLOGGING mode set, then full image redo logging is disabled for that segment (and of course, invalidation of redo is generated). The use of the NOLOGGING parameter allows a finer degree of control over the objects that will not generate LOGGING.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load insert operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo. In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables.