About the new parameter DB_UNRECOVERABLE_SCN_TRACKING and unrecoverablefault

About the new parameter DB_UNRECOVERABLE_SCN_TRACKING and unrecoverablefault

DB_UNRECOVERABLE_SCN_TRACKING is available in 11.2.0.2. See the official documentation for the following information:

Default value	true
Modifiable	ALTER SESSION,ALTER SYSTEM
Range of values	true | false
Basic	No
Oracle RAC	Multiple instances must have the same value

DB_UNRECOVERABLE_SCN_TRACKINGEnables or disables the tracking of unrecoverable (NOLOGGING) Direct-path insert and load operations.

When the value is settrue, Updates are made to the controlfile that maintainsV$DATAFILE.UNRECOVERABLE_CHANGE#AndV$DATAFILE.UNRECOVERABLE_TIMEColumns. When the value is setfalse, Updates are not made to the controlfile. Setting this parameterfalseMay improve performance of direct-pathNOLOGGINGOperations.

---------------- The default value of this parameter is True. If it is set to false, update operations with nologging cannot be updated to the control file. Performance of NOLOGGINGLoad operation cocould be limited by the control file write I/O. If it is set to flase, it will improve the performance of direct path nologging.

Multiple instances must have the same value. You can check the following columns of v $ datafile:

UNRECOVERABLE_CHANGE #
UNRECOVERABLE_TIME
FIRST_NONLOGGED_SCN
FIRST_NONLOGGED_TIME

This parameter has a BUG in 11.2.0.2. The official BUG is as follows:

Bug 12360160-alter system/SESSION of DB_UNRECOVERABLE_SCN_TRACKING has no effect (Document ID 12360160.8)

Dynamically changing DB_UNRECOVERABLE_SCN_TRACKING

ALTER SESSION or ALTER SYSTEM has no effect on the value used. Rediscovery Notes: The DB_UNRECOVERABLE_SCN_TRACKING init.ora parameter can be modified dynamically but the change has no effect; the code uses the value from instance startup, and this startup value determines whether the unrecoverable scn/timestamp is recorded in the controlfile (datafile section) for nologging writes. Workaround If DB_UNRECOVERABLE_SCN_TRACKING is set to TRUE at startup, then event 10359 level 1 can be used to dynamically disable (then later dynamically enable) unrecoverable scn/timestamp tracking in the controlfile:  alter system set events '10359 trace name context forever, level 1';  alter system set events '10359 trace name context off'; But, if DB_UNRECOVERABLE_SCN_TRACKING is set to FALSE at startup, there is no workaround to dynamically over-ride this setting ie the unrecoverable scn/timestamp will not be tracked.

This BUG has been fixed on 11.2.0.3.