ORA-01578 and ORA-26040
Errors found when dumping tables by exp.
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?
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.
Since there's no way to recover the corrupted data file, so we need to treat the object differently. First, we have to know what type and object was affected.
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 blocks
You 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 back
Modify 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);
4. Add data back
If the missing data is crucial to users, you have to add them back to make the table reinstated as much as possible by any means.
Hi I am getting ORA-01578: ORACLE data block corrupted (file # 53, block # 34982)
ORA-01110: data file 53: ‘/u01/app/oracle/oradata/orcl/users_08_01.dbf’
on table so can we drop and recreate that table ? so my ORACLE data block corrupted problem is resolved or not ?
According to your description, it’s corrupted. I explained the possible solution in this post, you can try it.