In an Oracle-l thread, this question was raised: how can you find objects creating nologging changes?

First, what is a ‘nologging’ change?

The redo logging mechanism plays critical role in media recovery. Media recovery relies on archivelog files generated to roll the database forward. A standby database or dataguard recovery also relies on archivelog files. It is possible to do DML changes with minimal logging, i.e., nologging changes, also known as direct mode inserts. For example, insert /*+ append */ can be used to populate the rows into a table without generating much redo. This can invalidate the standby database and might trigger rebuilding some or all parts of the standby database.

nologging changes generates minimal redo, since the blocks are pre-formatted and written to disk directly. A redo record is generated, invalidating a range of affected blocks. This invalidation redo record size is far smaller, for e.g. hundreds of blocks can be invalidated using just a single redo record. Of course, recovery is severely affected as the changes performed with nologging operations cannot be reapplied/recovered.

Internals of nologging changes

Since nologging is all about redo records, dumping the redo log file or archivelog file is a concrete way to see what happens under the hood. Let’s consider an example to explain the internals of nologging changes. We will create a table, insert rows, and closely review the redo records.

(more…)