Physical Corruption: ORA-1578 -Part 1

ORA-1578 is indication of physical block corruption. Oracle computes an internal checksum before fetching the block from disk, when it is about to fetch the block it reads the block header and compare the checksum stored in it. If the checksum mismatches we get ora-1578 error.
It indicates that a block is corrupted at
hardware level i.e at OS.

This error will always come whenever the corrupted block is read by Oracle.

Reasons for ora-1578:

1. Bad sectors in the Storage Disk Drive.
2. Formatting of blocks At OS level.( Zeroed out disk blocks).
3. some corruption in underlying hardware which is making block unreadable.

How to collect information for ora-1578:

Error Text:

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

The block # 38976 shows the corrupted block number and the file # 14 indicates
the reletive file number 14.

1. Get the absolute file number:

To get the absolute file # we will use the following query:

SQL > select file# from v$datafile where rfile#=14;

FILE#
——-
14

here the absolute file# is also 14.

2. Get the segment name and segment type in which this corrupted block resides:

Use the following query:

SQL > SELECT tablespace_name, segment_type, owner, segment_name FROM
dba_extents WHERE file_id = 14 and 38976 between block_id AND block_id + blocks – 1;

— It will give us the segment type (Table, Index, LOB etc.), name of the segment (Table name
Index name etc.), Owner of the segment and the tablespace name of the segment in which corrupted block resides.

SQL > select FILE#,TS#,STATUS,BLOCKS,NAME from v$datafile where FILE#=14;

— From this we will get the tablespace number and the fine name of the datafile in which the corrupted block resides.

SQL> select TS#,NAME from v$tablespace where TS#=;

— It will give us the Name of the tablespace.

Now we have all the information required to perform an action to overcome ora-1578.

I will be providing more on this about solving ora-1578 soon.

Saurabh Sood

Worked with prestigious Financial Institutions and Product Based MNCs e.g. Swiss Bank (UBS), CitiBank, Qatar Central Bank (QCB), Societe Generale, Oracle Corporation & Dell. An Oracle DBA who is up-skilling himself to help large enterprises move their data to cloud and get more insights into data to make it useful for them.

This Post Has 3 Comments

  1. ranz127

    what if datafile which corrupt is a datafile file# 1 (system01.dbf), how to recover it??

  2. Saurabh Sood

    You can use rman blockrecover command, if the number of blocks corrupted are large in number then you can consider doing closed DB recovery using restore system tablespace and recovering it.

    Thanks.
    Saurabh Sood

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.