Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Corrupt db, ora-8102 and more

Re: Corrupt db, ora-8102 and more

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Fri, 03 Oct 2003 22:10:52 GMT
Message-ID: <Mtmfb.41608$Wd7.31218@nwrddc03.gnilink.net>

Coments below:

Anurag

"Peter Laursen" <pl_at_invalid.dk> wrote in message news:3f7dddbf$0$22656$ba624c82_at_nntp02.dk.telia.net...
>
> > not really seen an exact problem ..
>
> My real concern now is the sqls that were giving wrong results without
> any error messages causing applications to fail and that was
> discovered only by accident! I cured that by rebuilding some indexes
> but got an ora-600 after a few minuttes. Say I cure that I would still
> be worried about inconsistensies/corruptions that will surface later.

Couple of things you might want to ask the customer: * Did they try running any unsupported operations like manipulating the SYS tables?
* Can you/they point out which statements are triggering the 600 errors.

Rebuilding the schema might be an option if other attempts fail. You said that export completed successfully.
That is good. If you have corrupt indexes then rebuilding the schema using exp/imp might be an option
(You can test if tht will work for the customer by importing in a test db and validating that it did work).

However, I'm hoping that you can avoid that by finding out the culprits. If the corrupt index was found on a parent table, then some likely culprits might be indexes
on the child tables. Try running the validate structure cascade on the parent and all the child tables at least. And then run it on all the tables in that schema.

And remember: You would need to drop and create the indexes as a safe bet (not just alter index .. rebuild).

>
> > 1. Does ORA-600 state any specific object/table
>
> No

Do you find any relevant info in the trace file? Does it hint at any recognizable problem?

>
> > 2. Run an analyze table tablename validate structure cascade;
> > and see if you get an ORA-600 .. That ORA-600 might point to
> the bad
> > index.
>
> Thanks, I will try that. Just found dbms_utility.analyze_schema. Is
> this as good as analyze table tablename validate structure cascade ?

No not at all. analyze table .. validate structure cascade; will actually not analyze the table (as you understand it). It will validate the structure of the table and cascade will validate the index keys and structure.
Completely different beasts. Don't run the analyze schema .. it might actually fail with a 600 also.

>
> > If you have the luxury ... then you can try running the #2 command
> on
> > most/all tables.
>
> Will running dbms_utility.analyze_schema have the same effect?

No

>
> Peter Laursen
>

Best of Luck. If what I'm assuming is correct, then probably some of your index entries might be pointing
to a non-existent rowid leading to the corruption. That corruption could have percolated into the child table
indexes. Thus a rebuild of parent table's indexes did not help. You might need to drop all the parent table's indexes and all the child tables index and in the end recreate them (including PK's FK's etc). Received on Fri Oct 03 2003 - 17:10:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US