Logging or Not Logging:That is the Question v1.7

What is a Redo? 

Let’s conduct a brief summary about the redo process. When Oracle blocks are changed, including undo blocks, oracle records the changes in a form of vector changes which are referred to as redo entries or redo records. The changes are written by the server process to the redo log buffer in the SGA. The redo log buffer is then flushed into the online redo logs in near real time fashion by the log writer LGWR.  

The redo logs are written by the LGWR when: 

• When a user issue a commit.
• When the Log Buffer is 1/3 full.
• When the amount of redo entries is 1MB.
• Every three seconds 
• When a database checkpoint takes place. The redo entries are written before the checkpoint to ensure recoverability. 

Redo Generation and Recoverability 

The purpose of redo generation is to ensure recoverability.  This is the reason why, Oracle does not give the DBA a lot of control over redo generation. If the instance crashes, then all the changes within SGA will be lost. Oracle will then use the redo entries in the online redo files to bring the database to a consistent state. When a tablespace is put in backup mode the redo generation behaviour changes. As long as the table space is in backup mode Oracle will write the entire modified block to the redo log. This is done due to the reason Oracle can not guaranty that a block was not copied while it was updating as part of the backup. 

Important points about LOGGING and NOLOGGING 

Despite the importance of the redo entries, Oracle gave users the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode.  NOLOGGING affect the recoverability. Before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, this are some points regarding it: 

• NOLOGGING is designed to handle bulk inserts of data which can be easy re-produced.
• Regardless of LOGGING status, writing to undo blocks causes generation of redo.
•  LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.
• Any change to the database dictionary will cause redo generation. This will happen to protect the data dictionary.  An example: if we allocated a space above the HWM for a table, and the system fail in the middle of one INSERT /*+ APPEND */ , the Oracle will need to rollback that data dictionary update.  There will be redo generated but it is to protect the data dictionary, not your newly inserted data (Oracle will undo the space allocation if it fails, where as your data will disappear).
• The data which are not logged will not be able to recover. The data should be backed up after the modification.
• Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
• NOLOGGING is not needed for Direct Path Insert if the database is in NO ARCHIVE LOG MODE. (See table 1.1) 

Table 1.1 

• The data which is not able to reproduce should not use the NOLOGGING mode. If data which can not be reloaded was loaded using NOLOGGING. The data cannot be recovered when the database crashes before backing the data.
• NOLOGGING does not apply to UPDATE, DELETE, and INSERT.
• NOLOGGING will work during certain situations but subsequent DML will generate redo. Some of these situations are:
  • direct load INSERT (using APPEND hint),
  • CREATE TABLE … AS SELECT,
  • CREATE INDEX.
• If the LOGGING or NOLOGGING clause is not specified when creating a table, partition, or index the default to the LOGGING attribute, will be the LOGGING attribute of the tablespace in which it resides. 

If the database has a physical standby database, then NOLOGGING operations will render data blocks in the standby “logically corrupt” because of the missing redo log entries. If the standby database ever switches to the primary role, errors will occur when trying to access objects that were previously written with the NOLOGGING option, you will an error like this:

ORA-01578: ORACLE data block corrupted (file # 3, block # 2527)

ORA-01110: data file 1: ‘/u1/oracle/dbs/stdby/tbs_nologging_1.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option”

That doesn’t sound good, and certainly I can’t imagine a happy DBA called at 3:00 AM to recover a database and that error message comes up.

**If you want to read the full paper please download it from: Logging or Not Logging, This is the Question. 

Cheers,

Francisco Munoz Alvarez