How to identify all the Corrupted Objects in the Database

ORA-01578: ORACLE data block corrupted (file # 377, block # 4205)
ORA-01110: data file 392: '/u01/app/oracle/db/apps_st/data/a_tx_data01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

For Refer :

&AFN      is  : 392       (from the ORA-1110 portion of the error)

&RFN      is  : 377      (from the "file #" in the ORA-1578)

&BL       is  : 4205  (from the "block #" in the ORA-1578)

&FILENAME is  : '/d01/app/oracle/db/apps_st/data/a_tx_data01.dbf'

Step 1 .The corrupted blocks are listed in the view v$database_block_corruption:

SQL> select * from v$database_block_corruption;

Step 2:  The corrupted blocks are listed with Tablsspace

SQL> select b.tablespace_name, count(BLOCK#) from v$database_block_corruption a, dba_data_files b where a.FILE#=b.FILE_ID group by b.tablespace_name

Step 3: Identify the corrupt segments It will map each block from v$database_block_corruption to either a segment or if the block is free.

SQL> set pagesize 2000

         set linesize 250

     SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

     , greatest(e.block_id, c.block#) corr_start_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

       - greatest(e.block_id, c.block#) + 1 blocks_corrupted

     , null description

  FROM dba_extents e, v$database_block_corruption c

 WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

     , header_block corr_start_block#

     , header_block corr_end_block#

     , 1 blocks_corrupted

     , 'Segment Header' description

  FROM dba_segments s, v$database_block_corruption c

 WHERE s.header_file = c.file#

   AND s.header_block between c.block# and c.block# + c.blocks - 1

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

     , greatest(f.block_id, c.block#) corr_start_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

       - greatest(f.block_id, c.block#) + 1 blocks_corrupted

     , 'Free Block' description

  FROM dba_free_space f, v$database_block_corruption c

 WHERE f.file_id = c.file#

   AND f.block_id <= c.block# + c.blocks - 1

   AND f.block_id + f.blocks - 1 >= c.block#

order by file#, corr_start_block#;

An output example is:

OWNER SEGMENT_TYPE       SEGMENT_NAME PARTITION_ FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----- ------------------ ------------ ---------- ----- ----------------- --------------- ---------------- -------------
SCOTT TABLE              EMP                         6                10              10                1
SCOTT TABLE PARTITION    ORDER        ORDER_JAN      6                26              28                3
                                                     6                29              29                1 Free Block

Step 4:To find the Corrupt Tablespace Name and details ..

 SQL> select b.tablespace_name,count(BLOCK#) from v$database_block_corruption a, dba_data_files b where a.FILE#=b.FILE_ID group by b.tablespace_name;

An output example is:

TABLESPACE_NAME                     FILE#     BLOCK#     BLOCKS CORRUPTIO

------------------------------ ---------- ---------- ---------- ---------

APPS_TS_TX_DATA                        06     57997          3 CORRUPT

APPS_TS_TX_DATA                        31    16067          1 CORRUPT

APPS_TS_TX_DATA                        34    173076          4 CORRUPT

APPS_TS_TX_DATA                       392      620         13 CORRUPT

APPS_TS_NOLOGGING                      401     2090         5 CORRUPT

Step 5: For each file# need to fire below query and find the objects that are corrupt & make a note of them. (Replace 06 with file number)

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

     , greatest(e.block_id, c.block#) corr_start_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)

       - greatest(e.block_id, c.block#) + 1 blocks_corrupted

     , null description

  FROM dba_extents e, v$database_block_corruption c

WHERE e.file_id = c.file#

   AND e.block_id <= c.block# + c.blocks - 1

   AND e.block_id + e.blocks - 1 >= c.block#

   and c.file#=06

UNION

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#

     , header_block corr_start_block#

     , header_block corr_end_block#

     , 1 blocks_corrupted

     , 'Segment Header' description

  FROM dba_segments s, v$database_block_corruption c

WHERE s.header_file = c.file#

   AND s.header_block between c.block# and c.block# + c.blocks - 1

   and c.file#=06

UNION

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#

     , greatest(f.block_id, c.block#) corr_start_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#

     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)

       - greatest(f.block_id, c.block#) + 1 blocks_corrupted

     , 'Free Block' description

  FROM dba_free_space f, v$database_block_corruption c

WHERE f.file_id = c.file#

   AND f.block_id <= c.block# + c.blocks - 1

   AND f.block_id + f.blocks - 1 >= c.block#

   and c.file#=06

order by file#, corr_start_block#;

Solution : 
Import the identified tables & rebuilt the indexes:

Drop the Tablespace ,Import  from Source to Target:

Source :
 expdp system/manager@PRD tablespaces=APPS_TS_TX_DATA directory=txdata dumpfile=appstxdt.dmp logfile=appstxdt.log

Target :
impdp system/manager@DEV  tablespaces=APPS_TS_TX_DATA directory=txdata dumpfile=appstxdt.dmp logfile=appstxdt.log

Step 6:   Retest the issue

To find the details …. Please check bellow :

1) An absolute FILE NUMBER (&AFN) of the file containing the corrupt block

SQL> SELECT name FROM v$datafile WHERE file#=&AFN;

Note : The file name of the file containing the corrupt block (Ex : file # 377)

                The BLOCK NUMBER of the corrupt block in that file     (Ex : block # 4205) 

2). The tablespace number and name containing the affected block.

SQL>   SELECT ts# "TSN" FROM v$datafile WHERE file#=&AFN;

SQL> SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN;

3). The block size of the tablespace where the corruption lies

SQL> SELECT block_size FROM dba_tablespaces

     WHERE tablespace_name =

       (SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN);

4).Identify the Corrupt Object

    The database must be open in order to use this query:

    The following query will tell you the object TYPE , OWNER and NAME of a segment

SQL> SELECT tablespace_name, segment_type, owner, segment_name

          FROM dba_extents   WHERE file_id = &AFN

           and &BL between block_id AND block_id + blocks – 1;

SQL> select  *  from dba_extents where file_id = &AFN

and &BL between block_id AND block_id + blocks - 1;

Note : If the block is in a TEMPFILE the above query will return no data

5). If the above query does not return rows, it can also be that the corrupted block is a segment header

  in a Locally Managed Tablespace (LMT).  When the corrupted block is a segment 

  header block in a LMT,  the above query produces a corruption message in the alert.log 

  but the query does not not fail.  In that case use this query:

SQL> SELECT owner, segment_name, segment_type, partition_name 

         FROM dba_segments WHERE header_file = &AFN and header_block = &BL;

Reference Note 819533.1(case 5)

6). If the block belongs to a free extent it should appear in DBA_FREE_SPACE:       

SQL> select * from  dba_free_space

where file_id = &AFN and &BL between block_id AND block_id + blocks - 1;

7). If the segment is a CLUSTER determine which tables it contains.

SQL> SELECT owner, table_name FROM dba_tables 

     WHERE owner='&OWNER' AND cluster_name='&SEGMENT_NAME';

8). If the segment is an INDEX PARTITION 

SQL> SELECT partition_name FROM dba_extents    WHERE file_id = &AFN

     AND &BL BETWEEN block_id AND block_id + blocks – 1;

Note : then continue below as if the segment was an INDEX segment.

                    Options:Index partitions can be rebuilt using:

EX: ALTER INDEX xxx REBUILD PARTITION ppp; 

INDEX :

                    - If the segment is an INDEX then if the OWNER is "SYS" contact 

                      Oracle support with all details.

 Note: For a non-dictionary INDEX or INDEX PARTITIONs find out which table 

                      the INDEX is on:

                      Eg:SQL>SELECT table_owner, table_name FROM dba_indexes 

                                      WHERE owner='&OWNER' AND index_name='&SEGMENT_NAME';

Note:  and determine if the index supports a CONSTRAINT:

                      Eg:SQL>SELECT owner, constraint_name, constraint_type, table_name 

                                           FROM dba_constraints WHERE owner='&TABLE_OWNER' 

                                           AND constraint_name='&INDEX_NAME';

Note: Possible values for CONSTRAINT_TYPE are:

                                          P                  The index supports a primary key constraint.

                                         U                 The index supports a unique constraint.

 Note :  If the INDEX supports a PRIMARY KEY constraint (type "P") then

               check if the primary key is referenced by any foreign key constraints:

                 Eg: 

                SQL> SELECT owner, constraint_name, constraint_type, table_name 

                                 FROM dba_constraints WHERE r_owner='&TABLE_OWNER' 

                                   AND r_constraint_name='&INDEX_NAME';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ID 28814.,472231.1,336133.1,819533.1 & 369076.1

+++++++++++++++++++++++++++++++++++++++++++++++++++++

Step 1:

SQL> select * from v$database_block_corruption; 

SQL> select * from v$datafile where file#='&file#no'; 

SQL> select distinct file# from  v$database_block_corruption;

SQL> select distinct  file#,CORRUPTION_TYPE from V_$DATABASE_BLOCK_CORRUPTION order by file#;

SQL> select file_id,file_name from dba_data_files where file_id in (no1,n02,no3,no4);

SQL> select owner, segment_name,tablespace_name from dba_extents where FILE_ID=&id and <Block> between BLOCK_ID and BLOCK_ID+BLOCKS;

Step 2: Check whether block is part of any object

Query dba_extents and cross check the block doesnot belong to any object. 

SQL> select segment_name,segment_type, owner from dba_extents

      where file_id = <Absolute filenumber> and <corrupted block number>

between block_id and block_id + blocks -1;

Note : If it doesn't belong to an object, double check if itdoes exists in dba_free_space to check if the block belongs to file space usage bitmap.

SQL> Select * from dba_free_space where file_id= <Absolute file number>

    and <corrupted block number> between block_id and block_id + blocks -1;

Step 3:  We cannot run a query on dba_extents to find out what the object is on the standby, so we ran the following query on the Primary to find the corrupted block: 



SQL> SELECT tablespace_name, segment_type, owner, segment_name 

FROM dba_extents WHERE file_id = &no-start and &no-end between block_id AND block_id + blocks - 1;

Step 4: The repair data block of table in a given tablespace:

SQL> BEGIN 

DBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => '&tablespace_name');

END;

/ 

Step 5: Identify corrupted blocks for schema.object:

SQL> set serveroutput on

SQL> DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => '&schema_name',

OBJECT_NAME => '&object_name',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

corrupt_count => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

Step 6:  Optionally display any corrupted block identified by check_object:

SQL> select BLOCK_ID,CORRUPT_TYPE, CORRUPT_DESCRIPTION from REPAIR_TABLE;

Step 7: Mark the identified blocks as corrupted

SQL> DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => '&schema_name',

OBJECT_NAME=> '&object_name',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

/

Step 8 : To find the corruption objects 

SQL> select count(*) from v$database_block_corruption;

SQL> select 'SELECT e.file_id,e.block_id,e.owner,e.segment_name, 

e.segment_type FROM dba_extents e WHERE 

file_id='|| file#||' and '||
block#||' BETWEEN block_id AND block_id + blocks - 1; '

from v$database_block_corruption order by file#;

Note :Allow future DML statements to skip the corrupted blocks 

Note : If it is needed to stop skipping corrupt blocks in
the object -- ID 556733.1:

SQL> BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => '&schema_name',

OBJECT_NAME => '&object_name',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

/

Note : if not required :

SQL> execute
dbms_backup_restore.resetCfileSection(file#); /** clear
v$backup_corruption    

To verify :

SQL>  SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE,
MARKED_CORRUPT,CORRUPT_DESCRIPTION,EPAIR_DESCRIPTION FROM REPAIR_TABLE;

SQL> select * from  V$DATABASE_BLOCK_CORRUPTION ;

RMAN Verify 

To identify both Physical and Logical Block Corruptions use
the "CHECK LOGICAL" option. The next command checks the complete
database for both corruptions without actually doing a backup:

$ rman target /

RMAN>  backup check logical validate database;

Note : The next command checks the complete database for both
corruptions in a backup:

$ rman target /

RMAN> backup check logical
database

DBVerify - Identify Datafile Block Corruptions

DBVERIFY identify Physical and Logical Intra Block

Corruptions by default. Dbverify cannot be run for the whole database in a
single command. It does not need a database connection either:

dbv file=<datafile name>
blocksize=<datafile Block size>

SQL> connect internal

 Connected.

SQL> show parameter db_block_size

SQL>select file#,name,bytes/2048 from v$datafile;

SQL> EXIT

$dbv file=/u02/oradata/R815/oradata/R815/users01.dbf blocksize=2048 logfile=users01_dbv.log feedback=100 

++++++++++++++++++++++++++++++++++++++++++++++