Recovering a Missing Oracle Datafile: Step‑by‑Step Diagnosis and Fix

Background : A client’s Oracle database reported that the database was open but one datafile (file 11) could not be accessed, leading to multiple ORA‑01157, ORA‑01110, ORA‑01565, and ORA‑27037 errors. The issue manifested as I/O errors when listing the file and when attempting to read it with dd.

Observed Errors :

Filesystem ls reports "input/output error" for zf4.dbf. dd cannot open the file.

Oracle trace logs contain ORA‑01157, ORA‑01110, ORA‑01122, ORA‑01565, and numerous ORA‑00600 internal errors (e.g., kdBlkCheckError, qosdFindObjRead).

Additional ORA‑12012, ORA‑06502, ORA‑00942, and other application‑level errors appear during attempted fixes.

Root Cause : The datafile suffered severe corruption (bad blocks), effectively making the file unreadable at the OS level.

Recovery Strategy for the Missing Datafile :

Run Oracle Data Unrecoverable (ODU) scans on the disk with two different offsets (0 and 4096) to locate raw extents belonging to the missing file.

Compare the two scan results, merge the extent information, and extract the raw data blocks.

Since the file size is about 20 GB and consists of a single extent, use dd to concatenate the extracted blocks into a new file.

Because the environment is non‑archival and normal recovery cannot be performed, modify the checkpoint information in the datafile header using the bbed utility.

Additional Recommendations :

For corrupted file 3, a simple DROP or TRUNCATE of the related objects followed by AWR rebuild is sufficient.

Consider using Oracle ASM for storage rather than traditional file systems, which are less reliable for large databases.

The described method is also applicable to similar issues on Windows where a datafile size becomes 0 KB.

Conclusion : By leveraging low‑level disk scans and manual reconstruction of the datafile, it is possible to recover a severely corrupted Oracle datafile when standard recovery paths fail, though the process is complex and should be a last resort.