Re: Read a corrupted block
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Thu, 29 Jul 2010 22:51:49 -0500
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@xxxxxxxxx
> 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@xxxxxxxxxxx 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.
>>
>>
>>
> -- //www.freelists.org/webpage/oracle-l
Other related posts:
