Manually Create Corruption in Oracle Datafile
Note: Please take rman backup before go for corruption in datafile.(used it for testing purpose for block corruption)
Create corruption in datafile users01.dbf for testing block corruption
1. Test the user HR belong to 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 and write some character in it and save it
Note:This step caused corruption in USER.dbf file
4. Test you table again with select query and got 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 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