Oraclue

Oracle internals, debugging and undocumented features

How to dump corrupted Oracle database blocks?

Oracle blocks are uniquely identified by an absolute file number and a block number

Command to dump one Oracle block

alter system dump datafile <file_number> block <block_id>

or to dump few blocks

alter system dump datafile <file_number> block min <first_block>  block max <last_block>

But if blocks are corrupted this may not be possible.

Solutions:

1) Dump blocks in hexadecimal

2) Unix  dd command

To dump blocks in hexadecimal we can use Oracle event 10289.

e.g.

First set event 10289 to enable dump in hexadecimal and then peform a dump using regular dump command and finally turn off event.

SQL>  alter session set events ‘10289 trace name context forever 1′;

Session altered.

SQL> alter system dump datafile 1 block 401;

SQL>  alter session set events ‘10289 trace name context off’;

Session altered.

or  if you need to dump only corrupted blocks but skip good blocks

SQL> alter session set events ‘10289 trace name context after 6 times, lifetime 2’

will dump only 2 corrupted blocks.

Event 10289 dump cache header and if header itself is corrupted then your second solution would be to use

Unix dd command.

 dd bs=8k if=file_name skip=100 count=8|hexdump > block_dump.txt

Advertisements

2 responses to “How to dump corrupted Oracle database blocks?

  1. Taral Desai April 9, 2009 at 5:48 pm

    Hi Miladin,

    I have few questions

    1. How to determine block type. Like is this data(table) or index or redo block.

    2. And from that block dump how to identify object.

    Lets say i had corruption in file 56, 56789 (block)

    I can blockdump this block but how to answer my 1 and 2 point from this.

    Can you please explain.

  2. oraclue April 13, 2009 at 4:48 pm

    Hi Taral,

    I guess you know how to find object based on file and block:

    SELECT segment_name , segment_type , owner , tablespace_name
    FROM sys.dba_extents
    WHERE file_id = &bad_file_id
    AND &bad_block_id BETWEEN block_id and block_id + blocks -1

    but I think what you are asking is to how to read blockdump.

    Check my paper on Oradebug at
    http://www.evdbt.com/Oradebug_Modrakovic.pdf

    and then do few dumps .. for table and for index too.

    Open trace file and search for:

    Seg/Obj ( Seg/Obj Id ) and Typ: 1- Data 2 – Index

    Remember things change with every new Oracle version.

    Look online and you will find description of formated block dump.

    Miladin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: