Oracle Database 12c Backup and Recovery Survival Guide: A comprehensive guide for every DBA to learn recovery and backup solutions

Despite the importance of the redo entries, Oracle gives users the ability to limit redo generation on tables, partitions, tablespaces, and indexes by setting them in the NOLOGGING mode. NOLOGGING affects the recoverability of a database and before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation. The following are some interesting points regarding this topic:

The NOLOGGING attribute tells Oracle that the operation being performed does not need to be recoverable in the event of a failure. In this case, the database will generate only a small set of metadata that is written to the redo log, and the operation will probably run faster. Oracle is relying on the user to recover the data manually in the event of any failure. In other words, the NOLOGGING option skips the generation of redo for the affected object, but will still log many things such as data dictionary changes caused by space management.

This section shows you how to reduce redo generation using the LOGGING and NOLOGGING operations. The discussion is divided into two parts. In one part, you'll learn about things to do when LOGGING is in effect. In the other, you'll learn what to do when NOLOGGING is enabled.

If it is required that the data loaded using the NOLOGGING option needs to be recovered in the case of a disaster situation, your only solution is making a backup as soon as the NOLOGGING operation is made. Otherwise, the data cannot be recovered in the event of a media failure because it has not been logged.

Sometimes you will not be able to run a full backup of the database until later in the day. In this situation, you can make use of the following strategies:

There are a number of wait events that happen during redo activities and most of them are I/O related. First, I will talk about the two most important wait events: 'log file parallel write' and 'log file sync'. Then I will mention some other important ones you should know about.

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered, then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle 8i, an ORA-26040 is also signaled (ORA-26040: Data block was loaded using the NOLOGGING option) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation, you need to understand that:

If this is your situation, please note that:

After a NOLOGGING operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future. However, there are additional steps required if you have an existing physical or logical standby database. Executing these steps is crucial if you want to preserve the data integrity of your standby databases.

For a physical standby database, Data Guard's Redo Apply process will process the invalidation redo and mark the corresponding data blocks as corrupted. Follow these steps to reinstate the relevant datafiles:

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

In this section we will talk about some other important facts about redo that are important for you to know.

Now is the time to see some important scripts that could help your life become a little bit easier when dealing with redo.

                      Redo Log Statistics

Statistic                                       Statistic
Name                                            Value
---------------------------------------------- ----------
redo KB read                                       209048
redo KB read (memory)                                   0
redo KB read (memory) for transport                     0
redo KB read for transport                              0
redo blocks checksummed by FG (exclusive)          205974
redo blocks checksummed by LGWR                         0
redo blocks read for recovery                        8532
redo blocks written                         ...

In this chapter, you learned about the LOGGING and NOLOGGING modes and saw how to use them. You also have seen different techniques for reducing redo generation when using the LOGGING and NOLOGGING modes. Hopefully we've cleared up some misconceptions about NOLOGGING operations as well. The NOLOGGING mode is one of my favorite topics in Oracle, and I hope you have enjoyed this chapter as much I have enjoyed writing it.

In the next chapter, we will talk about what is new in Oracle 12c for backup and recovery. We will see many new features and enhancements that will make our life more easy and our database recoverable.