RelationalDBDesign RelationalDBDesign

Physical Backups  «Prev  Next»
Lesson 11 Implications of the logging and nologging modes
Objective Identify the backup implications of logging and nologging modes.

Implications of the logging and nologging Modes

Logging and nologging are two modes you can set for database objects, such as tables, partitions, and indexes. If the database objects are set to logging mode, which is the default, all the changes made to them are fully recorded in redo logs. If you turn off logging mode for these objects and modify them, only minimal redo is generated.
Tables, indexes, or partitions may be set to nologging mode for loading a large block of data with the direct-load INSERT operation.
When the nologging mode is set for a direct-load, the insert statements are not logged in the redo logs, and thus, the loading process is faster.

Direct-load INSERT with nologging

When you are performing the direct-load INSERT in the nologging mode, data is inserted without generating redo information. Even though some minimal logging is still done, recovery is almost impossible without further back up. In contrast, data dictionary changes are always fully logged.
The nologging mode has a performance advantage because it generates much less redo information. On the other hand, because the redo logs do not contain the information about the inserted values during the period when the table was in nologging mode, the database is not recoverable from the last backup. You are responsible for backing up the data after a nologging INSERT operation in order to be able to perform successful media recovery. Thus, upon the completion of the direct-load operation on a table, you should reset the table to logging mode and immediately backup the datafiles containing the table. Features of logging and nologging options

Logging Nologging
All changes recorded to redo logs Minimal redo information recorded in redo logs
Fully recoverable from last backup Not recoverable from last backup
No additional backup required Requires additional backup

The next lesson discusses backing up "read-only" tablespaces.