NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:
DML:
1. Direct-path INSERT (serial or parallel) resulting either from an INSERT or a
MERGE statement. NOLOGGING is not applicable to any UPDATE operations
resulting from the MERGE statement.
2. Direct Loader (SQL*Loader)
DDL:
1. CREATE TABLE … AS SELECT
2. CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE |
CACHE READS
3. ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE |
CACHE READS (to specify logging of newly created LOB columns)
4. ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters
… NOCACHE | CACHE READS (to change logging of existing LOB columns)
5. ALTER TABLE … MOVE
6. ALTER TABLE … (all partition operations that involve data movement)
– ALTER TABLE … ADD PARTITION (hash partition only)
– ALTER TABLE … MERGE PARTITIONS
– ALTER TABLE … SPLIT PARTITION
– ALTER TABLE … MOVE PARTITION
– ALTER TABLE … MODIFY PARTITION … ADD SUBPARTITION
– ALTER TABLE … MODIFY PARTITION … COALESCE SUBPARTITION
7. CREATE INDEX
8. ALTER INDEX … REBUILD
9. ALTER INDEX … REBUILD [SUB]PARTITION
10. ALTER INDEX … SPLIT PARTITION
For objects other than LOBs, if you omit this clause, then the logging attribute of the
object defaults to the logging attribute of the tablespace in which it resides.
For LOBs, if you omit this clause:
If you specify CACHE, then LOGGING is used (because you cannot have CACHE
NOLOGGING).
If you specify NOCACHE or CACHE READS, then the logging attribute defaults to the
logging attribute of the tablespace in which it resides.
NOLOGGING does not apply to LOBs that are stored inline with row data. That is, if
you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not
disabled STORAGE IN ROW, then Oracle ignores the NOLOGGING specification and
treats the LOB data the same as other table data.
The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader
and direct load INSERT operations are not logged. Subsequent DML statements
(UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING
attribute of the table and generate redo.
Nologging Table Still generating Lots Of Archive Logs [ID 976722.1]
Modified 28-NOV-2011 Type HOWTO Status MODERATED
In this Document
Goal
Solution
References
This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4 to 10.2.0.4 – Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 28-Nov-2011***
Goal
Why is a Nologging Table still generating lots of archive log.
Solution
The nologging concept is still the same. Some redo still does get generated for objects regardless if the nologging option is set.
When nologging is set, the amount of redo generated is reduced but it is still generated.
For instance the DDL and DML information regarding the creation of any new objects is logged in the REDO logs for recovery purposes .
The situations where no log is generated is covered by:
Note 188691.1 “How to Avoid Generation of Redolog Entries”
Nologging operations are invoked by any of the following:
* SQL*Loader direct load operations
* Direct load INSERT operations from CREATE TABLE | INDEX or INSERT commands
* Loading into an object containing LOB data when its object’s segment characteristic is NOCACHE NOLOGGING
For databases in ARCHIVELOG mode, nologging operations can only occur for a particular object if and only if:
* Database allows for nologging (ALTER DATABASE NO FORCE LOGGING) and
* Tablespace allows for nologging (ALTER TABLESPACE
* Object allows for nologging (ALTER TABLE
NOTE:
The above means that if FORCE LOGGING is enabled at a higher level, the NOLOGGING at a lower level has no effect. That means that even for a nologging table redo information may still be logged.
Another aspect you should keep in mind is that, if the database is in NOARCHIVELOG mode, then the NOLOGGING flag has no effect.
For more information refer to :
Note 290161.1 – The Gains and Pains of Nologging Operations
Take also into account that by default the Oracle Database does not provide any supplemental logging which means that by default LogMiner is not usable/useful.
Supplemental logging should be enabled prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled.
You can turn off Supplemental Logging by following command.
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
Minimal supplemental logging ensures that Logminer (and any products building on Logminer technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables.
Minimal Supplemental Logging
o is required for Logminer
o includes additional information in redo stream
In summary, enabling supplemental logging would also increase the amount of information gathered in the log because of the increased granularity.
References
NOTE:188691.1 – How to Avoid Generation of Redolog Entries
NOTE:290161.1 – The Gains and Pains of Nologging Operations
NOTE:750198.1 – Effect of Supplemental Logging on LogMiner with Example
