Block Corruption in Oracle

Normally when block Corruption Happened with Oracle Database Oracle reports the corrupted block number in Error Message, 

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' 

This way .. 

we need to fix Corrupted block using few available methods, Best approach is RMAN where we have other Utilities as well 

BBED for block Corruption recovery. 

here in this case i tried to recover a block using RMAN 

---------

check DB / Datafile / status using validate command , which will list you the entire block details, with 

Used / Un Used and corrupted block if any 

 

RMAN> validate database;

Or  at Database level we can get the list using 

select * from v$database_block_corruption;

once we get the corrupted block details , we can recover it with steps given below. 

 

[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

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

STATUS INSTANCE_NAME DATABASE_ROLE

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

OPEN   SRPRIM        PRIMARY

SQL> select * from v$database_block_corruption;

no rows selected