You are hereLOGGING or NOLOGGING, that is the question - Part 2

LOGGING or NOLOGGING, that is the question - Part 2

By fmunoz - Posted on 15 July 2008

Redo Generation and Recoverability

The purpose of redo generation is to ensure recoverability. This is the reason why, Oracle does not give the DBA a lot of control over redo generation. If the instance crashes, then all the changes within SGA will be lost. Oracle will then use the redo entries in the online redo files to bring the database to a consistent state. The cost of maintaining the redolog records is an expensive operation involving latch management operations (CPU) and frequent write access to the redolog files (I/O). You can avoid redo logging for certain operations using the NOLOGGING feature.

Regarding redo generation, I saw all the times two questions in the OTN Forums:

#1-Why I have excessive Redo Generation during an Online Backup?
When a tablespace is put in backup mode the redo generation behaviour changes but there is not excessive redo generated, there is additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode.

As long as the table space is in backup mode Oracle will write the entire block is dumped to redo when the ALTER TABLESPACE TBSNAME BEGIN BACKUP MODE is entered but later it generates the same redo. This is done due to the reason Oracle can not guaranty that a block was not copied while it was updating as part of the backup.

Let’s go explain better this part:
In hot backup mode only 2 things are different:
• The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) are written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously. Let’s say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile the DBWR has asked to rewrite this block. The OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an impossible block -- the head and tail are from two points in time. We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself at least. We can recover it from there.

• The datafile headers which contain the SCN of the last completed checkpoint are NOT updated while a file is in hot backup mode. DBWR constantly write to the datafiles during the hot backup. The SCN recorded in the header tells us how far back in the redo stream one needs to go to recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablespace at a time in backup mode and bring the tablespace out of backup mode as soon
as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

#2-What are the differences between REDO and UNDO?

To clear this question we have this table:


Important points about LOGGING and NOLOGGING

Despite the importance of the redo entries, Oracle gave users the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode.
NOLOGGING affect the recoverability. 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, this are some points regarding it:

NOLOGGING is designed to handle bulk inserts of data which can be easy reproduced.
► Regardless of LOGGING status, writing to undo blocks causes 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 in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.
► Any change to the database dictionary will cause redo generation. This will happen to protect the data dictionary. An example: if we allocated a space above the HWM for a table, and the system fail in the middle of one INSERT /*+ APPEND */ , the Oracle database will need to rollback that data dictionary update. There will be redo generated but it is to protect the data dictionary, not your newly inserted data (Oracle will undo the space allocation if it fails, where as your data will disappear).
► The data which are not logged will not be able to recover. The data should be backed up after the modification.
► Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
NOLOGGING is not needed for Direct Path Insert if the database is in NOARCHIVE LOG MODE. (See table 1.1)


The data which is not able to reproduce should not use the NOLOGGING mode. If data which can not be reloaded was loaded using NOLOGGING. The data cannot be recovered when the database crashes before backing the data.

NOLOGGING does not apply to UPDATE, DELETE, and INSERT.
NOLOGGING will work during certain situations but subsequent DML will generate redo. Some of these situations are:
o direct load INSERT (using APPEND hint),
► 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 tablespace in which it resides.

The following operations are a few that cannot make use of NOLOGGING mode:
► Table redefinition cannot be done NOLOGGING.
► Temp files are always set to NOLOGGING mode.

The FORCE LOGGING mode is a persistent attribute of the database. That is, if the database is shut down and restarted, it remains in the same logging mode state. FORCE
LOGGING must be configured again after recreating the control file.

If the database has a physical standby database, then NOLOGGING operations will render data blocks in the standby “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 objects that were previously written with the NOLOGGING option, you will an error like this:

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"

That doesn't sound good, and certainly I can't imagine a happy DBA called at 3:00 AM to recover a database and that error message comes up.

The options UNRECOVERABLE (introduced in Oracle7) and NOLOGGING (introduced in Oracle8) can be used to avoid the redolog entries generation for certain operations that
can be easily recovered without using the database recovery mechanism. This option sends the actual DDL statements to the redo logs (this information is needed in the data
dictionary) but all data loaded, modified or deleted are not sent to the redo logs.

Even though you can set the NOLOGGING attribute for a table, partition, index, or tablespace, this mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. See more details on which operations are supported to be executed in this mode in the following topics.

Why Oracle generates redo and undo for DML

When you issue an insert, update or delete, Oracle actually makes the change to the data blocks that contain the affected data even though you have not issued a commit. To
ensure database integrity, Oracle must write information necessary to reverse the change (UNDO) into the log to handle transaction failure or rollback. Recovery from media
failure is ensured by writing information necessary to re-play database changes (REDO) into the log. So, UNDO and REDO information logically MUST be written into the transaction log of the RDBMS (see below regarding temporary tables).

While the RDBMS logically would only need to write UNDO and REDO into the transaction log, the UNDO portion must also be kept online (on disk and accessible to the RDBMS engine) to enable rollback of failed transactions. If UNDO data was only stored in the transaction log, the log could get archived and the RDBMS would have to try and read it from tape. On some platforms, the tape could be sitting in the DBA's desk drawer, so there are practical problems with this solution. Every RDBMS must meet the basic requirement of online access to undo data, and Oracle does this by storing UNDO data in what we call Rollback Segments (rollback = undo).

Because Oracle places UNDO data into a rollback segment and also must (logically) place this data into the transaction log, it is simpler to just treat rollback tablespaces like
any other tablespace from a log generation perspective. That is, Oracle generates REDO for a Rollback Segment, which is logically the same as UNDO for a data block (i.e. your
table, index, etc.).

Oracle's transaction log is really called the REDO log because it only contains redo records. There logically MUST be UNDO records stored in the log, but they are stored in
the form of redo for rollback segments.

For temporary tables, Oracle will need to do things like facilitate rollback, but it is not necessary to bring back temporary tables following media failure.

The undo data is also needed for things like rollback to save point and read consistency, not just to reclaim space that was used by that temporary table.

Amount of redo generated for temporary tables
Metalink Note 94402.1

The amount of log generation for temporary tables should be approximately 50% of the log generation for permanent tables. However, you must consider that an INSERT
requires only a small amount of "undo" data, whereas a DELETE requires a small amount of "redo" data. If you tend to insert data into temporary tables and if you don't delete the data when you're done, the relative log generation rate may be much lower for temporary tables that 50% of the log generation rate for permanent tables.

Can Redo Generation Be Disabled During Materialized View Refresh?
Metalink Note 334878.1

There is no way to turn off redo generation when refreshing materialized views.

Setting the NOLOGGING option during the materialized view creation does not affect this, as the option only applies during the actual creation and not to any subsequent actions on the materialized view.

Enhancement requests have been raised to be able to turn off redo generation during a refresh but these were rejected as this could put the database into an inconsistent state and
affect options such as Data Guard as well as backup and recovery.

The amount of redo generated can be reduced by setting ATOMIC_REFRESH=FALSE in the DBMS_MVIEW.REFRESH options.

Flashback and NOLOGGING

When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and data files affected
by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 8:00 to 8:15 on April 7, 2008, and you later need to use Flashback Database to return to the target time 08:07 on that date, the objects and data files updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected data files immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.

Performance and Recovery considerations

The NOLOGGING mode improves performance because it generates much less log data in the redo log files helping in eliminating the time needed to execute the redo generation (latch acquisition, redolog writing, etc.). The user is responsible for backing up the data after a NOLOGGING insert operation in order to be able to perform media recovery.

Be aware that this feature disables the recover mechanisms for this transaction: It will be required to repeat the process from the very beginning in case of a database or instance failure.

Wait for next part, we will touch matter with Disabling Redo Generation.

go to Part 1: Introduction


Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 0 guests online.


Locations of visitors to this page

hidden hit counter