Andrew Fraser DBA

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).