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 will need to rollback that data dictionary update.

There will be redo generated but it is to protect the data dictionary, not yournewly 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 NO

ARCHIVE LOG MODE. (See table 1.1)

 

Table Mode Insert Mode ArchiveLog Mode Result
LOGGING APPEND ARCHIVE LOG REDO GENERATED
NOLOGGING APPEND ARCHIVE LOG NO REDO
LOGGING NO APPEND ARCHIVE LOG REDO GENERATED
NOLOGGING NO APPEND ARCHIVE LOG REDO GENERATED
LOGGING APPEND NO ARCHIVE LOG NO REDO
NOLOGGING APPEND NO ARCHIVE LOG NO REDO
LOGGING NO APPEND NO ARCHIVE LOG REDO GENERATED
NOLOGGING NO APPEND NO ARCHIVE LOG REDO GENERATED
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),

o CREATE TABLE ... AS SELECT,

o CREATE INDEX.

_ 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.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-733270/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-733270/