ORA-01578 and ORA-26040 tips

Oracle Tips by Burleson Consulting
December 21, 2007

Question: I’m having a block corruption problem. I have built my indexes in parallel using the NOLOGGING option, and now I get the ORA-26040 error and an indicator that the block is corrupt when I try to validate the index structure:

ANALYZE INDEX
INFRA.R33
VALIDATE STRUCTURE

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 15, block # 1011707)

ORA-01110: data file 15: 'E:\ORACLE\ORADATA\SDE\RASTER_D2.ORA'

ORA-26040: Data block was loaded using the NOLOGGING option.

Answer: Normally, block corruption is often due to a hardware error, and there is likely a trace file or log entry that was made when it happened. However, the ORA-26040 error says that the index was loaded using the nologging option and then possibly recovered from a backup taken before the nologging load.

For now, just rebuild the index without nologging. However, that's addressing the symptom, not the cause, and you must learn the root cause of the corruption.

If you did not do a recovery on this index, open an SR IMMEDIATELY and Oracle technical Support. They can use the block editor (BBED) utility to inspect the data blocks and see EXACTLY why they became corrupted. You can also inspect the blocks with BBED yourself.

The "ORA-26040: Data block was loaded using the NOLOGGING option" is the normal result of loading with the NOLOGGING option, because NOLOGGING disables all writing of redo log entries. Hence, there is no way for Oracle to recover the object, with RMAN or any roll-forward. No redo logs, no recovery. The docs note:

ORA-26040: Data block was loaded using the NOLOGGING option

Cause: Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option

Action: Drop the object containing the block.

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books