Dumps

Database Blocks

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

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

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;

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

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;