Oracle internals, debugging and undocumented features
How to dump corrupted Oracle database blocks?
November 6, 2008Posted by on
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.
1) Dump blocks in hexadecimal
2) Unix dd command
To dump blocks in hexadecimal we can use Oracle event 10289.
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′;
SQL> alter system dump datafile 1 block 401;
SQL> alter session set events ‘10289 trace name context off’;
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