RE: (Update): My worst nightmare - ORA-8103

1. nearly never. Use dbms_stats (or the fnd_ equivalent for E-biz, which invokes dbms_stats preserving some special seeded data and with specific options that tend to yield good plans for E-biz. Some other suites have their own recommended cover routine alternatives to individually running dbms_stats). Sometimes run an index cacheing scan for performance reasons but that is very specialized case by case (and more difficult than you might guess with adaptive direct read.) Sometimes force scan a table or partition through the sga (avoiding adaptive direct read) for reasons of delayed block cleanout and/or situational cache for performance. Hints on avoiding adaptive direct read, if you don't want to set the parameters: a) small range scans for the indexes ultimately covering low through high, b) Read one rowid per block having fetched same via an index access and dbms_rowid packages and then use that list of rowids and some non-indexed block in an unmerged outer query.
2. Classic depends. Mostly indexes stay just fine for the long haul. JL, Foote, some others, and I have written a lot about broad and special cases where either through bug or inherent characteristic of use you should rebuild. In big themes, a)bitmaps read all about them b) monotonically increasing keys with subsequent deletes can strand empty blocks under some conditions, c) If you reach a large loaded number of rows and then subsequently and permanently delete most of the rows, a one time rebuild can be useful to hand back the space. This is a treadmill of unlikely benefit to do periodically unless you really have some cycle of buildup, purge. d) If you have stopped updating some table AT ALL, rebuilding an index as densely as possible one time might be useful. e) It is hopeless to try to list all the special cases. If an index is much much larger than you think it should be it is worth looking at, but only worth rebuilding under certain specific conditions that are difficult to enumerate in an email.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Maureen English
Sent: Tuesday, June 17, 2014 2:17 PM
To: oracle-l_at_freelists.org; BORACLE_at_ELLUCIAN.COM Subject: (Update): My worst nightmare - ORA-8103

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
  --
  http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 17 2014 - 21:09:07 CEST