Oracle Database Discussions

Today in my daily routine checks i found the followiing error in alert log file

Data in bad block:
type: 27 format: 2 rdba: 0x0040418a
last change scn: 0x0937.2d63eb31 seq: 0x1 flg: 0x0c
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xeb311b01
check value in block header: 0x4c2d
computed block checksum: 0x0
Reread of blocknum=16778, file=/u01/app/oracle/oradata/apexprod/oaut_qa01.dbf. found same corrupt data
Reread of blocknum=16778, file=/u01/app/oracle/oradata/apexprod/oaut_qa01.dbf. found same corrupt data
Reread of blocknum=16778, file=/u01/app/oracle/oradata/apexprod/oaut_qa01.dbf. found same corrupt data
Reread of blocknum=16778, file=/u01/app/oracle/oradata/apexprod/oaut_qa01.dbf. found same corrupt data
Reread of blocknum=16778, file=/u01/app/oracle/oradata/apexprod/oaut_qa01.dbf. found same corrupt data

The database is no archive log mode .when i queried select * from v$DATABASE_BLOCK_CORRUPTION

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
13 16771 8 0 CORRUPT
13 16259 7 0 CORRUPT
13 14083 111 0 CORRUPT
13 13955 111 0 CORRUPT
13 13827 6 0 CORRUPT
13 13699 43 0 CORRUPT
13 13571 1 0 CORRUPT
13 13315 1 0 CORRUPT
13 13187 111 0 CORRUPT
13 12931 7 0 CORRUPT
13 12803 1 0 CORRUPT

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
13 12675 6 0 CORRUPT
13 12551 1 0 CORRUPT
13 12547 1 0 CORRUPT
13 12419 8 0 CORRUPT
13 12291 1 0 CORRUPT
13 12163 5 0 CORRUPT

How can i resolve this issue.

When i quried to find the segment or extent is corrupted i am not able to find this by using the follwoing queries
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#;

In the above above i am not getting the segment type or name but able to see the block which was corrputed.

what is right way to proceed from here..


Thanks
Giridhar