Re: IOT table index got corrupted with no backup of table
Date: Thu, 10 Sep 2009 06:30:43 -0700 (PDT)
Message-ID: <926b2513-63f0-4ffa-aca2-a0c74737cd60_at_q7g2000yqi.googlegroups.com>
On Sep 10, 1:58 am, "aman.oracle.dba" <aman.oracle...._at_gmail.com> wrote:
> Hi All,
>
> IOT table in our database env. got corrupted in last december. Now
> some people want to use old archive data from this table. But there is
> corruption in its index (Physical - 17, Logical - 180, no. of
> datafiles - 60, size of index - 111GB). We don't have this old backup,
> so we can't recover it from that.
>
> We have already tested DBMS_REPAIR, BLOCKRECOVER - RMAN, exp/imp but
> nothing it working...
>
> Can any one please give suggest.
> is there any way we can recover uncorrupted data from this table ?
>
> Thanks
I expect more information is necessary. For one what is the full version of Oracle? What results did you get when you tried dbms_repair?
If this was a heap instead of an IOT I would tell you to read the PK and for each PK try to fetch the table row using an exception block to capture corruption errors. Any way you can build the list of PK perhaps using secondary indexes and FK relationships?
I would write some pl/sql and try to retrieve all the data till I got an error then I would try to skip over the bad area and start retrieving again. If the corruption is in a leaf block where the data is stored and not in a branch block that points to the index structure then you should be able to do this. If the corruption is in a branch block then the data loss would be larger since there could be whole sections of the index you cannot get to.
You did not mention partitioning. A 111G table would seem to be a canidate for being a partitioned table.
HTH -- Mark D Powell -- Received on Thu Sep 10 2009 - 08:30:43 CDT