Oracle Block Corruption - ORA-01578 ORA-01110 ORA-26040

The following errors were triggered in the alert log file.

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
ORA-01110: data file 5: '/oradata/users.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


Step 1:

If the error is after RMAN duplicate / RESTORE then enable force logging at source database and perform duplicate / restore with new RMAN backup.

Enabling force logging:

alter database force logging;

Step 2:

If the error is produced at physical standby database, Then the option is to restore the datafile from primary only if the primary database's datafile is in good condition. To avoid this problem from being introduced enable force logging at primary.

Note:-We can do exp/imp of the corrupted objects only if the corrupted object(block) is not of type index.

Identify the affected segment:

File numbers can be absolute file number(AFN) and relative file number(RFN), In most of the cases both are identical but can be different when the database has been migrated from 7i or transportable/plugged tablespaces were used.

Getting absolute file number:

The absolute file number is provide by the error line ORA-01110 which can be found after the error ORA-01578 line, From the above errors AFN is 5. We can get AFN using RFN using DBA_DATA_FILES.


We can use the following utilities to check for the corruption in the database.

1) RMAN

2) DBV

3) select * from v$database_block_corruption;


DBV will give information about the corrupted blocks in different ways. In general, It provides relative data block address (RDBA) associated with the effected block.

e.g.

Page 34 is marked corrupt
Corrupt block relative dba: 0x02c00022 (file 11, block 34)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x02c00022
 last change scn: 0x0771.4eebe71c seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe71c0602
 check value in block header: 0xd3ce
 computed block checksum: 0x2


In the above e.g. 0x02c00022 is the hex decimal block address and 11 is the RFN and 34 is block number.

Another way DBV will report the information about the corrupted blocks as follows.

DBV-200: Block, dba 46137379, already marked corrupted"

Here we use below query to find the RFN and Block number.

select dbms_utility.data_block_address_file(&&rdba) RFN,
dbms_utility.data_block_address_block(&&rdba) BL
from dual;
Enter value for rdba: 46137379

RFN        BL
---------- ----------
11         35

Getting AFN from RFN:

select file_id AFN, relative_fno, tablespace_name
from dba_data_files
where relative_fno=&RFN;
Enter value for rfn: 11

AFN        RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------

5          11           USERS


RMAN reports information about the corrupted blocks in v$database_block_corruption.


Step 3:

Identifying the corrupted objects:

select *
from dba_extents
where file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;

It will give list of objects and its types resides in the corrupted blocks.
If the above query does't return any rows then corruption is at segment header in LMTS and if the the corruption is at segment header then the above query produces a corruption message is alertlog file and query will not fail. In that case run the following query

select owner, segment_name, segment_type, partition_name
from   dba_segments
where  header_file = &AFN
  and  header_block = &BL;

If the corrupted block belongs to a free extent i.e block that is not associated to any object or if the corrupted block is in temp file then the above queries returns no data. For TEMP FILES segment type will be temporary, If the block belongs to a free extent it should appear in DBA_FREE_SPACE

select *
from  dba_free_space
where file_id = &AFN
  and &BL between block_id AND block_id + blocks - 1;

//** Identified object can be a free block (or) an index (or) a table (or) a LOB **//

If the identified block is an index type then drop and recreate index to resolve the error.

In our case the object type is index, So we have dropped and recreated indexes which resolved the issue.

Note:- Further document will be updated soon


The identified block is a FREE Block,

If the NOLOGGING Block is a FREE Block, there is the option to wait until the block is reused which will automatically re-format the block or force re-formatting the block using Doc ID 336133.1

The identified block holds a TABLE,

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements;

Doc ID 556733.1 has a DBMS_REPAIR example.

Then decide to re-create the segment: by moving the table: alter table &table_name move;

OR

by saving the data (export, Create Table as Select, etc) and then truncate or drop/create.

The identified block holds LOB,

If it is a LOB use Doc ID 293515.1