RE: Non-Sql data extraction (recovery tools)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 15 Aug 2013 15:36:19 -0400
Message-ID: <025b01ce99ee$b85cd1c0$29167540$_at_rsiz.com>

So you still have a valid index or indexes on the broken table(s). If you show the plan for the count(*) you'll see that you are not reading the table at all.  

Again, good luck. I think Tim has given you the list of reading material and I've given you the jury-rig partial solutions. A longer recovery from an older physical backup remains possible IF you have the interceding redo logs and know when to stop (probably just before someone issued drop on the table[s] in question and then aborted the database realizing a mistake had been made.) Okay, that's a nasty guess.  

If the data space has not been re-used yet, you can ask Tim about using DUDE to possibly get back the tables that will not repair. Probably you'll want to assess the value of the lost data before you make that call or send that email.  

mwf

From: John Smith [mailto:john40855_at_gmail.com] Sent: Thursday, August 15, 2013 3:26 PM
To: Mark W. Farnham
Cc: Tim Gorman; oracle-l_at_freelists.org Subject: Re: Non-Sql data extraction (recovery tools)  

I admit, it is really strange. I can do a count(*) on the tables, but I cannot select specific rows or export the tables. I set the event I found when I googled the problem and that made no difference, the error is usually object no longer exists, though sometimes it is ora-600 [4555]  

On Thu, Aug 15, 2013 at 2:18 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

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:36:19 CEST