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: Extract data from index

Re: Extract data from index

From: <fitzjarrell_at_cox.net>
Date: 14 Jan 2005 07:59:43 -0800
Message-ID: <1105718383.255101.162070@z14g2000cwz.googlegroups.com>

Peter Mubaiter wrote:
> > All in all this situation sounds too ludicrous to be true. So I
would,
> > if I were you, reveal the true nature and goal of this exercise.
>
> To explain what happened:
>
> A colleague issued a
>
> alter tablespace USERS add datafile '/oradata/users02.dbf' size 2000M
reuse;
>
> Unfortunately the file was already in use. He made two mistakes, he
used
> "reuse" and he made not sure to use the next number in naming the new

> datafile.
> Of course there was now backup which could have been used.
> What we did then was recover the data from all indexed columns of the

> affected tables by selecting data using the index like
>
> select /*+ index (c INDEX_NAME)*/ indexed_column
> from affected_table where indexed_column is not null;
>
> We could restore many columns from most tables. One index has a
> corrupted block, that is why I asked this question.
>
> Thanks anyway,
>
> Peter

My apologies for the apparent rudness at the end of my post; your original post did not present the full situation in which you find yourselr, thus it seemed implausible for such a scenario to occur.

Unfortunately with 8.0.5 you have no other choice but to take what you can get from the index and move on. You have no provision for marking and skipping the corrupted block (as I mentioned in my prior post); such queries terminate with an ORA-01578 listing the file and block where the corruption was found (and, of course, you are already aware of this).

If you do have a recent export of this data (prior to your colleague's misstep) you might be able to create anothing instance and load this data there, thus making as complete a recovery of the data as possible. Yes, you'd still be missing any data entered subsequent to the export (let us hope that would not be an excessive number of rows) but you would have access to all of the data in those tables and indexes, hopefully without the block corruption.

It is still not entirely clear if this is data corruption ( a bad read/write from memory to the table/index) or if it is a bad disk block. Of course, it really doesn't matter at this point with the release of Oracle you are using. It may be possible to migrate this database to 8.1.6 or 8.1.7 (both still out of support but more recent than your current installation) and then utilise the DBMS_REPAIR package. Not knowing the extent of what Oracle does to migrate a database at the datafile level I really can't say if this will succeed or not. It may be worth investigating, as it would provide you with a method of repairing or skipping the corrupt blocks.

David Fitzjarrell Received on Fri Jan 14 2005 - 09:59:43 CST

Original text of this message

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