Impact Of Nologging Operations In High Availability Environments

[Vinod Sadanandan]

IMPACT OF NOLOGGING OPERATIONS IN HIGH AVAILABILITY ENVIRONMENTS

Logged operation has to generate redo for every change data or undo block, nologging operations indicate that the database operation is not logged in the online redo log file. Even though a small invalidation redo record is still written to the online redo log file, nologging operations skip the redo generation of the corresponding inserted data. nologging can be extremely beneficial for the following reasons:
* data written to the redo is minimized dramatically
* time to insert into a large table or index or LOB can be reduced dramatically
* performance improves for parallel creation of large tables or index

-CREATE TABLE ... AS SELECT (CTAS)
- CREATE INDEX
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER INDEX ... SPLIT PARTITION
- ALTER INDEX ... REBUILD
- ALTER INDEX ... REBUILD PARTITION
- INSERT, UPDATE, and DELETE on non-internal LOBs in NOCACHE NOLOGGING

SQL> create table TEST.T nologging as select num_rows from dba_tables;
 
Table created.
 
SQL> select count(*) from test.t;
 
  COUNT(*)
----------
      1517
 
SQL> create index my_index on TEST.T(NUM_ROWS) nologging;
 
Index created.
 
SELECT NAME, UNRECOVERABLE_CHANGE#,                
TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
FROM V$DATAFILE;
 
UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE_TIME,'DD'
--------------------- -------------------------------------------
 
 
/oracle/oracle/oradata/oracle/orcl/system01.dbf
               451816 13-NOV-2007 02:53:21
 
/oracle/oracle/oradata/oracle/orcl/undotbs10.dbf
 
 
/oracle/oracle/oradata/oracle/orcl/trade_121.dbf
               451733 13-NOV-2007 02:50:13
 

Once Redo Apply (or Media Recovery) processes the invalidation redo, it marks all the corresponding data blocks corrupt in standby DB,moreover standby database will encounter corrupted blocks on the physical standby database when a query is executed on any data that references these data blocks.

ORA-01578: ORACLE data block corrupted (file # 3, block # 514)
ORA-01110: data file 3: '/oracle/oracle/oradata/oracle/orcl/trade_121.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


However, NOLOGGING is intended for configurations in which media recovery or the recovery of the corresponding object is not important .

Thanks & Regards,
Vinod Sadanandan
Oracle DBA