RE: Non-Sql data extraction (recovery tools)
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <john40855@xxxxxxxxx>, <tim@xxxxxxxxx>
- Date: Thu, 15 Aug 2013 15:18:26 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of John Smith
Sent: Thursday, August 15, 2013 2:23 PM
To: tim@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
