Corrupt db, ora-8102 and more

System: 8.1.7.4, Win2k,
running no archivelog, rule based , full exp taken daily

I am a developer with some Oracle DBA experience though far from a real DBA. However in our department the most experienced "DBA" so I take a hotline call. Customer is a small shop with no Oracle experience. Server is usually booted when there is a rare problem ...

Alertlog has no ORA-errors but points to several .trc files Tracefiles contains "ORA-08102 index key not found, obj# string ... " all for the same obj#
I do a full export as system with no errors. I do dbverify (with instance shut down) on every datafile. No error reported.
I restart the server - this is windoze afterall - problem still there.

I find the obj# from the ora-8102 in dba_objects and it points to a primary key index on a 70M rows table.
I drop and recreate the constraint and the index and restart all apps accessing the database. All seems fine. Several applications working fine, db-load is light. One app does nothing though it should. I trace it and find it does two sqls:

1. select count(pk_field) from small_t (are there any rows for me to process ?)
2. select * from small_t order by pk_field_index (get the rows for processing if sql 1 count > 0 )

I log in as appuser and
sql 1 returns 62 rows
sql 2 says "no rows selected" !! I dont believe my own eyes ?!

I further find that
Select * from small_t returns all 62 rows select * from small_t order by <any field that has an index> return zero rows !
select * from small_t order by <any field that has no index> does return all 62 rows
any select from small_t order by <any field that has an index> return zero rows !

What is going on here? Has anybody seen something like this before ?

At this point im very unsecure about the state of the database and what to do. I try validate index <index>. It just says index validated. I drop and recreate indexes on small_t and all selects now returns the expected rows. I restart the app that accesses small_t. It processes some rows after a few minuttes however the app crashes with an ORA-600 internal error. I find no other error messages than the ora-600.

What now? What actions should I take now? Right now I feel my best bet is to recreate the database from scrath and do a full import. This will cost the customer another day of production and will cause some loss of data, but the database seems to be unstable and the tools I used (dbverify and export) reported no errors.

Will apreciate any advice (other than get a real DBA or contact oracle support)

TIA
Peter Laursen Received on Fri Oct 03 2003 - 14:29:42 CDT