Manually create block Corruption in Oracle Datafile

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 


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s