R12.2 Apps DBA. Powered by Blogger.

Data block corruption in particular datafile

No comments :
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;
Sol:
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

No comments :

Post a Comment

Note: only a member of this blog may post a comment.