The syntax used for dumping database blocks changed when Oracle8 was introduced
Oracle8 and above |
Oracle7 and below |
In Oracle8 and above, blocks are uniquely identified by an absolute file number and a block number. The syntax of the ALTER SYSTEM command has been extended to include block dumps
To dump a block:
ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK block_number;
To dump a range of blocks:
ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK MIN minimum_block_number BLOCK MAX maximum_block_number;
The DATAFILE clause can specify an absolute file number of a datafile name. If the DATAFILE clause specifies a datafile name, the blocks can also be dumped from a closed database e.g.
ALTER SYSTEM DUMP DATAFILE 'file_name' BLOCK block_number;
Normally a symbolic block dump is output. However, this may not be possible if the block has become corrupt. It is possible to output the block dump in hexadecimal.
To dump a block in hexadecimal, enable event 10289:
ALTER SESSION SET EVENTS '10289 trace name context forever, level 1';
Dump the block(s) using one of the above commands and then disable 10289 again using:
ALTER SESSION SET EVENTS '10289 trace name context off';
In Oracle 12c ASSM bitmap blocks cannot be dumped by default because the block is considered to be encrypted.
A workaround is to set:
ALTER SESSION SET "_sga_clear_dump" = TRUE;
The X$KSPPI description for this parameter is:
Allow dumping encrypted blocks in clear for debugging.
On Unix systems blocks can also be dumped using the od utility.
dd bs=8k if=filename skip=200 count=4 | od -x
where:
As blocks are written back to the datafiles asynchronously by DBWR, it is possible that changed blocks have not been written back to the disk when they are dumped using operating system utilities.
The probability that a block has been written back to disk can be increased by performing a checkpoint using:
ALTER SYSTEM CHECKPOINT;
or a logfile switch using:
ALTER SYSTEM SWITCH LOGFILE;
In Oracle7, blocks are identified by a file number and a block number. These must be converted into a data block address. The block can then be dumped using the ALTER SESSION command.
COLUMN decimalDBA new_value decimalDBA SELECT dbms_utility.make_data_block_address (&file,&block) decimalDBA FROM dual; ALTER SESSION SET EVENTS 'immediate trace name blockdump level &decimalDBA';