ORA-1578 ORA-26040 On Awr Table

郑全

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
This is applicable for 10gR2

Symptoms

In the trace file is reported:
   encountered error: (ORA-01578: ORACLE data block corrupted (file# xx, block# xxxx)
   ORA-01110: data file x: '.....'
   ORA-26040: Data block was loaded using the NOLOGGING option


Db Verify reports:
   DBV-00200: Block, dba xxxxxx, already marked corrupted

Cause

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ora-1578, ora-26040.

Note, no data is salvagable from inside the block. 
It's needed to recreate this object.

However, the corruptions may still remain in the SYSAUX tablespace until such time as the blocks get
re-used and hence reformatted.

Solution

The steps to recreate an AWR (Automatic Workload Repository) table are:

 1. Disable AWR statistics gathering by setting the statistics level to basic, e.g.:

      sqlplus /nolog
      connect / as sysdba
      show parameter statistics_level
      alter system set statistics_level = basic scope=both;

2. Stop and restart the instance in restricted mode, e.g.: 

      sqlplus /nolog
      connect / as sysdba
      shutdown immediate
      startup restrict

3. Drop and recreate the AWR objects, e.g.:

      sqlplus /nolog
      connect / as sysdba
      @?/rdbms/admin/catnoawr
      alter system flush shared_pool;
      @?/rdbms/admin/catawr.sql
      @?/rdbms/admin/utlrp

4. Then re-enable the AWR statistics gathering as required, by setting STATISTICS_LEVEL back to
     its original value, and restart the instance normally, e.g.:

      sqlplus /nolog
      connect / as sysdba
      alter system set statistics_level = <Previous Value from above> scope=both;
      shutdown immediate 
      startup

5. Apply latest patchset for 10GR2 (10.2.0.3 actually) which resolves a few AWR related corruption
    issues.

References

NOTE:293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors