Re: Read a corrupted block
Date: Fri, 30 Jul 2010 07:50:23 +0200
Message-ID: <AANLkTi=b-bMhC=D56R28jcX6j9nB+OuVkjwAjDo3uBh8_at_mail.gmail.com>
One thing that I think was not yet mentioned. If you have indexes on that table, you can select the indexed columns individually by using access plans that don't touch the corrupted table block, and at least get some (or nowadays, where people like to index almost every column, you may actually get most ) of the data.
Stefan
Stefan P Knecht
CEO & Founder
s_at_10046.ch
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch
On Fri, Jul 30, 2010 at 5:51 AM, Kerry Osborne <kerry.osborne_at_enkitec.com>wrote:
> Having just been through a month of recovering from a severe logical
> corruption issue resulting from a SSD that was randomly flipping bits, I'd
> like to add a couple of other ideas in addition to Tim's very thorough
> response.
>
> There are all kinds of corruption: totally unreadable and Oracle knows it,
> to individual rows having something slightly amiss that Oracle may or may
> not notice. If the block is totally hosed, the only option may be to use
> dbms_repair to locate the block(s), mark them, and then tell Oracle to skip
> them. This is actually one of the quickest things to do if dbms_repair can
> recognize the bad block (sometimes it can't). But it doesn't save any of the
> records in the bad block(s). If objects are partitioned by date, the old
> partitions can usually be recreated fairly easily by restoring a copy of the
> database to some point prior to the corruption, exporting/importing the
> partition. If you have non-partitioned tables or corruption in current
> partitions it becomes a little more difficult. If you want to get all the
> records you can (including any salvageable rows in the bad blocks) you may
> be able to get them by selecting the data on a row by row basis using rowid.
> If you really need to access data in a bad block, and you can't select via
> rowid, you can dump the block(s) as documented by Tim. Oracle also has it's
> own block editor if you absolutely have no other option, which would also
> allow you to get back deleted rows, etc... Talk to someone on Oracle
> Support's Corruption Team if you feel you absolutely have to do this. I
> would probably never trust myself to actually edit blocks directly except to
> get to data that I then loaded back into another database. I do know one guy
> that actually wrote his own C program to read data out of the files of a
> completely fried database, including unpacking the numerics, working out the
> columns, the datatypes, etc ... This was a long long time ago in a galaxy
> far far away though (V4 or V5). Also, if you start asking the users, you may
> find that there is a lot of data that is just not that important to them, or
> that can be restored from some other location. They may prefer to loose a
> little or rebuild some themselves, rather than have the whole system down
> while you try to salvage every last piece. Anyway, my main point is that
> there are a number of options depending on the situation, especially if you
> have partitioned historical data. So don't get stuck in a one size fits all
> mind set.
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
>
>
>
>
>
>
> On Jul 29, 2010, at 10:39 AM, Tim Gorman wrote:
>
> Joel,
>
> DUDE probably is overkill for one block, but it depends on the value of the
> data. If you think about the cost of a DBA, an IT manager, a business
> manager, and a business analyst all working 4-8 hours trying to diagnose and
> clean up the mess caused by the data lost in a small number of blocks, then
> the $1000-$5000 spent on DUDE becomes a bargain. Kurt Van Meerbeeck (
> http://www.ora600.be) is the creator of DUDE and Dan Fink and I are the US
> contacts for Kurt.
>
> There is the ALTER SYSTEM DUMP DATAFILE command, with the following
> syntax...
>
> SQL> alter system dump datafile *'<file-name>' | <file#*> block [ *
> <block#>* | block min *<beginning-block#>* block max *<ending-block#>* ];
>
> where...
>
> - "*<file-name>*" is the name of the Oracle datafile
> - "*<file#>*" is the FILE_ID of the Oracle datafile
> - "*<block#>*" is the database block number
> - "*<beginning-block#>*" is the starting block number within the
> datafile
> - "*<ending-block#>*" is the ending block number within the datafile
>
> The output will be dumped to an Oracle trace file within the USER_DUMP_DEST
> directory. Example ALTER SYSTEM DUMP DATAFILE commands include...
>
> - alter system dump datafile
> '+DATA_DG/datafile/PROD/system_1_123456.dbf' block 32;
> - alter system dump datafile 3932 block min 100 block max 200;
>
> The first command dumps one block from the indicated ASM datafile in the
> SYSTEM tablespace. The second command dumps 101 blocks from datafile #3932.
>
> Of course, I long for the grand old days of the DEC VMS "dump" command, but
> on UNIX/Linux you might consider something like the following...
>
> $ dd if=*<file-name>* bs=*<db-block-size>* seek=*<beginning-block#>*count=
> *<#blocks>* | od -cx > *<output-filename>*
>
> where...
>
> - "*<file-name>*" is the name of the Oracle datafile
> - "*<db-block-size>*" is the size of the Oracle database blocks in that
> tablespace
> - "*<beginning-block#>*" is the starting block number within the
> datafile
> - "*<#blocks>*" is the number of blocks you wish to dump
>
> So, the UNIX/Linux "dd" command extracts the block(s) from the datafile and
> the UNIX/Linux "od" (a.k.a. "octal dump") command translates the stream into
> "ASCII character" (i.e. "-c" flag) and hexadecimal (i.e. "-x" flag).
>
> Hope this helps...
>
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
> website => http://www.EvDBT.com/
> email => Tim_at_EvDBT.com
> mobile => +1-303-885-4526
> fax => +1-303-484-3608
> Lost Data? => http://www.ora600.be/ for info about DUDE...
>
>
> On 7/29/2010 8:46 AM, Joel.Patterson_at_crowley.com wrote:
>
> When all hope fails – block recover, dbms_repair, etc. and before you
> enable skipping corrupt blocks, is there some utility that can show me what
> is in the block?
>
>
>
> Something like a hex reader, but since it is only one block, (and we are
> probably going to be getting rid of that data anyway), the method should be
> free. (For instance, I think dude (possible spelling) is something that
> could work, but for one block it would be overkill).
>
>
>
> Even if it is not free, you could let me know. I probably won’t get it,
> but the information would be useful, and the question has been raised.
>
>
>
>
>
>
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 30 2010 - 00:50:23 CDT