The Oracle (tm) Users' Co-Operative FAQ

I have created a table with the nologging option, but any insert, update, or delete still generates redo log , why?


Author's name: Mark J. Bobak

Author's Email: mark@bobak.net

Date written: 20th June 2001

Oracle version(s): 8.0, 8i

After creating a table w/ the nologging option, my DML still generates redo log activity.  Why?  I thought the point of nologging was to disable redo generation.

Back to index of questions


First, it must be understood that redo generation is at the very heart of Oracle's ability to recover from virually any media failure.  For that reason, the ability to disable it only exists for a small subset of commands.  They are all related to serial and parallel direct-path loads.

What operations allow NOLOGGING?
DML statements such as insert, update, and delete will always generate redo.  However, the nologging option may be utilized for the following SQL statements (from the Oracle 8i concepts manual, chapter 22):

For the statements listed above, undo and redo logging can be almost entirely disabled.  New extents are marked invalid, and data dictionary changes are still logged.  Note that a table or index with the nologging attribute (which can be seen in the LOGGING column of the DBA_TABLES or DBA_INDEXES view) will default to nologging when one of the above statements is executed.

What is the recoverability of objects created as NOLOGGING?
Since nologging disables writing redo log entries, there is no way for Oracle to recover data related to nologging operations.  In the case of a media failure subsequent to a nologging operation, Oracle will apply the redo log transactions, but, when it reaches the transactions related to the nologging operation, it will only be able to apply the extent invalidation records, since that is all that was recorded.  Any subsequent attempt to access data in those extents will result in an ORA-26040 "Data block was loaded using the NOLOGGING option".  The only resolution to the error is to drop and recreate the object.  Note that this risk only exists until the next successful backup.  Backups taken after the completion of the nologging operation will provide complete recovery.


Further reading:   Using Oracle7 UNRECOVERABLE and Oracle8 NOLOGGING option  (MetaLink Registration required)

This question is also addressed by the following document(s):

Author Title/URL Suggested by Referee's comments
Howard Rogers What does the "nologging" operation do for me ? Jonathan Lewis A short pdf file with an intelligent discussion of the issues. You will need Adobe Acrobat to read this document.

NOTE - The referenced site was closed down by Oracle Australia. in May 2002. This link has been maintained in case Howards gets permission to re-open the site.

       

.


Back to top

Back to index of questions