How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify


$ dbv BLOCKSIZE=8192 file=sysaux01.dbf
DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application
...
DBVERIFY - Verification complete

Total Pages Examined : 131072
Total Pages Processed (Data) : 69691
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 28669
Total Pages Failing (Index): 0
Total Pages Processed (Other): 15755
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 16957
Total Pages Marked Corrupt : 9
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3220271881 (11.3220271881)


For the dba number, identify the block

def dba=12585405
col block_id new_v block_id
col file_id new_v file_id
select dbms_utility.data_block_address_block(&dba) block_id,
dbms_utility.data_block_address_file(&dba) file_id from dual;

BLOCK_ID FILE_ID
---------- ----------
2493 3


From the block_id/file_id, identify the segment

col owner new_v table_owner
col segment_name new_v segment_name
select owner,segment_name,segment_type from dba_extents where file_id=&file_id and &BLOCK_ID between block_id and block_id + blocks - 1;
OWNER
------------------------------
SEGMENT_NAME
-----------------------------------
SEGMENT_TYPE
------------------
SYS
SYS_IL0000008786C00008$$
LOBINDEX


If it is a lob, identify the column and data_type

select tablespace_name,owner, table_name, column_name, data_type from dba_lobs join
dba_tab_columns using (owner, table_name, column_name) where segment_name =
'&segment_name' and owner='&table_owner';
TABLESPACE_NAME OWNER
------------------------------ ------------------------------
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
SYSAUX SYS
WRI$_DBU_FEATURE_USAGE FEATURE_INFO
CLOB


If you are lucky, you will find a useless segment that you will just drop. Or maybe you will be able to move all segments in another tablespace and drop the tablespace with the corrupt blocks.

If you are pretty unlucky like me today, you will find sys segments in system or sysaux.

Either you export all users data and import them again in a new database (but this means downtime), or you start moving the segments in another tablespace. Or dropping and recreating them.
Check Tablespace maintenance tasks

Once dropped or moved or emptied, you may still see the corrupted blocks. Do not forget to purge the recyclebin, either with PURGE DBA_RECYCLEBIN or PURGE TABLESPACE tbs1 USER usr1

Even then the corruption may belong to no more segment but still appear in dbverify. One workaround is to fill the tablespace (check it does not extend) with a dummy table

create table t(x number, y varchar2(4000) default lpad('x',4000,'x')) tablespace tbs1;

exec while true loop insert into t(x) select rownum r from dual connect by level<10000;commit;end loop exec while true loop insert into t(x) select rownum r from dual connect by level<100;commit;end loop exec while true loop insert into t(x,y) select rownum r,'x' from dual;commit;end loop exec while true loop insert into t(x,y) values (null,null);commit;end loop drop table t;

Run dbv again and again until you get completly rid of errors. If you drop and recreate sys objects, or even if you simply move them out of the sys tablespace, dictionary corruption and ora-600 is possible. But well, you had corruption anyway ...