Recover Block Corruption
3 min readWhat is a data block corruption?
Data block corruptions occur whenever data is not in its expected state. The block may have contents that are not internally consistent, or it may have been altered to be unrecognizable as part of the Oracle Database format. Some data block corruptions are relatively minor, affecting only a single block.
Why do data block corruptions occur?
Problems with any or all of the following components can result in data block corruptions: Hard drive Disk controller Operating system Storage device
Detect and monitor data block corruption?
1. Oracle Recovery Manager (RMAN) 2. DBVerify : DBVerify is an external command-line utility for validating online and offline databases and files, including backups. 3. ANALYZE command: You can use the ANALYZE… VALIDATE STRUCTURE construction to verify the data blocks in an object. Once the analysis is complete, check the INVALID_ROWS table to see the corrupted data. 4. DB_BLOCK_CHECKING parameter 5. DBMS_REPAIR
SQL> select header_file, header_block from dba_segments where segment_name='EMPLOYEES';
HEADER_FILE HEADER_BLOCK
----------- ------------
3 99198
SQL> select name from v$datafile where file#=3;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/sysaux01.dbf
[oracle@localhost]$ dd of=/u01/app/oracle/oradata/orcl/sysaux01.dbf bs=8192 conv=notrunc seek=99198 << EOF
> #CORRUPTED_BLOCKS#
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.00115335 s, 16.5 kB/s
[oracle@localhost ]$ dd of=/u01/app/oracle/oradata/orcl/sysaux01.dbf bs=8192 conv=notrunc seek=99199 <<EOF
> #CORRUPTED_BLOCKS#
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.000480514 s, 39.5 kB/s
SQL> select * from hr.employees;
select * from hr.employees
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 99198)
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
RMAN> validate datafile 3;
Starting validate at 10-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:06
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 FAILED 0 14090 65331 1920724
File Name: /u01/app/oracle/oradata/orcl/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6147
Index 0 5428
Other 2 39615
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5531.trc for details
Finished validate at 10-DEC-21
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
3 99198 2 0 CORRUPT 0
RMAN> RECOVER CORRUPTION LIST;
Starting recover at 10-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/fast_recovery_area/orcl/ORCL/datafile/o1_mf_sysaux_f3ckcry5_.dbf
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl/ORCL/archivelog/2021_12_10/o1_mf_1_18_f3cv07g7_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2021_12_10/o1_mf_annnn_TAG20171217T115308_f3ctk53s_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2021_12_10/o1_mf_annnn_TAG20171217T115308_f3ctk53s_.bkp tag=TAG20171217T115308
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/ORCL/archivelog/2021_12_10/o1_mf_1_16_f3d1soxo_.arc RECID=72 STAMP=962978230
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/ORCL/archivelog/2021_12_10/o1_mf_1_17_f3d1soyt_.arc RECID=71 STAMP=962978230
media recovery complete, elapsed time: 00:00:03
Finished recover at 2021_12_10
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
no rows selected
