How To Detect and Repair Block Corruption in Oracle

dbtut

6 years ago

In this article, we will see how to fix block corruption when there is a block corruption on a data file.

If Block Corruption is on the index, you can solve the problem by dropping and recreating the index.

In some cases, block corruption can also be on free pages.

Detect Corrupt Blocks

Block corruption in Oracle databases is not a fearful dream. Block corruption can be easily corrected in databases that are in Archivelog mode and that are regularly backed up by rman.

Connect to the database which has block corruption and detect the block corruption with the following script.

if you dont know to connect to Oracle, you may want to read the below articles.

“How To Connect To Oracle With Toad“,

“How To Connect To Oracle With sqlplus“,

select * from v$database_block_corruption;

1	select * from v$database_block_corruption;

If we run the query, the screen will appear as below.

For RAC Databases;

select * from gv$database_block_corruption;

1	select * from gv$database_block_corruption;

Repair Block Corruption Using RMAN

Then connect to RMAN and repair block corruption with the following script.

To connect to RMAN you may want to read the article titled “How To Connect To RMAN“.

Write the file number returned from the above script insted of “x” in the below script.

Write the block number returned from the above script instead of “y” in the below script..

blockrecover datafile x block y;
backup validate check logical datafile x;

1 2	blockrecover datafile x block y; backup validate check logical datafile x;

Find Which Object is Corrupted

You can use the following query to determine which objects are on the corrupted blocks.

SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id =x and y 
between block_id AND block_id + blocks - 1;

1 2	SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id =x and y between block_id AND block_id + blocks - 1;

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#;

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#;

When you run the query, it will tell you where the block corruption is, as shown below.

Repair All Block Corruptions

To repair all the corruptions in this list, simply run the following command with RMAN.

RMAN> blockrecover corruption list;

1	RMAN> blockrecover corruption list;

Repair a particular block

RMAN> blockrecover datafile 151 block 3454464;

1	RMAN> blockrecover datafile 151 block 3454464;

You can find the datafile and block number from v$database_block_corruptionas I mentioned above.

Finally you need to check the corresponding datafiles. With the following command, you can scan the entire datafile and see if there are problems.

RMAN> backup validate check logical datafile 151;

Starting backup at 31-MAR-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00151 name=+DATA/orcl/datafile/data_ts.1491.871430401
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
151 OK 0 153363 3932160 629800241304
File Name: +DATA/orcl/datafile/data_ts.1491.871430401
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 3718487 
Index 0 3829 
Other 0 56481

Finished backup at 31-MAR-16

RMAN> backup validate check logical datafile 151;

Starting backup at 31-MAR-16

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00151 name=+DATA/orcl/datafile/data_ts.1491.871430401

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

151 OK 0 153363 3932160 629800241304

File Name: +DATA/orcl/datafile/data_ts.1491.871430401

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 3718487

Index 0 3829

Other 0 56481

Finished backup at 31-MAR-16

If the Status OK and Blocks Failing values are zero as in the above output, we can say that the problem is fixed.

If you want to check the datafile offline, you can also use the DB verify tool. The sample command is as follows.

dbv file=+DATA/orcl/datafile/users.411.795847253 blocksize=8192 userid=sys/*****

1	dbv file=+DATA/orcl/datafile/users.411.795847253 blocksize=8192 userid=sys/*****