Yesterday’s RMAN backup failed with ORA-19566 error reporting block corruption, here’s the error as reported by RMAN:
channel ORA_DISK_1: starting piece 1 at 20-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 20/06/2011 11:55:11
ORA-19566: exceeded limit of 0 corrupt blocks for file xxxxxxxxxx
The next logical step is to run DB Verify (dbv) utility to find out what blocks are corrupt. Dbv confirmed block corruption as shown below:
Corrupt block relative dba: 0x0346c3cf (file 113, block 229845)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x0346c3cf
:
:
DBVERIFY - Verification complete
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
:
:
Below are the OS and database details:
-bash-3.2$ cat /etc/*-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
-bash-3.2$
-bash-3.2$ uname -r
2.6.18-194.el5
-bash-3.2$
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>
Note that Block 229845 is corrupt in datafile 113. The next step in the process is to identify the object by querying DBA_EXTENTS view
SQL> select segment_name, segment_type, owner
2 from dba_extents
3 where file_id = 113
4 and 229845 between block_id and block_id + blocks -1;
no rows selected
SQL>
Apparently, this block is not associated with any of the segments in the database. The next question to ask is where this corrupt block lies in the data file. Is it in between the allocated blocks after the HWM?
SQL> select max(block_id) from dba_extents where file_id = 113;
MAX(BLOCK_ID)
-------------
182520
SQL>
So, the corrupt block is beyond the maximum allocated block. Think of the data file as shown below (blue=used data, white=empty blocks, red=corrupt block).
The easiest solution is to shrink the datafile below the corrupt block, this way we drop the corrupt block off the datafile.
Identify the datafile size:
SQL> select bytes/1024/1024 size_mb from dba_data_files where file_id = 113;
SIZE_MB
----------
2048
Identify the location of the maximum block in the datafile by multiplying it with the blocksize (8K):
SQL> select max(BLOCK_ID) * 8/1024 size_mb from dba_extents where file_id = 113;
SIZE_MB
----------
1425.9375
SQL>
Also, find out the location of the corrupt block in the datafile:
SQL> select 229845 * 8/1024 size_mb from dual;
SIZE_MB
----------
1795.66406
SQL>
Resize the datafile beyond the maximum allocated data block and below the corrupt block:
SQL> alter database datafile 113 resize 1450m;
Database altered.
SQL>
After resizing the datafile run DBV utility to check for corrupt blocks:
DBVERIFY - Verification complete
Total Pages Examined : 188160
Total Pages Processed (Data) : 62277
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 71630
Total Pages Failing (Index): 0
Total Pages Processed (Lob) : 9337
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 21867
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 23049
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)
There are no corrupt blocks in the datafile. Happy ending.
But it wouldn’t have been this easy if the corrupt block were somewhere in between the allocated blocks. This solution will not be helpful in that case. Refer to following note from Oracle Support to address this issue:
How to Format Corrupted Block Not Part of Any Segment [ID 336133.1]
The easiest solution is to shrink the datafile below the corrupt block, this way we drop the corrupt block off the datafile.
Identify the datafile size:
