ORA-01578: ORACLE data block corrupted (file # 53, block # 34982)
ORA-01110: data file 53: '/u01/app/oracle/oradata/orcl/users_08_01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
There’re two key phrases that we should notice.
- data block corrupted: It means that the data was already corrupted, mainly due to logically corruptions, it’s not likely the hardware failure. But the key question is: Can we recover the corrupted data?
- NOLOGGING: It almost equals to “No Recover” in terms of data protection, even though your database is in ARCHIVELOG mode. So don’t expect the corrupted data is salvageable.
SQL> select * from dba_extents where file_id = 53 and 34982 between block_id and block_id + blocks - 1;
Note, in the statement, 53 is the absolute file number (AFN) and 34982 is the block number (BL). They can be found in the error message.
Different corrupted types of object have different solutions. If the object is an index, then we’re really lucky, just drop it then create. The corrupted blocks will be reformatted by background processes soon or later.
If the object is a table, you have to be patient, there’s a long way to go. The best solution that we can do is to repair the table by skipping the corrupted data (i.e. rows), move all of available (non-corrupted) data to a safer place and then insert the missing rows back into the table manually or, do nothing if the corrupted data is really irrelevant.
1. Skip corrupted blocksYou can skip the corrupted blocks by executing dbms_repair.skip_corrupt_blocks with flag SKIP_FLAG, which allows data manipulations on healthy blocks.
SQL> exec dbms_repair.skip_corrupt_blocks (schema_name => '&schema_name', object_name => '&object_name', object_type => dbms_repair.table_object, flags => dbms_repair.SKIP_FLAG);
2. Move table to other placeRelocate the table to somewhere else in the same tablespace.
SQL> alter table table_name move;
Or move it to other tablespace.
SQL> alter table table_name move tablespace tablespace_name;
3. Set noskip flag backModify the table back to NOSKIP_FLAG
SQL> exec dbms_repair.skip_corrupt_blocks (schema_name => '&schema_name', object_name => '&object_name', object_type => dbms_repair.table_object, flags => dbms_repair.NOSKIP_FLAG);