Oracle Database 12c Backup and Recovery Survival Guide
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

LOGGING versus NOLOGGING

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:

  • NOLOGGING is designed to handle bulk inserts of data which can be easily reproduced. (Remember that the UPDATE and DELETE operations will always be logged.)
  • Regardless of the LOGGING status, writing to the UNDO blocks will always cause generation of redo.
  • LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason, Oracle introduced the ALTER DATABASE FORCE LOGGING command to place the database in the FORCE LOGGING mode—meaning that the NOLOGGING attribute will not have any effect on the segments. The FORCE LOGGING mode can also be used at the tablespace level using the ALTER TABLESPACE <Tablespace_Name> FORCE LOGGING command. Use of this option results in some performance degradation, but ensures the recoverability of your primary database and the integrity of your standby database.

    Note

    Using FORCE LOGGING in the initialization parameter file in a Multitenant Container Database will always place all the pluggable databases using that CDB in the FORCE LOGGING mode.

  • Any change to the database dictionary will always cause redo generation. This will happen to protect the data dictionary integrity. For example, if Oracle allocates a space above the high water mark (HWM) for a table, and the system fails in the middle of an INSERT /*+ APPEND */ command, then Oracle will need to rollback the data dictionary change that was made. There will be a redo generated, but it is only to protect the data dictionary, not your newly inserted data (Oracle will undo the space allocation if it fails, and your newly inserted data will also disappear).
  • Objects should be set back to the LOGGING mode when the NOLOGGING mode is no longer required.
  • NOLOGGING is unnecessary for direct path inserts if the database is in the NOARCHIVELOG mode (see the following table).
  • Operations involving data that cannot be easily reproduced should always use LOGGING operations; avoid NOLOGGING in such cases! If data is loaded using NOLOGGING, the data will not be able to be recovered in a situation of media recovery if no backup is made after the load.
  • NOLOGGING does not apply to normal UPDATE, DELETE, and INSERT operations.
  • NOLOGGING will work during specific situations only, but subsequent DML operations over the data will always generate redo (we will see a list of the specific commands that will work in the NOLOGGING mode a little bit later in this chapter).
  • If the LOGGING or NOLOGGING clause is not specified when creating a table, partition, or index, the default to the LOGGING attribute will be the LOGGING attribute of the database, or if not set, the tablespace in which it resides.

Note

When doing the insert mode APPEND, it isn't APPEND really, it is the fact that you are doing a direct path operation that will bypass undo (hence reducing redo) and may bypass redo when in NOLOGGING.

Only a few operations cannot make use of the NOLOGGING mode benefits. They are:

  • Table redefinition cannot be done in NOLOGGING, in other words, it will need to be in the LOGGING mode and will always generate redo.
  • Temp files are always set to the NOLOGGING mode, but any non-direct path operation on them such as INSERT/UPDATE/DELETE will generate redo since they do generate undo.

Note

In Oracle 12c, temporary tables record their undo into temp, removing all redo!

Tip

Always remember to do a backup of your database after a NOLOGGING operation is made.

The database mode FORCE LOGGING (introduced with 9i R2) when set is a persistent attribute for the database (initialization parameter), meaning that the NOLOGGING operations will not have any effect if used. If the database is shut down and restarted, it remains in the same logging mode state (FORCE LOGGING).

Note

FORCE LOGGING only needs to be configured again if a control file is re-created.

If your database has a physical or logical standby database and is not set in the FORCE LOGGING mode, then NOLOGGING operations in the primary database will render data blocks in the standby database to become logically corrupt because of the missing redo log entries. If the standby database ever switches to the primary role, errors will occur when trying to access data in objects that were previously written with the NOLOGGING option. In this case, you will see an error like the following:

ORA-01578: ORACLE data block corrupted (file # 3, block # 2527)
ORA-01110: data file 1: '/u1/oracle/dbs/stdby/tbs_nologging_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option"

This doesn't sound good and I certainly can't imagine a happy DBA when called at 3:00 A.M. if this kind of error message has come up.

Tip

A DBA can then move the datafiles over to the standby, manually making it consistent. People do this all of the time for massive loads or re-orgs to avoid generating, shipping, and applying a ton of redo; it is easier to just move the datafiles.

You can check if your database is using the FORCE LOGGING mode with the following command:

SQL> SELECT force_logging FROM v$database;

FORCE_LOGGING
---------------------------------------
NO

Note

The options UNRECOVERABLE (introduced in Oracle 7) and NOLOGGING (introduced in Oracle 8) can be used to avoid the redo log entries being generated for certain operations that can be easily recovered without using the database recovery mechanism. Do remember that the UNRECOVERABLE option is deprecated and is replaced by the NOLOGGING option.

上一章目录下一章