Corrupt blocks by NOLOGGING - Error explanation and solution

This document is intended for Customers and Oracle Support.

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

If the associated redo/archived log file is used to RECOVER the data files, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads.

Errors Example:

SQL> select * from test_nologging;

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
ORA-01110: data file 4: '/oradata/users.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The NOLOGGING attribute is stored in column LOGGING in data dictionary views like:

DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

LOGGING='NO' indicates NOLOGGING.

The way for Oracle to identify that the block was previously invalidated due to NOLOGGING is by updating most of the bytes in that block with 0xff only if that "invalidate" redo is applied in a Recovery.

The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.

The SCN in the block corresponds to the SCN in the REDO RECORD for when the "INVALIDATE" change was applied in a recovery. This is useful to know the timestamp for when the block was marked as soft corrupt due to NOLOGGING.

RMAN/DBV and Corrupted Blocks by NOLOGGING

DBV prints the generic message DBV-200 in rdbms versions lower than 10.2.0.4 and error DBV-201 in RDBMS versions greater or equal to 10.2.0.4  ( Note  5031712.8 ):

DBV-00200: Block, dba 46137428, already marked corrupted
DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

In rdbms versions lower than 10.2.0.5 RMAN reports is with a generic message like:

RMAN reports it in v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

When there is a generic message besides the error ORA-26040, a block dump might be taken and see if the byte 0xff is along the block or if the block is associated to a segment, try to read it with a SQL statement for which errors ORA-1578/ORA-26040 will be produced if the block is corrupt due to a recovery with a NOLOGGING operation.

For RMAN to identify if the block is corrupt by NOLOGGING, an enhancement has been provided in Bug 7396077.  See Note 7396077.8

RMAN backups don't fail due to NOLOGGING corrupt blocks. In general RMAN does not fails with soft corrupt blocks so the MAXCORRUPT clause is not necessary in such cases.

Important change in 11g

FORCE LOGGING is irrelevant in NOARCHIVELOG mode; this was a change introduced in 11g.

Reference Note 1071869.1

SOLUTION

Note that the data inside the affected blocks is not salvageable. Methods like "Media Recovery" or "RMAN blockrecover" will not fix the problem unless the data file was backed up after the NOLOGGING operation was registered in the Redo Log.

In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

• Identify the object as described in Note 819533.1

• If it is an INDEX, drop/create the index.

• If it is a TABLE then procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements and decide to re-create the table. Note 556733.1 has an example of DBMS_REPAIR. 

• If it is a LOB segment associated to a LOB column in a Table, use Note 293515.1

• If the error is produced in a Physical STANDBY database, the option is to restore the affected file from the PRIMARY database (only if the problem is not present in the PRIMARY).

Run script provided in Note 472231.1 to identify any additional corrupted objects.

 

References

NOTE:1071869.1 - ORA-1578 ORA-26040 in 11g for DIRECT PATH with NOARCHIVELOG even if LOGGING is enabled
NOTE:290161.1 - The Gains and Pains of Nologging Operations
NOTE:293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
NOTE:472231.1 - How to identify all the Corrupted Objects in the Database reported by RMAN
NOTE:556733.1 - DBMS_REPAIR SCRIPT
NOTE:7396077.8 - Bug 7396077 - RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY

 ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution [ID 794505.1]