IOT table index got corrupted with no backup of table

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

On Sep 10, 1:58*am, "aman.oracle.dba"
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 --

On Sep 9, 10:58*pm, "aman.oracle.dba"
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

See metalink Note: 61685.1

Figure out the rowid's of the corrupt blocks and skip them.

jg
--
@home.com is bogus.
Give a man a fish you feed him for a day.
Teach a man to fish and you've fed him for life.
Mass-market fish and you have a series of ecological disasters:
http://www3.signonsandiego.com/stori...rsy/?uniontrib