Tuesday, October 09, 2007

LOGGING/NOLOGGING

One of the common misconception is if you set NOLOGGING on a table or index, then no future DML operations (insert, update, delete etc) on this object will be recorded in logfiles.

Actually the real meaning of NOLOGGING is whatever operations are performed on the object with the NOLOGGING option, will NOT be recorded in logfiles.

However, not all operations support NOLOGGING mode, the following is a list of operations that support NOLOGGING:

direct load (SQL*Loader)
direct-load
INSERT CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line


It also recommended to do a backup of subject object after NOLOGGING operation. When you do media recovery of the object using backup copy before NOLOGGING operation, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. The similar situation apply to Data Guard setup. That's also one of reason why Data Guard setup require you to set force logging.

Howard has been talking about the use of _disable_logging parameter to temporary disable redo logging while bulking loading. However this parameter should be used with extreme caution.

Howard's post about _disable_logging

No comments: