Sunday, March 4, 2018

Oracle nologging - does it generate redo?


Some SQL statements and operations support the use of a NOLOGGING clause. This does not mean that all operations against the object will be performed without generating redo log, just that some very specific operations will generate significantly less redo then normal.


All operations will generate some redo ‐ all data dictionary operations will be logged regardless of the logging mode.


Remember,It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load. Let's say you created a table using nologging, using CREATE TABLE AS SELECT NOLOGGING and that table is started using by the application now. Here if the disk that the table is on fails we can't do a media recovery even if our database is running in archivelog mode. The problem here is the table was created using nologging option and it was not logged, is not recoverable from the archived redo log. So after doing any nologging operation DBA has to take a full backup immediately.


Note:- 


NOLOGGING does not prevent redo from being generated by all subsequent operations. In the above example, I did not create a table that is never logged. Only the single, individual operation of creating the table was not logged. All subsequent ʹnormalʹ operations such as INSERTs, UPDATEs, and DELETEs will be logged. Other special operations such as a direct path load using SQLLDR, or a direct path insert using the INSERT /*+ APPEND */ syntax will not be logged.


After performing NOLOGGING operations in an ARCHIVELOG mode database, you must take a new baseline backup of the affected data files as soon as possible.


There are two ways to use the NOLOGGING option.

1.by embedding the keyword NOLOGGING in the SQL command itself at the appropriate location.

2.Second method allows operations to be performed implicitly in a

NOLOGGING mode. For example, I can alter a table to be NOLOGGING by default. This means that subsequent direct path loads and direct path inserts performed which affect this table, will not be logged.
Remember If you use the APPEND hint and place the table in nologging mode, redo will be bypassed. 

3.It is always recommenced to schedule any nologging operation(such as alter table...move partition + alter index...rebuild) to take place immediately before a backup occurs


The operations that may be performed in a NOLOGGING mode are:



DML:
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.
Direct Loader (SQL*Loader)
DDL:
CREATE TABLE … AS SELECT
CREATE TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS
ALTER TABLE … LOB_storage_clause … LOB_parameters … NOCACHE | CACHE READS(to specify logging of newly created LOB columns)
ALTER TABLE … modify_LOB_storage_clause … modify_LOB_parameters … NOCACHE| CACHE READS (to change logging of existing LOB columns)
ALTER TABLE … MOVE
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
CREATE INDEX
ALTER INDEX … REBUILD
ALTER INDEX … REBUILD [SUB]PARTITION
ALTER INDEX … SPLIT PARTITION

No comments:

Post a Comment