By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,262 Members | 1,161 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Impact Of Nologging Operations In High Availability Environments

P: 16
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

Expand|Select|Wrap|Line Numbers
  1. SQL> create table TEST.T nologging as select num_rows from dba_tables;
  2.  
  3. Table created.
  4.  
  5. SQL> select count(*) from test.t;
  6.  
  7.   COUNT(*)
  8. ----------
  9.       1517
  10.  
  11. SQL> create index my_index on TEST.T(NUM_ROWS) nologging;
  12.  
  13. Index created.
  14.  
  15. SELECT NAME, UNRECOVERABLE_CHANGE#,                
  16. TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
  17. FROM V$DATAFILE;
  18.  
  19. UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE_TIME,'DD'
  20. --------------------- -------------------------------------------
  21.  
  22.  
  23. /oracle/oracle/oradata/oracle/orcl/system01.dbf
  24.                451816 13-NOV-2007 02:53:21
  25.  
  26. /oracle/oracle/oradata/oracle/orcl/undotbs10.dbf
  27.  
  28.  
  29. /oracle/oracle/oradata/oracle/orcl/trade_121.dbf
  30.                451733 13-NOV-2007 02:50:13
  31.  
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
Nov 13 '07 #1
Share this Article
Share on Google+