Manually Corrupting Oracle Data Block & Recover using RMAN

What is data block corruption?

A database block is corrupted when its content has changed from what Oracle Database expects to find. If not prevented or repaired, block corruption can bring down the database and possibly result in the loss of key business data.

Prerequisite :

• Audiance of this document should be experienced DBA
• Database should be in archive log mode and have good backup

Procedure of work :

• Create tablespace  using sysdba
• create user  using sysdba
• create table  using test user 
• insert records  using test user
• check the header of table segment 
• manually corrupt the block on terminal 
• flush the buffer cahce  as sysdba
• Disconnect and re-connect test user and query the table you will get block corruption error 

To get information about datafiles exists in database .

select file_name from dba_data_files;

create tablespace useing below command :

create tablespace app_corrupt datafile '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf' size 5m;

create user as follow below command

create user test identified by test123
default tablespace app_corrupt
quota unlimited on app_corrupt;

grant create session, resource to test;


Login using test user account :

create emp table :

create table emp(eno number(8)) tablespace app_corrupt;

Insert records in emp table :

begin
for i in 1..1000000
loop
insert into emp values(i);
end loop;
end;


select count(*) from emp;


SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP';

HEADER_BLOCK
------------
         130

SQL>

Take tablespace backup using RMAN:

backup tablespace app_corrupt;

Mannualy Corrupt the datafile block using below command:

dd of=/u01/app/oracle/oradata/testdb/corrupt_block_test.dbf bs=8192 conv=notrunc seek=130  EOF
testing corruption
EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 0.000133601 s, 142 kB/s


[oracle@localhost ~]$

SQL> select count(*) from emp;
select count(*) from emp
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/risdev/app_corrupt01.dbf'


SQL>

connect with RMAN and validate tablespace :

RMAN> validate tablespace APP_CORRUPT;

Starting validate at 05-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=/u01/app/oracle/oradata/risdev/app_corrupt01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    FAILED 0              1            640             1669173
  File Name: /u01/app/oracle/oradata/risdev/app_corrupt01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              496
  Index      0              0
  Other      1              143

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/risdev/risdev/trace/risdev_ora_24069.trc for details
Finished validate at 05-SEP-19

RMAN>


SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         5        130          1                  0 CORRUPT            0

SQL>


Recover corrupt block using RMAN:

RMAN> blockrecover datafile 5 block 130;

Starting recover at 05-SEP-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 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/db/dbs/02ub15f9_1_1 tag=TAG20190905T185433
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 05-SEP-19

RMAN>



SQL>
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SQL>