Data corruption recovery
A step-by-step decision tree for data corruption (block/page checksum errors, unreadable tables, corrupted RDB/AOF): first confirm the scope of the corruption, then choose correctly between block-level recovery, a full restore/PITR from backup and last-resort repair tools.
What is the scope of the corruption?
First run an integrity check to determine how far the corruption has spread. Are only a few blocks/pages corrupted, is a single object (table/index) affected, or is the corruption widespread? Scan with the commands below to determine the scope, then choose the appropriate branch.
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE; -- then: SELECT file#, block#, blocks, corruption_type FROM v$database_block_corruption;- Only a few blocks / pages are corrupted
- A single object (table/index) is affected
- Corruption is widespread / many objects
- Check tools don't work, the DB won't open
- Corruption keeps recurring (root cause suspected)
Root cause: limited block/page corruption → targeted recovery
If only a few blocks/pages are corrupted, there is no need to restore the whole database. Minimize data loss by repairing only the corrupted parts from backup/logs with the engine's block/page-level recovery feature.
Root cause: object-level/widespread corruption → full restore or PITR from backup
If the corruption has spread to one or more objects, the safest path is to restore from a good backup and, where possible, roll forward to the moment of corruption (PITR). This avoids the data loss repair tools would cause.
No backup / DB won't open — moving to the last resort?
Repair tools (REPAIR_ALLOW_DATA_LOSS, innodb_force_recovery, --fix) CAN CAUSE DATA LOSS and can break integrity constraints. They are a last resort only when no usable backup exists and the database cannot be opened. First make sure there really is no usable backup.
- I found a good backup, let me restore
- There is no backup at all, a repair tool is the only option
Last resort: repair tools with data loss risk
These tools discard corrupted rows/pages to make the database openable. Use them only if there is no backup and you have first taken a copy of the existing files. Goal: open it just enough to export the data, then move it to a clean copy.
Root cause: hardware/infrastructure → fix the source and enable checksums
If corruption keeps recurring, the real cause is most likely the disk, the controller, memory (RAM) or an unstable storage layer. Recovering the data alone is not enough; if you do not fix the source, the corruption will come back. Also enable checksum/integrity verification for early detection.