Chapter 2. NOLOGGING Operations

You could be asking yourself why I want to talk about NOLOGGING operations in a backup and recovery book. The answer is simple: NOLOGGING operations will affect the recoverability of a database and due to this, it is very important for a DBA to understand this concept. It is the most visited topic on my blog www.oraclenz.org.

Many DBAs and developers use NOLOGGING operations when doing bulk inserts and massive deletion of data to reduce redo generation but many do not know that these statements will always generate redo, in other words, UPDATE or DELETE will always be logged. Later in this chapter, we will see some techniques that will reduce redo generation for massive updates and deletes.

In this chapter, we will cover the following topics:

NOLOGGING operations do not generate redo records in the redo log files (only a notification that a NOLOGGING operation was made is registered, but not the changes). Consequently, such operations are a very helpful option to reduce the amount of redo to be generated in a transaction, which might make the transaction run faster and also reduce any unnecessary stress on the database.

You need to understand that NOLOGGING operations are direct path—they bypass the buffer cache. If you direct path load say 100 MB of data and that all fits in the buffer cache—a conventional path load might be much faster than a non-logged direct path load (you don't want the blocks to be written to disk and the redo streamed to disk in the background by the LGWR process).

On the other hand, if you are loading gigabytes of data, more than what can be buffered in the cache, then you might benefit from direct path writes since you'd be waiting for the DBWR process to empty the cache.

However, a problem arises due to misconceptions in the use of these operations. In particular, many people forget that NOLOGGING operations will affect the recoverability of the database.

Also it's incredible how many questions I receive regarding this topic when speaking at conferences or when connected in forums and technical chats. Some of the main questions I hear all the time are:

All these questions and many more will be answered as this chapter develops.

You need to remember that redo generation is a crucial part of the Oracle recovery mechanism. NOLOGGING operations only affect the recovery from a media failure perspective (due that you then will need to recover from a backup and apply all the available archive logs in the recover process), but will not affect a database in case of an instance failure. On the other hand, excessive generation of redo is the result of an excessive workload of update, insert, and DML operations in the database.