2019-02-14

ORACLE data block corrupted after restoring database (2)

A test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.
When such 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 the affected segment:

select segment_type, owner, segment_name, 
case segment_type WHEN 'INDEX' THEN 'ALTER INDEX '||owner||'.'||segment_name||' UNUSABLE; ALTER INDEX '||owner||'.'||segment_name||' REBUILD;' 
                  WHEN 'TABLE' THEN 'TRUNCATE TABLE '||owner||'.'||segment_name||';'
end as SQLstatement
from dba_extents
where file_id = 42 -- change
and 1029253 -- change
between block_id and (block_id + blocks -1);

If it is an index, we can (make unusable and rebuild) or (drop and recreate).