Database Block Dump

The syntax used for dumping database blocks changed when Oracle8 was introduced

Oracle 8 and above

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;

Hexadecimal Block Dumps

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';

ASSM Bitmap Blocks

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.

Dumping blocks using the od utility

On Unix systems blocks can also be dumped using the od utility.

dd bs=8k if=filename skip=200 count=4 | od -x

where:

bs is the Oracle block size e.g. 8k
if is the datafile name
skip is the number of blocks to skip from the start of the file
count is the number of blocks to dump

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;

Oracle 7 and below

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';