Repair Corrupt Blocks using RMAN Data Recovery Advisor Scenario - Oracle 11gR2

SQL> show parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DB11G1

SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6        131          1                  0 CORRUPT

Sample corrupted block I created manually

SQL> select * from MAC.MAC_SAMPLE;
select * from MAC.MAC_SAMPLE
                  *ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '+DATA/db11g1/datafile/mac.270.1001447963'

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@db11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 8 11:01:55 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G1 (DBID=1931916594)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
682        HIGH     OPEN      28-FEB-19     Datafile 6: '+DATA/db11g1/datafile/mac.270.1001447963' contains one or more corrupt blocks
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
682        HIGH     OPEN      28-FEB-19     Datafile 6: '+DATA/db11g1/datafile/mac.270.1001447963' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=172 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command.  Then perform a Data Guard role change (failover).  Available standbys: DB11F1.
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 131 in file 6
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/db11g1/DB11G1/hm/reco_2517526839.hm
RMAN> exit

Recovery Manager complete.

Check what is inside repair script.

c[oracle@db11g ~]$ cat /u01/app/oracle/diag/rdbms/db11g1/DB11G1/hm/reco_2517526839.hm
   # block media recovery

   recover datafile 6 block 131;

[oracle@db11g ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 8 11:03:42 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G1 (DBID=1931916594)
RMAN> repair failure;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 03/08/2019 11:04:03RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN> advise failure
2> ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================RMAN-03002: failure of advise command at 03/08/2019 11:04:11
RMAN-07211: failure option not specified

RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
682        HIGH     OPEN      28-FEB-19     Datafile 6: '+DATA/db11g1/datafile/mac.270.1001447963' contains one or more corrupt blocks
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
682        HIGH     OPEN      28-FEB-19     Datafile 6: '+DATA/db11g1/datafile/mac.270.1001447963' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 131 in file 6
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/db11g1/DB11G1/hm/reco_666347409.hm

Using below command this will execute the repair script highlighted above.

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/db11g1/DB11G1/hm/reco_666347409.hm
contents of repair script:
   # block media recovery
   recover datafile 6 block 131;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting recover at 08-MAR-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece +FRA/db11g1/backupset/2019_02_28/nnndf0_tag20190228t200741_0.298.1001448461
channel ORA_DISK_1: piece handle=+FRA/db11g1/backupset/2019_02_28/nnndf0_tag20190228t200741_0.298.1001448461 tag=TAG20190228T200741
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 08-MAR-19
repair failure complete

Check whether the corruption still exists;

RMAN> list failure;
no failures found that match specification
RMAN> exit

Recovery Manager complete.

[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 8 11:05:20 2019
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter db_n

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DB11G1

Block Corruption is no longer there;

SQL> select * from MAC.MAC_SAMPLE;
        ID
----------
       123