Data block corruption cleared with alter system flush buffer_cache

So I had:
ORA-08103: object no longer exists
being reported on SQL affecting one application table. But that table existed ok in dba_tables, could be described ok, and selects restricted to its indexed columns returned data ok.

Suspicion was some sort of data block corruption. Rman backup logs had not reported any corrpution, but dbverify did find some corrupt blocks. Those blocks were not associated with any record in dba_extents though.

All that is curious enough, but it is the fix which was the real surprise. Running:
SQL> alter system flush buffer_cache ;
Fixed the problem. Implication of that is that there is intermittent data block corruption, and that table was corrupt at the time it was first read into cache, and clean again (on disk) by the time I came along to look at it.

Dbverify later on also switched over to giving the datafiles a clean bill of health.

What’s the root cause? Don’t know (yet), but intermittent disk or memory hardware failure seems the most likely. Nothing reported in server logs though.

This on Oracle 10.1.0.4.0 Enterprise Edition on Microsoft Windows Server 2003 Standard Edition Service Pack 1 on Intel Xeon 3.2GHz.

The dbverify syntax I used was like:
dbv file=D:\ORACLE\ORADATA\SYSTEM01.DBF logfile=4.log
I didn’t have to specify block size because database was at the default block size for that version of dbv (seen with dbv help=y).

April 19, 2007

  • Hi Andrew,
    this is very interesting. I had a similar efect at a customer’s site recently with 10gR2 where I encountered an object that “didi not exist any more”.
    It was possible to describe it but a SELECT COUNT(*) returned an ORA-600.
    There were no extents in DBA_EXTENTS and it was not possible to DROP it: also ORA-600.

    Since my customer did not use RMAN we ended up with a full export except this one object and full import into a new DB.
    Obviousely the Data Dictionary was corrupted.

    I guess it’s a BUG!
    Or is it a feature?
    =;-)
    Lutz

  • Leave a Reply

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