Scribd
Upload
18 views1 page

Block Curruption

The document discusses the first steps to address block corruption in a database, which includes validating the database, checking the dba_database_block_corruption view, and using RMAN to recover corrupted blocks. It also provides SQL queries to identify the specific corrupted blocks and their attributes.

Uploaded by

sandeep
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views1 page

Block Curruption

The document discusses the first steps to address block corruption in a database, which includes validating the database, checking the dba_database_block_corruption view, and using RMAN to recover corrupted blocks. It also provides SQL queries to identify the specific corrupted blocks and their attributes.

Uploaded by

sandeep
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Block Corruption, First steps

Launch ‚validate database;‘


Look into dba_database_block_corruption and try to recover the corrupted blocks:
Rman> validate database;
SQL> select * from v$database_block_corruption;
RMAN> RECOVER CORRUPTION LIST;

Before trying to recover the blocks, it might be useful to look for the block and corruption
type (if these are free blocks no recovery would be possible and needed):

set pagesize 2000


set linesize 280
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
, corruption_type 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
, corruption_type||' 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#;

You might also like