(Update): My worst nightmare - ORA-8103

  • From: Maureen English <maureen.english@xxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, BORACLE@xxxxxxxxxxxx
  • Date: Tue, 17 Jun 2014 10:17:07 -0800

Thanks to all on both of my favorite listservs.

I'm still left with 2 questions....

1) Do you regularly run analyze table/index validate structure on the 
tables/indexes in your database?

2) How often to you rebuild indexes and how do you determine which indexes to 
rebuild?


For anyone who may be interested, here's an update.

We are still investigating the cause of the problem and are still fixing things 
in the database.
It looks like there may have been a hardware problem that started well before 
we noticed it and
got worse to the point where we had serious problems.  Note that we do cold 
backups every Friday.

5/23 - normal database backup, all is well
5/24 - refresh of materialized views in reporting instance on other server 
succeeds
5/24 - weekly gather stats job succeeds for all tables matching criteria

5/28 - ORA-00600 error that we missed - user ran a job that is known to have a 
problem
5/29 - ORA-00600 error that we missed - user ran FGITRND

5/30 - normal database backup, all appears well
5/31 - refresh of FGBTRND in reporting instance on other server fails with 
ORA-8103
5/31 - weekly gather stats job fails when gathering statistics on FGBTRND

6/01 - Banner upgrades (Student, Financial Aid, Faculty Grade Entry)
6/02 - refreshed a copy of prod database with prod...basically copied datafiles 
and ran a script
       we've used for years.  No apparent problems.

6/02 - Hardware problems with a SAN Director switch (I have no real details)

This is when we started seeing all kinds of ORA-8103 errors and ORA-600 errors 
that all seemed
to point to I/O loss.

We managed to clean up the ORA-8103 errors by copying the data to a temporary 
table, truncating
the original table, dropping indexes on the original table, then either copying 
the rows back
to the original table (small tables) or exporting the temp table data and 
importing it to the
original table.

Now we are left with about 12-15 tables that produce errors when we run analyze 
table...validate
structure on them.  It looks like most of these will be resolved by dropping 
and recreating the
index that's causing the mismatch.


I think that the hardware problem has been resolved, but since we don't yet 
know what exactly
happened, I'm still in a panic.

- Maureen
--
//www.freelists.org/webpage/oracle-l


Other related posts: