Skip to Main Content
  • Questions
  • ORA-01578: ORACLE data block corrupted (file # , block # )

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Akshay.

Asked: July 17, 2016 - 7:24 pm UTC

Last updated: July 20, 2016 - 3:14 pm UTC

Version: oracle 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,
I m getting following error in my production data base alert log since last 5 month.

Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_j000_26775.trc:
ORA-01578: ORACLE data block corrupted (file # , block # )
ORA-01578: ORACLE data block corrupted (file # 2, block # 88580)
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/sysaux01.dbf'
===========================================================================================
Select * from v$database_block_corruption;
File# Block# Block corruption_change# corruption_type
2 77846 1 0 FRACTURED
2 88580 1 0 FRACTURED
================================================================================================
SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 2
And 88580 between block_id AND block_id + blocks -1 ;

tablespace_name segment_type owner segment_name
SYSAUX INDEX SYS SHEDULER$_INSTANCE_PK

=====================================================================================================

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents
WHERE file_id = 2
and 77846 between block_id AND block_id + blocks -1 ;

tablespace_name segment_type owner segment_name
SYSAUX TABLE SYS SHEDULER$_JOB_RUN_DETAILS

===========================================================================================================

What should i do to resolved this error.I don't have any physical backup.


and Connor said...

With a corruption, I would log a call with Support so they can do a more thorough diagnosis.

For indexes, the fix is normally to rebuild the index

For tables, normally you might recovery or restore, but in your case it is the run details for scheduler jobs, which is probably not of great concern.

But what *is* of concern is how you got a corruption in the first place, which is why a full diagnosis with Support's assistance is a good way to go.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks oracle support

Akshay, July 19, 2016 - 10:53 am UTC

Hi Connor,

Thank You very much for prompt support.

Its great experience to interact with you.

I'll wait for your response which you will get from your support team.



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

Akshay, July 19, 2016 - 12:04 pm UTC

Hi Connor,

Thank you for your prompt support.

I will wait for your response which will you get from your support team.

===============================================
select job_name, status, additional_info
from user_scheduler_job_run_details;
/

select job_name, status, additional_info
from dba_scheduler_job_run_details;
/

I m getting following error in both cases when I run above queries.
=========================================================
ORA-01578: ORACLE data block corrupted (file # 2, block # 88580)
ORA-01110: data file 2: '/u01/app/oracle/oradata/aprod/sysaux01.dbf'
01578. 00000 - "ORACLE data block corrupted (file # %s, block # %s)"
*Cause:The data block indicated was corrupt. This was a physical corruption, also called a media corruption. The cause is unknown but is most likely external to the database. If ORA-26040 is also signaled, the corruption is due to NOLOGGING or UNRECOVERABLE operations.
*Action: The general method to repair a media corrupt block is to restore a backup and recover the backup. For databases in ARCHIVELOG mode, use block media recovery or media recovery. In some situations, you can also drop the segment and re-create it. For example, you can drop an index and re-create the index.
=======================================================
Please suggest me best way to resolved this problem.
Chris Saxon
July 19, 2016 - 2:04 pm UTC

Well the error tells you what to do:

Action: The general method to repair a media corrupt block is to restore a backup and recover the backup.

So you need to recover the affected blocks. But as Connor says, work with support.

Chris

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

A reader, July 20, 2016 - 10:41 am UTC

Hi All,

I want to add one more thing on 11 July 2016,All my DDMS job got broken when I investigate the issue and verify alert and trace file log. I found following error.

ORA-1653: unable to extend table MYLOG.MLOG by 8192 in tablespace MYLOG_USER.

Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_j000_26775.trc:
ORA-01578: ORACLE data block corrupted (file # , block # )
ORA-01578: ORACLE data block corrupted (file # 2, block # 88580)
ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/sysaux01.dbf'

Although my all tablespace are auto extended yet I was getting unable to extend table. After truncated related table ORA-1653:.. have resolved.

@Connor & Chris I just want to add this to describe complete scenario may be both issue are related each other.
and help oracle support to find root cause.

Thanks & Regards,
Akshay
Chris Saxon
July 20, 2016 - 12:58 pm UTC

Did you run out of space on the filesystem by any chance?

Chris

A reader, July 20, 2016 - 2:09 pm UTC

When table space issue is occur the DB Prod server reached above 85% disk space usage for more than 30 mins.

following are the current disk usage statistics.

Total Disk Usage: 76.9% (377 GB)Total Disk Capacity:490.1 GB
Free Disk Space Disk Size
Local Disk / 81% free 36.9 GB 45.1 GB
Local Disk /boot 44% free 84.5 MB 188.7 MB
Local Disk /tmp 90% free 1.7 GB 1.9 GB
Local Disk /u01 16% free 74.4 GB 442.8 GB


As per above statistics, 16% free disk space still their. As a general process, disk usages threshold are set between 85-90 % usage.
@Note:Block corruption alert is coming form last 4-5 month.
Akshay
Chris Saxon
July 20, 2016 - 3:14 pm UTC

Work with support. They will help you!

Thk's advice for Corruption segment

Kaiser Lee., November 23, 2017 - 1:04 pm UTC

Thank you four your advice of Corruption segment.

I did recovery the our database was successfully.



More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.