RE: Non-Sql data extraction (recovery tools)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 15 Aug 2013 15:18:26 -0400
Message-ID: <023f01ce99ec$386c3430$a9449c90$_at_rsiz.com>

So +1 on both Tim Gorman and DUDE. (Full disclosure: I have NO financial interest in writing that, but if it gets Tim some business we might buy each other drinks when next we meet. Wait. We'll probably do that either way.)

However, since you can at least open the database, you should be able to turn on the events to continue reading after hitting corrupt blocks and at least get all the data that remains fetchable through the Oracle read model.

IF your statistics are reasonably up to date on any tables that are partly broken, then you should be able to access the maximum amount of lost rows.

If there is a valid index containing at least one not null column on any table that is partly broken, then you should be able to get an actual row count to compare with your select allowing corruption.

Depending on the index texture you have, you *may* be able to identify at least the keys and probably the rowids of the partly broken rows, and you may be able to reconstruct a substantial piece (and sometimes all) of the row by querying the indexes by rowid= and the column set for each index for the rows identified as broken.

This, by the way, is a useful argument in favor of separation of indexes from tables at the storage layer. There are many pros and cons to that argument and I am not suggesting this settles it.

I'm NOT aware of a handy dandy utility to do this. It seems to me building it generically would be quite a challenge, but specific one-offs are not rocket science.

This also seems a good advertisement for RMAN and block level recovery.

You *may* be able to resurrect the identified broken rows from an older backup, but you may or may not be able to tell whether those blocks should have changed in the ensuing time. If you have all the interceding archived redo logs you can roll forward an arbitrarily long amount of time from a physical backup (but cannot cross boundaries of an unrecoverable operation on a block nor a resetlogs operation).

Good luck.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of John Smith
Sent: Thursday, August 15, 2013 2:23 PM
To: tim_at_evdbt.com
Cc: oracle-l_at_freelists.org
Subject: Re: Non-Sql data extraction (recovery tools)

Im sorry, I should have made that clear. Im getting errors about corruption in my alert log, and ora-600 errors when trying to do simple selects and exports. Occasionally I get object no longer exists errors also. The last backup is corrupted also.

On Thu, Aug 15, 2013 at 1:16 PM, Tim Gorman <tim_at_evdbt.com> wrote:

> One of two things are going on here...
> 1. Performing recovery from a unopenable Oracle database, when this is
> the only possible method to extract the data for reload
> * Though it isn't free, DUDE (http://www.ora600.be) is the best
> tool available at the lowest cost
> o if the data is truly of value, then the cost should
> considered in light of the value of the data
> o Extracting data directly from the datafiles for any other
> purpose or reason is foolish
> + Bypassing the consistent-read mechanism built into the
> SQL API of Oracle can yield corrupted data 2. Hacking
> into some Oracle datafiles
> * I am not interested in helping someone do this, both for the
> reasons stated above and because it might be irresponsible
> <SNIP

>
>

> --
> http://www.freelists.org/webpage/oracle-l

>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 15 2013 - 21:18:26 CEST