Data block corruption in particular datafile

Find the Data block corruption details:

SELECT name FROM v$datafile WHERE file#=34;

SELECT ts# "TSN" FROM v$datafile WHERE file#=34;

SELECT tablespace_name FROM dba_data_files WHERE file_id=34--APPS_TS_TX_DATA

SELECT block_size FROM dba_tablespaces 
WHERE tablespace_name = 
(SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN);---8192

SELECT tablespace_name, file_id "AFN", relative_fno "RFN" FROM dba_data_files;


SELECT *
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;

SELECT * 
FROM dba_free_space 
WHERE file_id = 34 
and 682478 between block_id AND block_id + blocks - 1; 

Solution:

RMAN> run {blockrecover datafile 34 block 682478;}
RMAN>  backup check logical validate datafile 34;
analyze table PA.PA_COST_XXX_ALL validate structure online; 

If table has analyzed your in good shape.

If it is not analyzed and it shows again datafile has been corrupted.

 SQL> alter table PA.PA_COST_XXXX_LINES_ALL move;  
Table altered.  

  SELECT * FROM dba_free_space WHERE file_id = 34 and 682478 between block_id AND block_id + blocks - 1; 

Return any rows it means block has been changed.rebuild the indexes on this table.

select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected.

Now there is no db blocks on Database.

Note: 28814.1 - Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g

Note: 403747.1 - FAQ: Physical Corruption
Note: 68117.1 - Introduction to the Corruption Category

Note: 840978.1 - Physical and Logical Block Corruptions. All you wanted to know about it

 

Thanks 

Srini