Hi,

I will explain how to solve ” ORA-01578: ORACLE data block corrupted (file # string, block # string) ” error.

ORA-01578: ORACLE data block corrupted

Details of error are as follows.

ORA-01578: ORACLE data block corrupted (file # string, block # string)

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.

Errors in file /u01/app/oracle/diag/rdbms/MSDB/MSDB/trace/MSDB_j000_33284.trc
(incident=663845):
ORA-01578: ORACLE data block corrupted (file # 7, block # 2241925)
ORA-01110: data file 7: '+DATATEST/MSDB/DATAFILE/default_tbs.298.1031185857'
ORA-26040: Data block was loaded using the NOLOGGING o

Oracle Block Corruption

Check the block corruption exists or not with the following script.

SQL> select * from v$database_block_corruption;

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
        7     2241925        128                  0 CORRUPT            0
       137    3464833        127                  0 CORRUPT            0
        93    4156032          1         1.4648E+11 CORRUPT            0
        93    4156033        127                  0 CORRUPT            0
        92      20737        127                  0 CORRUPT            0
        93    3393024        128                  0 CORRUPT            0
       137     831872        128                  0 CORRUPT            0
       137    2277888        128                  0 CORRUPT            0

---------- ---------- ---------- ------------------ --------- ----------
       137    3596416        128                  0 CORRUPT            0
       137    3464833        127                  0 CORRUPT            0
        93    4156033        127                  0 CORRUPT            0
        92      20737        127                  0 CORRUPT            0

Use the  DB verify utility to understand which blocks are impacted.

[oracle@msdbadm01 ~]$ dbv file=+DATA/MSDB/datafile/test01.dbf
 
DBVERIFY: Release 12.1.0.2.0 - Production on Tue Jul 19 12:49:19 2016
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
DBVERIFY - Verification starting : FILE = +DATA/MSDB/datafile/test01.dbf
Page 131 is marked corrupt
Corrupt block relative dba: 0x01400083 (file 7, block 2241925)
Completely zero block found during dbv:
 
 
 
DBVERIFY - Verification complete
 
Total Pages Examined         : 640
Total Pages Processed (Data) : 4
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 130
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 505
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2631270 (0.2631270)

You can also list the corruption with RMAN as follows.

RMAN> backup check logical validate database;

You can learn which of segment ( Table, index ) include this block with the following script.

SELECT 
tablespace_name, 
segment_type,
owner, 
segment_name 
FROM dba_extents 
WHERE file_id = 7
AND 2241925 BETWEEN block_id AND block_id + blocks - 1;

To repair the corruption you can recreate the table from Dataguard if you have standby.

If the corruption exist in the index segment, then you can drop and create it again.

If you have the latest backups ( Full backup and archivelog backups ), then you can restore and recover the related datafile as follows.

RMAN> alter database datafile '+DATA/MSDB/datafile/test01.dbf' offline;
RMAN> restore datafile 7;

RMAN> recover datafile 7;

RMAN> alter database datafile '+DATA/MSDB/datafile/test01.dbf' online;

Block Media Recovery (BMR) method

Or you can use the Block Media Recovery (BMR) method to repair the related blocks as follows.

BLOCKRECOVER DATAFILE 137 BLOCK 3464832;

BLOCKRECOVER DATAFILE 137 block 3844717 DATAFILE 38 block 1490618;

You can recover the related block from Disk or Tape backup as follows.

run
{
ALLOCATE CHANNEL CH1 TYPE 'DISK';
BLOCKRECOVER DATAFILE 92 BLOCK 20737;
RELEASE CHANNEL CH1;
}


run
{
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
send 'NSR_ENV=(NSR_CLIENT=NSR_CLIENT_NAME,NSR_SERVER=NSR_SERVER_NAME) ';
BLOCKRECOVER DATAFILE 92 BLOCK 20737;
RELEASE CHANNEL CH1;
}

You can repair all physically corrupted blocks recorded in the view with the following command.

RMAN> RECOVER CORRUPTION LIST;

Now problem has been solved.

To prevent Block corruption, you should set the following parameters as FULL.

db_block_checking string FALSE
db_block_checksum string TYPICAL



SQL> alter system set db_block_checking=FULL scope=both sid='*';

System altered.

SQL> alter system set db_block_checksum=FULL scope=both sid='*';

System altered.

If these parameters value are FULL as follows, then Database will prevent from Block corruption.

SQL> show parameter db_block_check

NAME                     TYPE           VALUE
------------------------------------ ----------- ------------------------------
db_block_checking        string         FULL
db_block_checksum        string         FULL
SQL>

Data recovery advisor is also used to repair block corruptions using the Block Media Recovery (BMR) method. You can read and apply the following post to learn what is the Data recovery advisor and how to use it.

Do you want to learn Oracle Database for Beginners, then read the following articles.