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
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.
Comment by Taral Desai — April 9, 2009 @ 5: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
Comment by oraclue — April 13, 2009 @ 4:48 pm