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
_ 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
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/，如需转载，请注明出处，否则将追究法律责任。