Block Corrouption

If database can be started in open mode then do below keeping DB open.
If DB is in noarchive mode or DB cannot be opened or situation is like, DB can be opened but after some time DB goes in recovery mode(may be frequent ORA-600[17182] & ORA-600[17114] in alert log) and stopped then starup in mound mode and follow steps below:€

1. Set $ORACLE_SID and $ORACLE_HOME

2. $rman target / nocatalog

3. from rman prompt validate all datafiles(user "check logical" as rman normally checks only physical corruption):

run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

Now v$database_block_corruption view is populeted with corroupted blocks.

4. If you have RMAN backup and related archive logs then perform block corruption recovery using rman and go to step 9.


5. If DB is in open mode and you can query dictionary (dba_%) tables then go to the next step otherwise folow as below:
- execute below query to find out datafiles with corroupted blocks

select a.FILE#, a.NAME, b.TS#, b.NAME
from v$datafile a, v$tablespace b
where a.FILE# in
(select FILE# from v$database_block_corruption group by FILE#)
and a.TS# = b.TS#;

- make datafiles offline which are got from above query
- alter database open
- goto next step(6)

6. Execute the script

create table segment_corrupted (owner varchar2(10), segment_name varchar2(50), segment_type varchar(20));
truncate table segment_corrupted;

declare
nrows number;
badsegs number;
begin
badsegs:=0;
nrows:=0;
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from dba_extents where file_id=i.file# and i.block# between block_id and block_id + blocks -1;
commit;
end;
nrows:=nrows+1;
end loop;
commit;
end;
/

7. Drop the objects found in segment_corrupted table

8. Recover datafiles (from step 5) and make online as below:
recover datafile 'datafilename';
alter database datafile 'datafilename' online;

9. Bounce the DB and look in the alert log if there are any alerts

10. Take a full DB backup using rman