How to solve block corruption in Oracle

Step 1:

If block corruption was found, first find the corrupted blocks:

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
       104    1572928          1                  0 CHECKSUM           0

Step 2:

Check which table/index/etc. that block belongs to.

SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2    FROM dba_extents
  3  WHERE file_id = 104 and 1572928 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME                SEGMENT_TYPE
------------------------------ ------------------
OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
MYTABLESPACE                        TABLE
MYOWNER
TEST

Step 3:

Resolve the block corruption using RMAN (which is one of the possibilities):

RMAN> connect target

connected to target database: MYDB (DBID=3680315615)

RMAN> run {
2>  allocate channel sbt_1 device type 'SBT_TAPE';
3>  RECOVER CORRUPTION LIST;
4> }

using target database control file instead of recovery catalog
allocated channel: sbt_1
channel sbt_1: SID=594 device type=SBT_TAPE
channel sbt_1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)

Starting recover at 26-APR-19

new media label is "V_8523253_15501860" for piece "MYDB_bfavrppi.56893_1"
new media label is "V_8523253_15496902" for piece "MYDB_bfavrppi.56789_1"
channel sbt_1: restoring block(s)
channel sbt_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00104