Corrupted Free-Space in Oracle Database
Several weeks ago we had major corruption across the file-system on our production database server. Everything was affected--Oracle home files, database files, logs...everything.
After I restored the database, I used RMAN to search for corruption in the instance.
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
This command will validate all database and archive log files that would be backed up, checking for physical and logical corruption. No backup is actually done with the command.
When corruption is found, it can be seen in V$DATABASE_BLOCK_CORRUPTION. My output was the following:
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------------------------15 2061570 126 9709077348 NOLOGGING15 2061442 126 9709084124 NOLOGGING
Corruption was found, but the corruption type wasn't shown. I also couldn't find the objects that were corrupted.
SELECT owner, segment_name, segment_type, partition_name FROM dba_segments WHERE header_file =16
2 and header_block=1856801;
no rows selected
I opened an SR with Oracle Support on the issue. Oracle gave me this SQL, which showed the corrupted blocks were part of the free space.
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#;
Support also provided this doc, "How to Format Corrupted Block Not Part of Any Segment" (Doc ID 336133.1) for how to cleanup the corrupted free space. I did confirm with Oracle Support though that it was safe to just leave the corrupted free space as is, because it would get written over eventually.
After I restored the database, I used RMAN to search for corruption in the instance.
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
This command will validate all database and archive log files that would be backed up, checking for physical and logical corruption. No backup is actually done with the command.
When corruption is found, it can be seen in V$DATABASE_BLOCK_CORRUPTION. My output was the following:
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ------------------------------------15 2061570 126 9709077348 NOLOGGING15 2061442 126 9709084124 NOLOGGING
Corruption was found, but the corruption type wasn't shown. I also couldn't find the objects that were corrupted.
SELECT owner, segment_name, segment_type, partition_name FROM dba_segments WHERE header_file =16
2 and header_block=1856801;
no rows selected
I opened an SR with Oracle Support on the issue. Oracle gave me this SQL, which showed the corrupted blocks were part of the free space.
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#;
Support also provided this doc, "How to Format Corrupted Block Not Part of Any Segment" (Doc ID 336133.1) for how to cleanup the corrupted free space. I did confirm with Oracle Support though that it was safe to just leave the corrupted free space as is, because it would get written over eventually.
Comments