How to Recover Accidentally Deleted Oracle Databases: Step‑by‑Step Guide

Introduction

Accidental deletion of database files or tables is a common operational risk; the article provides practical methods to recover data without resorting to fleeing or resigning.

Physical Deletion Recovery

Single datafile removed

If only one datafile was mistakenly removed, you can restore it with RMAN after taking the file offline.

SQL> alter database datafile 4 offline;

RMAN> restore datafile 4;

RMAN> recover datafile 4;

RMAN> sql "alter database datafile 4 online";

SQL> select name, enabled, status from v$datafile;

After these steps the datafile is back online.

Entire database files removed

When all datafiles are lost, a full database restore from a backup is required.

SQL> startup mount

$rman target /

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

If the control file is intact the instance can be started to MOUNT; otherwise start in NOMOUNT.

Logical Deletion Recovery (Flashback)

Flashback a single table

Oracle’s RECYCLEBIN parameter (default ON) stores dropped tables, allowing them to be restored.

SQL> SELECT owner, original_name, object_name, ts_name, droptime FROM dba_recyclebin;

SQL> SHOW RECYCLEBIN

SQL> FLASHBACK TABLE persons TO BEFORE DROP;

Permissions required: DROP or DROP ANY TABLE, plus SELECT and FLASHBACK privileges.

Permanently drop a table

SQL> DROP TABLE persons PURGE;

SQL> PURGE TABLE persons;

SQL> PURGE INDEX persons;

Flashback the whole database

Flashback Database restores all datafiles to a previous point using archived redo logs, without needing a full backup of the files. SQL> ARCHIVE LOG LIST; If the database is not in ARCHIVELOG mode, enable it:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> STARTUP;

Configure the flash recovery area:

DB_RECOVERY_FILE_DEST_SIZE = 10G

DB_RECOVERY_FILE_DEST = '/u01/oradata/rvc_area'

Or set dynamically:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/oradata/rvc_area';

Set retention target (e.g., 1440 minutes = 1 day):

SQL> ALTER db_flashback_retention_target = 1440;

Enable flashback:

SQL> ALTER DATABASE FLASHBACK ON;

SQL> STARTUP OPEN;

SQL> SELECT FLASHBACK_ON FROM v$DATABASE;

Flashback can be performed by timestamp, SCN, or log sequence number:

SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('09-10-14 14:37:05','yy-mm-dd hh24:mi:ss');

SQL> FLASHBACK DATABASE TO SCN 947921;

SQL> FLASHBACK DATABASE TO SEQUENCE 12345;

If the result is unsatisfactory, you can recover the database to the current state: SQL> RECOVER DATABASE; Or undo a flashback operation with RECOVER DATABASE UNTIL.

Summary

Effective database maintenance must include clear procedures for both accidental file removal and logical table drops, along with robust backup mechanisms. Proper use of RMAN, flashback features, and recycle‑bin queries can dramatically reduce downtime and data‑loss risk.