Tag: DBMS_REPAIR.skip_corrupt_blocks

Alert log shows following ORA- errors:

ORA-01578:ORACLE data block corrupted (file # 13, block # 652351)
ORA-01110:data file 13:'/media/sf_software/oradata/OEMREP/EMPDBREPOS/mgmt.dbf'
ORA-26040:Data block was loaded using the NOLOGGING option

Find out the segment impacted, and confirm this is an empty table.

SQL>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
    from dba_extents 
   where file_id =13 and 652351 between block_id AND block_id+blocks-1;

OWNER    SEGMENT_NAME           SEGMENT_TYPE   TABLESPACE_NAME
-------- ---------------------  -------------- ----------------
SYSMAN   MOS_PA_EVAL_TARGETS_E  TABLE          MGMT_TABLESPACE

SQL> select count(*) from SYSMAN.MOS_PA_EVAL_TARGETS_E;

COUNT(*)
----------
0

SQL>select FILE#, BLOCK#, BLOCKS,scn_to_timestamp(NONLOGGED_START_CHANGE#), 
           scn_to_timestamp( NONLOGGED_END_CHANGE#), OBJECT# 
    from v$nonlogged_block;

FILE# BLOCK#   BLOCKS  SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#)  SCN_TO_TIMESTAMP(NONLOGGED_END_CHANGE#)  OBJECT#
---------------------- -----------------------------------------  ---------------------------------------  -------
13   652351   1        12-MAY-20 11.58.27.000000000 AM           12-MAY-20 10.59.29.000000000 PM           91461

Truncate or move the table. 

SQL>truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

or

SQL> alter table SYSMAN.MOS_PA_EVAL_TARGETS_E move tablespace MGMT_TABLESPACE;

luckily, the table is empty. it is easy to deal with. If table is not empty, then we can skip corrupted blocks to avoid DML ORA- errors.

Skip corrupt blocks 

SQL> select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E;

select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 652351)
ORA-26040: Data block was loaded using the NOLOGGING option

BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SYSMAN',
    object_name => 'MOS_PA_EVAL_TARGETS_E',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/
 
SQL> select owner, table_name, SKIP_CORRUPT  from dba_tables 
      where table_name='MOS_PA_EVAL_TARGETS_E'

OWNER      TABLE_NAME                     SKIP_COR
---------- ------------------------------ --------
SYSMAN     MOS_PA_EVAL_TARGETS_E          ENABLED

Backup the table data, truncate the table , and then restore the table data

SQL> create table SYSMAN.STG_MOS_PA_EVAL_TARGETS_E as 
            select * from SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL> Truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL>Insert into SYSMAN.MOS_PA_EVAL_TARGETS_E 
                select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E ;

The nologging corrupt block is returned to free space list

SQL> Select BYTES from dba_free_space 
     where file_id=13 and 652351 between block_id and block_id + blocks -1;

BYTES
----------
65536

SQL> select * from dba_extents 
     where file_id =13 and 652351 between block_id AND block_id+blocks-1;

no rows selected

After a little while, the nologging corrupt block will be reused

SQL> Select BYTES from dba_free_space
where file_id=13 and 652351 between block_id and block_id + blocks -1; 

no rows selected

SQL> select count(*) from dba_extents
where file_id =13 and 652351 between block_id AND block_id+blocks-1; 

COUNT(*)
----------
1