Recover Accidentally Deleted Oracle Data with Backup, LogMiner, and Flashback

Introduction

Accidental deletions or incorrect updates in Oracle can corrupt data. Oracle provides built‑in mechanisms to restore lost or wrongly modified data.

Recovery methods

Scenario: a nightly full logical export backup at 23:00, a hot physical backup of all datafiles, and at 10:00 a mistaken update on the TFUNDASSET table that changed thousands of ztm values.

Logical backup with imp Physical backup with point‑in‑time recovery

LogMiner analysis of redo logs

Flashback Query and

DBMS_FLASHBACK

1. Logical backup using import

Suitable for small, relatively static tables.

Export the table to a dump file or copy it to a backup table.

Delete the erroneous rows from the original table.

Import the table with IGNORE=Y to skip “table already exists” errors.

Verify the data after import and apply any post‑import adjustments.

2. Physical backup with incomplete (point‑in‑time) recovery

Requires ARCHIVELOG mode.

Shut down the database.

Identify the exact time of the bad operation (operator estimate or LogMiner).

Restore datafiles from the most recent physical backup, preserving the control file.

Perform point‑in‑time recovery to a timestamp just before the error, e.g.

RECOVER DATABASE TO TIMESTAMP TO_DATE('2023-10-20 09:58:00','YYYY‑MM‑DD HH24:MI:SS');

Open the database with ALTER DATABASE OPEN RESETLOGS; and reapply business data for the missing interval.

3. Using DBMS_LOGMNR to mine redo logs

Prerequisites: database in ARCHIVELOG mode, UTL_FILE_DIR set, and DBMS_LOGMNR_D package built.

Modify init.ora to set UTL_FILE_DIR to a directory for the external dictionary file.

Run @$ORACLE_HOME/rdbms/admin/dbmslmd.sql as SYS to create DBMS_LOGMNR_D.

Build the external dictionary:

BEGIN DBMS_LOGMNR_D.BUILD('dict_path','dict_file'); END;

Add the redo log files to be analyzed:

BEGIN DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'path/to/redo1.log', OPTIONS=>DBMS_LOGMNR.NEW); END;

Start LogMiner:

BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_FILE); END;

Query V$LOGMNR_CONTENTS to locate the offending DML statements, optionally copy to a table:

CREATE TABLE undo_sql AS SELECT * FROM V$LOGMNR_CONTENTS;

End the LogMiner session:

EXEC DBMS_LOGMNR.END_LOGMNR;

4. Flashback Query and DBMS_FLASHBACK

Available since Oracle 9i. Requires Automatic Undo Management and sufficient UNDO_RETENTION (seconds).

Set undo retention if needed:

ALTER SYSTEM SET UNDO_RETENTION = 86400; -- 24 hours

Query the table as of a past timestamp:

SELECT * FROM TFUNDASSET AS OF TIMESTAMP TO_TIMESTAMP('2023-10-20 09:58:00','YYYY‑MM‑DD HH24:MI:SS');

Insert the result into a temporary table and use it to restore the original rows.

Alternatively, enable session‑wide flashback with DBMS_FLASHBACK, e.g.:

BEGIN DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2023-10-20 09:58:00','YYYY‑MM‑DD HH24:MI:SS')); END;

Flashback is automatically disabled when the session ends.

Comparison

exp/imp

works for low‑change tables but may still need manual correction.

Point‑in‑time physical recovery restores data but requires downtime and loses changes after the recovery point.

LogMiner provides comprehensive recovery without downtime, provided the database runs in ARCHIVELOG mode.

Flashback is the quickest method, but depends on sufficient undo retention and consumes undo tablespace.

Preventive measures

Create separate database users and passwords for different environments.

Separate owner and application users with appropriate privileges.

Validate DML statements with a SELECT using the same conditions before executing.