OERR: ORA-1578 “ORACLE data block corrupted (file # %s, block # %s)” Master Note

 

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: [email protected]

APPLIES TO:

Oracle Database – Standard Edition – Version 8.0.6.0 to 12.1.0.1 [Release 8.0.6 to 12.1]
Oracle Database – Enterprise Edition – Version 8.0.6.0 to 12.1.0.1 [Release 8.0.6 to 12.1]
Information in this document applies to any platform.

PURPOSE

This article provides information about error ORA-1578 and possible actions.

SCOPE

This note is intended for general audience as initial starting point for beginning diagnosis of ORA-1578.

DETAILS

Error:  ORA-01578 (ORA-1578)
Text:   ORACLE data block corrupted (file # %s, block # %s)
Cause:  The data block indicated was corrupted, mostly due to software errors.
Action: Try to restore the segment containing the block indicated. This
may involve dropping the segment and recreating it. If there
is a trace file, report the errors in it to your ORACLE
representative.

 

Description

Error ORA-1578 reports a Physical Corruption within a block or a block marked as software corrupt.  Reference Note 840978.1 for Physical Corruption concept.

ORA-1578 – Solution (excludes NOLOGGING case)

  • Main article describing corruption issues in different Oracle areas and Solutions:
Note 28814.1 Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g

Database in ARCHIVELOG mode

    • Repair the Block with RMAN Block Media recovery.  In order to repair a block causing ORA-1578 with Block Media Recovery, the database must be in archivelog mode.
Note 144911.1 RMAN : Block-Level Media Recovery – Concept & Example

Note 342972.1 How to perform Block Media Recovery (BMR) when backups are not taken by RMAN

Database in NOARCHIVELOG mode or there is not a valid backup:

    • Identify the segment producing ORA-1578:
NOTE 819533.1 How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY

NOTE 472231.1 How to identify all the Corrupted Objects in the Database reported by RMAN

NOTE 836658.1 Identify the corruption extension using RMAN/DBV/ANALYZE etc. Main sections in Note 836658.1 to identify corrupt blocks causing ORA-1578 are:

RMAN – Identify Datafile Block Corruptions
DBVerify – Identify Datafile Block Corruptions

    • For INDEX segment type consider to recreate the index.
    • Drop the segment and recover it from a different source.  Or use the next options to recover the information from the current segment:
    • For TABLES the corrupt block can be skipped using DBMS_REPAIR and decide to create a new table using “Create Table As Select”:
Note 556733.1 DBMS_REPAIR script
Note 68013.1 DBMS_REPAIR example
    • A Datapump export with ACCESS_METHOD=DIRECT_PATH (default value) may also be used to skip the corrupt block, then the table may be truncated or dropped and imported.
    • Another option is to MOVE the table with “ALTER TABLE MOVE &TABLE_NAME” as the MOVE skips corrupt blocks causing ORA-1578; it is recommended to take a backup (e.g. datapump export before moving the table it).
    • Reference Note 28814.1 for additional cases.

ORA-1578 / ORA-26040 due to NOLOGGING

Error ORA-1578 can also be produced along with error ORA-26040 meaning that the block is corrupt due to a NOLOGGING operation after a recovery.

ORA-1578 / ORA-26040 due to NOLOGGING – SOLUTION

  • Reference the next article to fix error ORA-1578 caused by NOLOGGING:
Note 794505.1 ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING – Error explanation and solution

ORA-1578 due to incorrect wallet in encrypted database

  • Reference the next article for ORA-1578 caused by incorrect wallet:
Note 1329437.1 ORA-1578 Corrupt Block Found in Encrypted Database

 

Known Corruption issues caused by 3rd party Software Provider

  • Reference the next document for 3rd party known issues causing corruption:
Note 1323649.1 Known Corruption issues caused by 3rd party Software Provider

 

White Paper: Preventing, Detecting, and Repairing Block Corruption: Oracle Database 11g

Oracle Maximum Availability Architecture White Paper

 

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB Prob Bug Fixed Description
III 16776922 12.1.0.2, 12.2.0.0 ORA-1578/ORA-600 block corruption messages on the temporary data blocks
E I 22228324 12.2.0.0 Enhancement to Avoid Block Memory Corruption being propagated to Disk by Direct Path (prevents future ORA-1578 / adds ORA-600[kcblco_2] )
II 20437153 12.2.0.0 Unnecessary incident files after ORA-603 following CTRL-C or session kill on Global Temporary Tables
+ 20144308 12.2.0.0 ORA-27086 or ORA-1182 RMAN May Overwrite a SOURCE Database File during TTS, TSPITR, etc when OMF is used in SOURCE. ORA-1578 ORA-1122 in SOURCE afterwards
II 18323690 12.1.0.2, 12.2.0.0 ORA-600 [kcbz_blk_decrypt_failed] [2001] / ORA-1578. Logical Corrupt undo block code 2001 when decryption with incorrect wallet. Error message changed
II 18252487 12.1.0.2, 12.2.0.0 ORA-1578 for an encrypted block (TDE) after master REKEY of incorrect wallet. Error message changed
E I 17511071 12.1.0.2, 12.2.0.0 Datapump expdp silently skips corrupt block that produce ORA-1578 – This fix prints a warning message in export log when the corupt block is the first block
II 17210525 12.2.0.0 ORA-1 on SYS.I_PLSCOPE_SIG_IDENTIFIER$ / ORA-600 [kqlidchg0] / ORA-1578 in SYSTEM or SYSAUX Tablespaces
IIII 17437634 11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4.2, 11.2.0.4.BP03, 12.1.0.1.3, 12.1.0.2 ORA-1578 or ORA-600 [6856] transient in-memory corruption on TEMP segment during transaction recovery / ROLLBACK (eg: after Ctrl-C)
I 20658524 A query using direct read may fail with ORA-1578 ORA-26040 due to former corrupt block version
II 14828059 11.2.0.3.BP15, 11.2.0.4, 12.1.0.1 Wrong Results / False ORA-1578 in SuperCluster
III 13804294 11.2.0.3.4, 11.2.0.3.BP07, 11.2.0.4, 12.1.0.1 Internal errors, corruptions, using pipelined function whose rows raise exceptions
P I 12330911 12.1.0.1 EXADATA LSI firmware for lost writes
I 11707302 11.2.0.2.3, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.1 Corruption from ASM crash during rebalance diskgroup. Misplaced Blocks
II 11659016 11.2.0.3, 12.1.0.1 ORA-1578 against recently create tablespace that once was encrypted
+ II 10209232 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2.1, 11.2.0.2.BP02, 11.2.0.2.GIBUNDLE01, 11.2.0.3, 12.1.0.1 ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM
* III 10205230 11.2.0.1.6, 11.2.0.1.BP09, 11.2.0.2.2, 11.2.0.2.BP04, 11.2.0.3, 12.1.0.1 ORA-600 / corruption possible during shutdown in RAC
I 9965085 11.2.0.3, 12.1.0.1 ORA-1578 / ORA-8103 Temporary table block corruption / space wastage from PDML – superseded
III 9739664 11.2.0.2, 12.1.0.1 ORA-1578 / ORA-26040 MANUAL RECOVER marks block as corrupt NOLOGGING in even if LOGGING is enabled
+ III 9724970 11.2.0.1.BP08, 11.2.0.2.2, 11.2.0.2.BP02, 11.2.0.3, 12.1.0.1 Block Corruption with PDML UPDATE. ORA_600 [4511] OERI[kdblkcheckerror] by block check
II 9407198 11.2.0.3, 12.1.0.1 “LOG ERRORS INTO” can cause ORA-600 [kcb***] or hang scenarios
* II 9406607 11.2.0.1.3, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.1 Corrupt blocks in 11.2 in table with unique key. OERI[kdBlkCheckError] by block check
* III 8943287 11.2.0.2, 12.1.0.1 ORA-1578 corrupt block with AUTH SQL*Net strings
* III 8898852 11.1.0.7.2, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1 ORA-1578 Blocks misplaced in ASM when file created with compatible.asm < 11 and resized
III 8885304 11.2.0.2, 12.1.0.1 ORA-7445 [ktu_format_nr] during RMAN CONVERT or Corrupt fractured block of UNDO tablespace datafile
* III 8768374 10.2.0.5, 11.1.0.7.8, 11.2.0.1.BP11, 11.2.0.2, 12.1.0.1 RFS in Standby with a wrong location for archived log corrupting/overwriting database files when max_connections > 1
E II 8760225 11.2.0.2, 12.1.0.1 Auto Block Media Recovery reports ORA-1578 on first query
II 8731617 11.2.0.3, 12.1.0.1 ORA-1578 from DESCRIBE or CTAS even if table not accessed / ORA-959 from DBMS_STATS
E II 8720802 10.2.0.5, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1 Add check for row piece pointing to itself (db_block_checking,dbv,rman,analyze)
E II 8493978 11.2.0.2, 12.1.0.1 Reserve file descriptors for datafile access
II 10025963 11.2.0.1.BP09, 11.2.0.2 Block corruption of LOB blocks with checksum value but block has checksum disabled
II 8714541 11.2.0.2 ORA-1578 Corrupt Block in ASM with 0xbadfda7a after ASM block repair due to disk read error when ASM mirror is used
I 13101288 ORA-600, corruption or check errors dropping a column in a OLTP compressed table
+ 8354682 11.2.0.1 ORA-1578 – Blocks can be misplaced in ASM when there is IO error and AU > 1MB
+ III 8339404 10.2.0.5, 11.1.0.7.1, 11.2.0.1 ORA-1578 – Blocks can be misplaced in ASM during a REBALANCE
8227257 11.2.0.1 ORA-1578 corruption found after media recovery on encrypted datafile
E II 7396077 10.2.0.5, 11.2.0.1 RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
6471351 10.2.0.5, 11.1.0.7, 11.2.0.1 ORA-1578 / ORA-26040 due to NOLOGGING after recovery despite of FORCE LOGGING
II 6674196 10.2.0.4, 10.2.0.5, 11.1.0.6 OERI / buffer cache corruption using ASM, OCFS or any ksfd client like ODM
5515492 10.2.0.3, 11.1.0.6 ORA-1578 corruption with Block Misplaced during ASM rebalance after IO error
E 5031712 10.2.0.4, 11.1.0.6 DBV enhanced to report NOLOGGING corrupt blocks with DBV-201 instead of DBV-200
+ 4724358 11.1.0.6 ORA-27045 ORA-1578 ORA-27047 corruption caused by DBMS_LDAP
4684074 10.2.0.2, 11.1.0.6 OERI:510 / block corruption (ORA-1578) with DB_BLOCK_CHECKING
4655520 10.2.0.3, 11.1.0.6, 9.2.0.8 Block corrupted during write not noticed
4411228 9.2.0.8, 10.2.0.3, 11.1.0.6 ORA-1578 Block misplaced with mixture of file system and RAW files
II 4344935 10.2.0.4, 11.1.0.6 OERI from DML on TEMPORARY TABLE after autonomous TRUNCATE
II 7381632 11.1.0.6 ORA-1578 Free corrupt blocks may not be reformatted when Flashback is enabled
8976928 10.2.0.5 ORA-1578 caused by a former free corrupt block and remains unformatted
I 8684999 10.2.0.5 ORA-1578 caused by a former free corrupt block and remains unformatted
+ 3544995 9.2.0.6, 10.1.0.3, 10.2.0.1 LOB segments with “CACHE READS” generate no REDO even with the logging option
+ 1281962 9.2.0.1 Media recovery after ORA-1578 on rollback can cause logical inconsistency
589855 7.3.3.6, 7.3.4.1 ORA:1578 or ORA:8103 selecting invalid ROWID
406863 7.3.3.4, 7.3.4.0, 8.0.3.0 ORA-1578 using PQ with heavy simultaneous INSERTS
P 707304 7.3.4.4 AIX: Resizing RAW datafile can corrupt a DB block
603502 7.3.4.3, 8.0.4.4, 8.0.5.0 Possible Corruption if a session with LOOPBACK DB Links aborts.

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *