Control File Updates Can Be Disabled During NOLOGGING Operations

Parameter DB_UNRECOVERABLE_SCN_TRACKING = [ TRUE | FALSE ] can be used to turn off control file writes to update fields that track the highest unrecoverable SCN and Time during a NOLOGGING direct path operation.

Performance of the NOLOGGING load operation could be limited by the control file write I/O.

When the value is set to true, updates are made to the controlfile that maintains the V$DATAFILE.UNRECOVERABLE_CHANGE# and V$DATAFILE.UNRECOVERABLE_TIME columns. When the value is set to false, updates are not made to the controlfile. Setting this parameter to false may improve performance of direct-path NOLOGGING operations.

The DB_UNRECOVERABLE_SCN_TRACKING initialization parameter is available starting with Oracle Database 11g Release 2 (


Direct-Path INSERT without Logging

In this mode, Oracle Database inserts data without redo or undo logging. Instead, the database logs a small number of block range invalidation redo records and periodically updates the controlfile with information about the most recent direct write.

Direct-path insert without logging improves performance. However, if you subsequently must perform media recovery, the invalidation redo records mark a range of blocks as logically corrupt, because no redo data was logged for them. Therefore, it is important that you back up the data after such an insert operation.

Beginning with release of Oracle Database, you can significantly improve the performance of unrecoverable direct path inserts by disabling the periodic update of the controlfiles. You do so by setting the initialization parameter DB_UNRECOVERABLE_SCN_TRACKING to FALSE. However, if you perform an unrecoverable direct path insert with these controlfile updates disabled, you will no longer be able to accurately query the database to determine if any datafiles are currently unrecoverable.






Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s