Recovering Corrupted Blocks
Blocks corruption is very rare in Oracle DBMS, but when it occurs it is very dangerous and can result in DB crash or lost of blocks or datafile, but if you have active complete backup and archive logs, it can be easily recovered/repaired.
In this blog, i'll test by corrupting one of my TEST db datafile block and than will try to repair that block online without impacting the DB.
Let's have a fun:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB11G READ WRITE
- We can check, if there is any corrupted block currently in DB:
SQL> select * from v$database_block_corruption;
no rows selected
SQL>
- Now i'll corrupt one of datafiles block. i.e
we will check the datafile location by:
SQL> select file_name,file_id from dba_data_files;
FILE_NAME FILE_ID
------------------- ----------
/u01/app/oracle/oradata/DB11G/users01.dbf 4
/u01/app/oracle/oradata/DB11G/undotbs01.dbf 3
/u01/app/oracle/oradata/DB11G/sysaux01.dbf 2
Now we will corrupt the datafile by adding below line
- I'll run below line at OS level to corrupt an example datafile
dd ibs=8192 seek=100 count=110 if=/dev/zero of=example01.dbf conv=notrunc
i.e
[oracle@linuxtest ~]$ cd /u01/app/oracle/oradata/DB11G/
[oracle@linuxtest DB11G]$
[oracle@linuxtest DB11G]$ pwd
/u01/app/oracle/oradata/DB11G
[oracle@linuxtest DB11G]$ ls -ltr
total 2893212
-rw-r----- 1 oracle oinstall 943726592 Apr 4 02:54 system01.dbf~
-rw-r----- 1 oracle oinstall 52429312 Apr 10 14:38 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Apr 10 14:38 redo01.log
-rw-r----- 1 oracle oinstall 20979712 Apr 10 14:39 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Apr 10 14:49 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Apr 10 14:52 example01.dbf
-rw-r----- 1 oracle oinstall 62922752 Apr 10 15:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 943726592 Apr 10 15:06 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Apr 10 15:06 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Apr 10 15:06 redo03.log
-rw-r----- 1 oracle oinstall 9748480 Apr 10 15:06 control01.ctl
[oracle@linuxtest DB11G]$ dd ibs=8192 seek=100 count=110 if=/dev/zero of=example01.dbf conv=notrunc 110+0 records in
1760+0 records out
901120 bytes (901 kB) copied, 0.00209488 seconds, 430 MB/s
- Let's check the V$database_block_corruption
SQL> select * from v$database_block_corruption;
no rows selected
SQL>
- As there are no row in validated yet, But we have error reported in alert log file. i.e
Corrupt block relative dba: 0x0140006c (file 5, block 108)
Completely zero block found during validation
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Reread of blocknum=108, file=/u01/app/oracle/oradata/DB11G/example01.dbf. found same corrupt data
Hex dump of (file 5, block 109) in trace file /u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_11146.trc
- It can be validated as below:
RMAN> backup validate database;
Starting backup at 10-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11G/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11G/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11G/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11G/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11G/undotbs01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12442 115209 998654
File Name: /u01/app/oracle/oradata/DB11G/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 58764
Index 0 12304
Other 0 31690
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 18066 64042 998653
File Name: /u01/app/oracle/oradata/DB11G/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 12179
Index 0 8677
Other 0 25078
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 385 7680 998654
File Name: /u01/app/oracle/oradata/DB11G/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7295
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 25210 25600 888027
File Name: /u01/app/oracle/oradata/DB11G/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 93
Index 0 39
Other 0 258
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 FAILED 0 1738 12803 775084
File Name: /u01/app/oracle/oradata/DB11G/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4410
Index 0 1262
Other 111 5390
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_11146.trc for details
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished backup at 10-APR-18
RMAN>
- Now Checking the V$database_block_corruption view
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 116 1 0 FRACTURED
5 7 109 0 ALL ZERO
5 6 1 0 FRACTURED
SQL>
Now it is showing the file number and block number which is corrupted, it can be repaired if we have a valid backup and archivelogs are also available:
- It can be as simple as this, by issuing single command:
RMAN> blockrecover corruption list;
Starting recover at 10-APR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11G/backupset/2018_04_04/o1_mf_nnndf_TAG20180404T223416_fdbb5rl0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11G/backupset/2018_04_04/o1_mf_nnndf_TAG20180404T223416_fdbb5rl0_.bkp tag=TAG20180404T223416
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 10-APR-18
RMAN>
- lets check the v$database_block_corruption view again and alert log file as well:
- Checking View
SQL> select * from v$database_block_corruption;
no rows selected
SQL>
2.Checking Alert log
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Started Block Media Recovery
Recovery of Online Redo Log: Thread 1 Group 1 Seq 19 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DB11G/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 20 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DB11G/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 21 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DB11G/redo03.log
Completed Block Media Recovery
So we have successfully performed recovery of our datafile without impacting database.
