Skip to main content

NOLOGGING Operations Frequently Asked Questions (KBA1339)



Q: What is a NOLOGGING operation?

When performing a write operation (making changes to a database) Oracle first writes those changes to a redo log. While the datafile writes are occurring Oracle writes the redo vectors to the redo log, recording the changes made to the data blocks. Doing this means that, even if corruption is encountered in the datafile, the database can be recovered with the last backup and the redo actions stored in the redo logs.  Archive logs are copies of the 'full' redo logs that preserve the change history recorded in the online redo.  Usually it's the archive logs that are used for recovery.

A NOLOGGING operation is when a user or process making changes to an Oracle database asks that their changes not be logged in Oracle's redo logs. There are valid reasons why this might be desirable which is why Oracle allows the behavior. For example, one reason is to speed up operations during a large bulk data load.  A table can be declared as NOLOGGING, and even though only certain operations obey that directive it's still not a recommended setting for tables in a production database.

Q: Why does Delphix care about redo / archive logs?

The Delphix Engine takes and stores Snapshots of source databases and VDBs. A Snapshot is nothing more than an incremental backup of a source database. Since Delphix does not require the database to be shutdown or quiesced during the backup, changes may occur while the backup is happening. In order to allow the snapshot to be restorable, Delphix needs to track any and all changes that were happening while the snapshot was being taken. The archive logs are used for this purpose. Also, if LogSync is being used, that is simply keeping copies of all archive logs so that Delphix can provision to points in time between Snapshots.

In order to provision a database, the Delphix Engine will choose a snapshot of the source database and restore it as from a backup. Since there could have been changes happening during the backup process the Snapshot itself may not contain a consistent set of data. Delphix will then apply any redo from archive logs stored during the Snapshot process to bring the restored database to a consistent state.

Q: Why does Delphix care about NOLOGGING operations?

Because the redo operations from archive logs are used to bring the database to a consistent state as part of the provisioning process, if there have been NOLOGGING operations while the Snapshot was happening, there is no longer a record of all data that was changed during the snapshot process. It is no longer possible to bring the database to a consistent state. A VDB provisioned from a snapshot with NOLOGGING operations could suffer from many possible issues:

  • The provision may fail with errors
  • The provision may succeed, but using the database will result in errors being thrown or the database crashing
  • The database may be fully usable, but missing data

Q: What should I do if NOLOGGING operations are occurring?

  • Always limit the amount of NOLOGGING operations that occur on your databases as they pose a risk to the ability to restore a database in the event of data corruption and they should be treated just so, as a risk.
  • While using a database as a part of Delphix, make sure that anyone who will be doing NOLOGGING operations is aware that such operations can prevent data changes from being recorded in the redo logs making those operations unrecoverable and snapshots taken while NOLOGGING operations are performed are unusable to provision or recover a VDB.
  • If LogSync is being used NOLOGGING operations won't write the necessary redo to the logs, making recovery to a point in time between snapshots not possible.
  • Take a snapshot as soon as possible after the NOLOGGING operations complete. This will at least provide a clean backup after the problem has occurred to minimize risk and provide a clean snapshot for provisioning should you need to do so.

Q: I'm getting an Oracle datablock corruption issue in a recently created VDB from the logs I see: ORA-01578: ORACLE data block corrupted (file # 16, block # 55549) ; ORA-26040: Data block was loaded using the NOLOGGING option 

A: The snapshot used to provision the VDB had NOLOGGING operations performed on it. Do you have the "Diagnose NOLOGGING faults" option checked for the dSource? If so, it would have thrown an alert for the snapshot. If you do not have that option checked, it would make sense to check it since the nature of the NOLOGGING operations is they can impact both tables and indexes.

In regards to the VDB in question, once you confirm that the dSource had nologging operations, it would be best to refresh it from a snapshot that does not have NOLOGGING operations occurring during the capture of the snapsync in the dSource.

Q:  Will enabling or disabling BCT (Block Change Tracking) affect NOLOGGING?

A: Nologging operations are not affected by disabling and enabling BCT. Nologging operations are typically associated with bulk loads of data into tables or rebuilds of indexes where a developer has added the nologging option.  When you provision a VDB from a snapshot where nologging has occurred you may have issues with the VDB either in recovery performed during provisioning or once users start to use it, hence our warning.