Recovering Corrupted Blocks

There were certain blocks corrupted in a datafile which did not allow me to query tables associated with the corrupted blocks

 SQL> select * from update7sp7.test1;
 select * from update7sp7.test1
 *
 ERROR at line 1:
 ORA-01578: ORACLE data block corrupted (file # 8, block # 138 )
 ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.8 19379005'

SQL> select * from update7sp7.test2;
select * from update7sp7.test2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 146)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'

The following errors were reported in alert log file:
Errors in file /u01/app/oracle/diag/rdbms/srpstb/srpstb/trace/srpstb_m000_3839.trc (incident=43378):

ORA-01578: ORACLE data block corrupted (file # 8, block # 138)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'
Errors in file /u01/app/oracle/diag/rdbms/srpstb/srpstb/trace/srpstb_m000_3839.trc (incident=43379):
ORA-01578: ORACLE data block corrupted (file # 8, block # 146)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'
Checker run found 3 new persistent data failures
Fri Oct 04 18:29:25 2013

Now, let me check the same using DBV utility.

[oracle@ora1-2 trace]$ dbv file='+DATA_NEW/srpstb/datafile/myts.262.819379005' userid=sys/oracle

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Oct 4 18:38:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA_NEW/srpstb/datafile/myts.262.819379005

DBV-00111: OCI failure (4157) (ORA-00600: internal error code,arguments: [kudbvslfw1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_DBVERIFY", line 24
ORA-06512: at line 1

Oops !! DBV is giving me out an internal error. Upon checking with Oracle Support, came to know that it was a bug
Bug 13811172 dbms_dbverify may fail with dbv-111: oci failure (4157) ora-600 [kudbvslfw1]

So, let me check for the corruption using RMAN “validate database”.

RMAN> validate database;

Starting validate at 04-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_NEW/srpstb/datafile/system.261.819379005
input datafile file number=00002 name=+DATA_NEW/srpstb/datafile/sysaux.257.819378991
input datafile file number=00005 name=+DATA_NEW/srpstb/datafile/example.258.819378991
input datafile file number=00008 name=+DATA_NEW/srpstb/datafile/myts.262.819379005
input datafile file number=00003 name=+DATA_NEW/srpstb/datafile/undotbs1.263.819379005
input datafile file number=00006 name=+DATA_NEW/srpstb/datafile/myts.264.819379005
input datafile file number=00007 name=+DATA_NEW/srpstb/datafile/tbs.259.819378991
input datafile file number=00004 name=+DATA_NEW/srpstb/datafile/users.260.819378991
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14671        92203           2505790
File Name: +DATA_NEW/srpstb/datafile/system.261.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              60610
Index      0              12734
Other      0              4145

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              18749        74365           2505788
File Name: +DATA_NEW/srpstb/datafile/sysaux.257.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              12822
Index      0              9345
Other      0              33324

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            6403            2505790
File Name: +DATA_NEW/srpstb/datafile/undotbs1.263.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other 0 6399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              18           669             1786990
File Name: +DATA_NEW/srpstb/datafile/users.260.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              106
Index      0              39
Other      0              477

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              31391        44242           2397708
File Name: +DATA_NEW/srpstb/datafile/example.258.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              4509
Index      0              1148
Other      0              7192

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              1            6401            1370949
File Name: +DATA_NEW/srpstb/datafile/myts.264.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other      0              6399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1            1281            1348023
File Name: +DATA_NEW/srpstb/datafile/tbs.259.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other      0              1279

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     3              1            12809           2396687
File Name: +DATA_NEW/srpstb/datafile/myts.262.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              26
Index      0              9
Other      0              12764

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
--------- ------ -------------- ---------------
SPFILE    OK     0              2
Control File OK 0 642
Finished validate at 04-OCT-13

We can even use “Validate datafile 8;” command at RMAN to check the corruption if we know the exact datafile ID.

From the above outcome, it is clear that datafile ID 8 (+DATA_NEW/srpstb/datafile/myts.262.819379005) has 3 corrupted blocks. Let me get the corrupted block# from v$database_block_corruption view.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ----------------
 8    137    2      0                  CORRUPT
 8    146    1      0                  CORRUPT

From the above outcome, it is clear that 2 blocks starting from block# 137 (block 137 and 138) of datafile 8 are corrupted and so is block 146 of datafile 8 corrupted.
Let’s recover them using RMAN (Block Recovery).

[oracle@ora1-2 trace]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 418:41:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> recover datafile 8 block 137 to 138,146;

Starting recover at 04-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
searching flashback logs for block images until SCN 2501389
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s) from datafile copy /u02/bkp/myts_8
failover to previous backup

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backupset
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece /u02/bkp/srprim_inc0_37olibpj_1_1.bak
channel ORA_DISK_1: piece handle=/u02/bkp/srprim_inc0_37olibpj_1_1.bak tag=TAG20131004T120611
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 04-OCT-13

Recovery has been completed successfully. Let’s verify the same from the view “V$DATABASE_BLOCK_CORRUPTION”.

[oracle@ora1-2 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 4 20:01:48 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- --------------
OPEN   srpstb        PRIMARY

SQL> select * from v$database_block_corruption;

no rows selected

There are no corruption reported by the view and now we should be able to query the tables that was initially done.

SQL> select * from update7sp7.test1;

CODE NAME
---- ------
100  rama
101  raja
102  soma
103  ravi
104  shivu
105  adsf
106  ijasdf

7 rows selected.

SQL> select * from update7sp7.test2;

CODE
----------
200
201
202
203
204

Here we go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.