Re: ORA-01578 oracle data block corrupted.

jo_at_cci.dk (Jan Overgaard) wrote:
>
> In article lsn_at_arc.electriciti.com, Paul Singer <singerap_at_powergrid.electriciti.com> () writes:
> >rmte_at_ix.netcom.com (rocky termanini) wrote:
> >>
> >> We have been running into the ORA-01578 (oracle data block corrupted)
> >> error on a regular basis. Most of the corruptions have been found in
> >> our indexes, though a few have been on tables.
> >
> >
> >This is NOT good news. It could, and probably does, mean that
> >you have lost data. I hope your backups were good.
> >
> >Oracle assues that the database is in good condition when it is
> >started. It does not check each block of the database when it
> >starts up. If some errent program or process alters the contents
> >of an Oracle database file Oracel will not detect the error
> >until it actually trys to read from, or writ to the damaged database
> >block. There is a crc or some simular chcking built into each block
> >header. If Oracel reads a database block and it fails you will get
> >this error.
> >
> >Check the status of your disks. You could be due for a disk failure
> >or your file system could be curropted. If you recently restored
> >the database from tape you could have had a bad restore or bad blocks
> >on the tape. If you run a regular backup process check to make shure
> >that it is not messing with your database files.
> >
> >You can force Oracle to validate the status of your tables by
> >SELECT * FROM all your tables. This will chase out any further errors
> >that may be lurking there. Validat your indices.
> >
> >Good Luck.
>
>
>
> The action depends on type of object and you backup status.
>
> Index:
> drop and recreate index.
>
> Tables:
>
> If is not possible to recreate the table up to the point before the error you have
> the possibility of setting an event in your init.ora file instructing Oracle to
> skip the corrupted data.
>
> event = "10231 trace name context forever, level 10"
>
>
> If you got an index on the table then you can narrow the corrupted rows down with
> the following statement:
>
> select count(*) from Table
> where indexed_col > XX
> minus
> select count(*) from Table
>
>
> Be careful that you remember to remove the envent again from the init.ora file.
>
>
> Good luck
>
>
> Jan
>
>

I remember encountering ORA-1578 error last year after migrating a V6.0.34 database on RS/6000 to V7.0.15 using Oracle's migrate utility. This was a 24x7 database and that's why a quick upgrade was preferred. One of the indexes got corrupted and we couldn't drop the index without dropping the associated table. But, Oracle helped us drop the corrupted index by "tweaking" one of the system tables. To prevent the problem in future, we dropped and recreated all indexes with FREELISTS=1. This bug was later fixed in 7.0.16.

The bug affects indexes with multiple freelists when there is a lot of concurrent update activity against the table. Corruption occurs during insert but is detected during delete. In V6, you had to specify parameters free_list_inst and free_list_proc in init.ora. In V7, they were replaced by FREELISTS parameter during index creation. If you use FREELISTS=1, this bug did not affect you. Also, this bug does not exist in 7.0.16 and later versions.

I don't know if the above helps, because I don't know the Oracle versions referenced in this posting.

                                  """
                                 (o o)
+---------------------------------(_)----------------------------------+
|                                _/ \_                                 |
|                             Vish Gopalan                             |
|                         Information Systems                          |
|                      Architecture & Integration                      |
|                   International Space Station, NASA                  |
|                                                                      |
| E-mail: vgopalan_at_borg24.jsc.nasa.gov           Phone: (713) 280-7473 |
+----------------------------------------------------------------------+