Skip to end of metadata
Go to start of metadata

What is a NOLOGGING corruption?

 

Changes made in the index blocks during the creation of the index are not written to Oracle Redo Log Files, so the same information is not contained in the Oracle Archive Log Files too.
During a restore and recovery of such a block, Oracle recognize that the object was created with NOLOGGING option and marks these blocks internally. Later on RMAN or DBV consistency check will shows these blocks with error message ORA-00201 or DBV-00201.

As long as there are only indexes affected, this does not mean a real problem!

Advantages / Disadvantages of NOLOGGING option

Advantages

  • quicker index creation
  • less I/O
  • less Redo Log data created
  • less Archive Log created 
  • less time to restore the Archive Logs and to recover the data

Disadvantages

  • Longer time required for SAP System to back in busnies because indexes has to be recreated/reorganized after the restore and recovery.
  • After restore and recovery RMAN and DBV will constantly show ORA-00201 or DBV-00201 if the objects were not recreated/reorganized 

How are objects with NOLOGGING options created?

  1. Indexes in BW / BI Systems are usually created with NOLOGGING option

  2. Objects gets created during SAP Upgrade. Check below note.
    1155199 - NOLOGGING Option in Upgrade for Indices

  3. By specifying NOLOGGING during CREATE Statement in SQLPLUS
    SQL> CREATE TABLE .... NOLOGGING;
    SQL> CREATE INDEX ... NOLOGGING;

How to detect NOLOGGING corruption?

  1. Check the Oracle Alert Log for entries, like:
     
    ORA-01578: ORACLE data block corrupted (file # 8, block # 466)
    ORA-01110: data file 8: '/oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1'
    ORA-26040: Data block was loaded using the NOLOGGING option

  2. SQL Command Prompt:
     
    SQL> select count(*) from OLBUD; select count(*) from OLBUD       
    *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 8, block # 466)
    ORA-01110: data file 8: '/oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1'
    ORA-26040: Data block was loaded using the NOLOGGING option
  3. By checking the database or individual data files with DBV:

    -sh-4.1$ dbv file='/oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1'
    DBVERIFY: Release 12.1.0.2.0 - Production on Wed Aug 17 12:37:15 2016
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

    DBVERIFY - Verification starting : FILE = /oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1
    DBV-00201: Block, DBA 33554898, marked corrupt for invalid redo application

    ...
    ...
    ...

    DBVERIFY - Verification complete

    Total Pages Examined         : 1280
    Total Pages Processed (Data) : 245
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 76
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 958
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 1
    Total Pages Marked Corrupt   : 74
    Total Pages Influx           : 0
    Total Pages Encrypted        : 0
    Highest block SCN            : 50287726 (0.50287726)



  4. By checking the database or individual data files with RMAN:

    RMAN> backup validate check logical datafile 8;

    Starting backup at 17-AUG-16
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00008 name=/oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    List of Datafiles =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    8    OK     74             1            1283            50287726   
    File Name: /oracle/OL1/sapdata4/sr3manual_1/sr3manual.data1  
    Block Type Blocks Failing Blocks Processed  
    ---------- -------------- ----------------  
    Data       0              245              
    Index      0              76               
    Other      0              958            

    Finished backup at 17-AUG-16

How to get more information about the affected objects?

  1. Oracle Release <= 11g

    Oracle View
    V$DATABASE_BLOCK_CORRUPTION

  2. Oracle Release >= 12c 

    Oracle View
    V$DATABASE_BLOCK_CORRUPTION
    and
    V$NONLOGGED_BLOCK

How to get rid of ORA-00201 / DBV-00201?

  1. Specify FORCE LOGGING
    824212 - FORCE LOGGING for Oracle databases
  2. Use transaction SPRO to use LOGGING option for the indexes
    1812719 - Eliminate DBV-00201 and ORA-26040
  3. BR*Tools recognizes and rebuilds NOLOGGING indexes after a successful restore/recovery
    849485 - Reconstruction of the NOLOGGING indexes after recovery
    1400845 - Setting up NOLOGGING index partitions again after recovery
  4. Rebuild affected Indexes in SQLPLUS

Related Documents

Oracle Block Corruption

Related SAP Notes/KBAs

SAP Note 547464 - Nologging Option when creating indexes

SAP Note 365481 - Block corruptions

SAP Note 23345 - Consistency check of ORACLE database