Oracle database failure types and how they are recovered
Six families of database failure (statement, network, user process, instance, media, unwanted changes) and the recovery approach for each.
A database can fail in several ways, from a single statement that cannot run to the loss of a disk. Oracle handles some failures without DBA action. Others need the DBA to find the cause and restore data. The table lists six failure types, what causes them, and how each one is handled.
| Failure type | Typical causes | Recovery |
|---|---|---|
| Statement failure | Syntax error; missing privilege on an object; a large insert or update that exceeds the free space of a tablespace; a logic error in the application; data that breaks an integrity constraint | Correct the statement; grant the privilege; add space (resize a datafile, or enable autoextend on a datafile or tablespace); fix the program; correct the data |
| Network failure | Failed network card; broken physical or logical link; failed Oracle Net listener | Use several network cards and links; configure a backup listener |
| User process failure | Client machine shut down; client software crashed; application closed abruptly | No DBA action. The PMON background process releases the locks and resources of the session and rolls back its uncommitted changes |
| Instance failure | Hardware or power failure; a background process shuts the database down after an error | Check the alert log and the trace files, then start the database. At startup, instance recovery (crash recovery) rolls forward committed changes and rolls back uncommitted ones, so no DBA action is needed for the recovery itself |
| Media failure | Disk or disk controller failure; datafiles, control files or redo log files deleted or modified outside Oracle | Restore the affected files from backup, which can mean the whole database, a set of datafiles, control files, redo logs or archived logs. Requires DBA action |
| Unwanted SQL statements | A table dropped by mistake; wrong insert, update or delete; a change committed that must be undone | Use Flashback features to restore a dropped table or to query data as of an earlier time; use LogMiner to analyze the changes; for uncommitted changes, use ROLLBACK |
Notes
- The time and effort of a media recovery depend on the backup and recovery strategy in place.