Oracle Data File Corruption: Steps Before You Repair It

Oracle corruption tends to show up as an error naming a file and a block. ORA-01578 reports that a data block is corrupt and gives its file and block number, ORA-01110 names the data file involved, and an instance that will not open may report that a file needs media recovery. Internal errors such as ORA-00600 and ORA-07445 can also point to damage. The useful thing about these messages is that they are specific: they tell you what is affected, which is the starting point for deciding how to recover it.

Shut the instance down cleanly if it is still running, then copy the data files, control files, online redo logs, archived redo logs and parameter file to separate storage. If the database uses Transparent Data Encryption, copy the wallet or keystore as well, because without it the encrypted data cannot be read at all. If the files are in ASM, they are not visible as ordinary files, so use RMAN or asmcmd to copy them out. All recovery attempts should then run against the copy.

Oracle has its own tools for scoping the damage. The V$DATABASE_BLOCK_CORRUPTION view lists the corrupt blocks that RMAN or other operations have found, RMAN can validate the database and record any corruption it finds, and the DBVERIFY utility can check data files while the database is closed. Knowing whether the problem is a few blocks in one table, a whole data file, the SYSTEM tablespace or the control file changes the right next step completely.

If a valid backup and the archived redo logs since it exist, restoring the affected data file, or just the damaged blocks with RMAN block media recovery, and applying redo is the safest path, because it brings the data back rather than working around it. Check that the backup itself is usable before relying on it, and that the archived logs needed to bring it forward are all present.

Hidden parameters, the ones that begin with an underscore, such as those that let the database open despite resetlogs or rollback corruption, are unsupported outside of Oracle Support guidance. They can get a database to open, but they can leave it logically inconsistent, and the damage may not be visible until later. Also avoid deleting archived redo logs, recreating control files without understanding what they contained, and opening with RESETLOGS to make an error go away. DBMS_REPAIR can mark corrupt blocks so queries skip them, but that does not restore the data in them.

Block corruption usually comes from the storage layer: a failing disk, a controller or SAN fault, a power loss during a write, or an ASM disk group problem. Repairing the database onto storage that is still faulty tends to repeat the problem, so review the system and storage logs alongside the database alerts.

If there is no usable backup, if media recovery fails, or if the SYSTEM tablespace or all copies of the control file are damaged, the data files become the recovery source. Data can often be recovered from damaged files directly, but how much depends on what has been overwritten, and we tell you exactly what is recoverable after a free assessment. At that point, keep the original files untouched and work from the copy taken at the start.