Example of Manually Creating Corruption in Oracle Datafile

Note: Please take an RMAN backup before going for corruption in the data file. (used it for testing purposes for block corruption)

Create corruption in datafile users01.dbf for testing block corruption

1. Test the user HR belongs to the USER tablespace.

SQL> select count(*) from hr.emp;
COUNT(*)
----------
200

SQL> alter system flush buffer_cache;
System altered.

2. Go to the database data file location

select name from v$datafiles;

3. Open the user01.dbf file in Notepad write some characters in it and save it
Note: This step caused corruption in the USER.dbf file

4. Test your table again with a select query and get ora-01578 error.

Note: Repeat 1 step also give corruption error.

SQL> select count(*) from hr.emp;
select count(*) from hr.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1027)
ORA-01110: data file 4: ‘/u03/oradata/orcl/users01.dbf’

5. Check with the validate command through RMAN Utility:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

6. Check the v$database_block_corruption view.

SQL> select * from v$database_block_corruption;

FILE#  BLOCK#  BLOCKS  CORRUPTION_CHANGE#  CORRUPTION
-----  ------  ------  ------------------  ---------
  4    1027    1       0                   ALL ZERO