Skip to end of metadata
Go to start of metadata

Overview

List some SQL Statement to exactly determine the segments of the corrupted block(s). 

Identify the object that belongs to corrupt block

 

For all selects on dba_extents, v$datafile, when you dump blocks or during brrestore of individual files, you must always specify the absolute file number.
It is possible to map between absolute file numbers, relative file numbers and file names using the following statement:

SQL> select file#, rfile#, name from v$datafile;


If you have only file names and block numbers (=page numbers), execute the following SQL queries in sqlplus:

  • SQL> select file# from v$datafile where name = '<Filename>';
     
    => file number of the file that contains the corrupt block

 

  • SQL> select segment_name, partition_name, segment_type, block_id, blocks
    from
    dba_extents
    where
    (<corrupted block> between block_id and (block_id + blocks - 1))
    and
    file_id = <Filenummer from the first statement>
    and
    rownum < 2;
     
    => Object name, object type

In case RMAN check was executed, use below series of SQL commands to identify the object(s)

SQL> set linesize 200
SQL> set pagesize 100
SQL> col owner for a20
SQL> col segment_name for a20
SQL> col partition_name for a20
SQL> col file_name for a50

SQL> select ext.owner, ext.segment_name, ext.partition_name, ext.segment_type, ext.block_id, ext.blocks, dbc.corruption_type, fil.file#, fil.name as file_name
from 
dba_extents ext, 
v$datafile fil, 
v$database_block_corruption dbc
where 
(dbc.block# between ext.block_id
and
(ext.block_id + ext.blocks - 1)) 
and
dbc.file# = ext.file_id 
and
dbc.file# = fil.file#
order by ext.file_id;

Below list shows tables that can be recreated empty and filled up automatically by SAP during the next System startup

Note: if only one of the tables is affected, all tables of the relevant row must nevertheless be emptied for consistency reasons!

  • D010L, D010Q, D010Y, D010LINF
  • D020L, D020LINF, D021L, D021LINF
  • D346T, D342L
  • DDFTX
  • D344L
  • REPOLOAD
  • DYNPTXTLD, DYNPLOAD
  • DDLOG

Navigating to other Chapters

General Information

Detecting Corrupted Blocks

Consistency Check with 'ANALYZE' command

Consistency Check with DBV

Consistency Check with RMAN

Consistency Check with Data Pump

Checks found corrupted blocks. What to do now