Backup and Recovery

How to Resolve ORA-01578, ORA-01110 and ORA-26040

Errors generated 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.
  1. 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?
  2. 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.
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 place

Relocate 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.

Leave a Reply

Your email address will not be published. Required fields are marked *