2015-01-30

ORACLE data block corrupted after restoring database

Environment: Oracle database 11.2, Oracle Linux 5.7.

Test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.
When object is used, the following errors happen

ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)
ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'

Find affected segment
select segment_name, segment_type, owner
from dba_extents
where file_id = 42 -- change
and 1029253 -- change
between block_id and (block_id + blocks -1);

as in this case it is an index, we can (make unusable and rebuild) or (drop and recreate).
ALTER INDEX owner.name UNUSABLE;
ALTER INDEX owner.name REBUILD;