Recover Mistaken Oracle Data Deletions with Backup, LogMiner, and Flashback

Background

Accidental DML or connection errors can cause data loss in Oracle databases. Oracle provides several mechanisms to recover the lost or corrupted data.

Illustrative scenario

A full logical export backup was taken at 23:00 and a hot physical backup of all datafiles was made. At 10:00 the column ztm of table TFUNDASSET was updated with an incorrect condition and the change was committed.

1. Logical backup with Export/Import

Best for tables with low change frequency (e.g., reference tables).

Export the table:

expdp user/password tables=TFUNDASSET directory=DP_DIR dumpfile=tfundasset.dmp logfile=exp.log

Delete the erroneous rows from the original table (or truncate).

Import the dump, using IGNORE=Y to skip existing‑table errors:

impdp user/password tables=TFUNDASSET directory=DP_DIR dumpfile=tfundasset.dmp logfile=imp.log IGNORE=Y

Validate the restored rows and re‑apply any legitimate changes that occurred after the export.

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

Requires the database to be in ARCHIVELOG mode.

Shut down the database: SHUTDOWN IMMEDIATE; Determine the SCN or timestamp just before the bad UPDATE (use LogMiner or operator estimate).

Restore the most recent data‑file backup while preserving the current control file.

Recover to the target point: RECOVER DATABASE UNTIL TIME '2023-07-15 09:55:00'; or RECOVER DATABASE UNTIL SCN 12345678; Open the database with reset logs:

ALTER DATABASE OPEN RESETLOGS;

3. LogMiner (DBMS_LOGMNR) extraction

Allows retrieval of the original DML statements without shutting down the database.

Set a directory for the external dictionary by adding UTL_FILE_DIR='C:\logminer' to init.ora and restart the instance.

Run the script $ORACLE_HOME/rdbms/admin/dbmslmd.sql as SYS to rebuild the DBMS_LOGMNR_D package.

Create the dictionary file: EXEC DBMS_LOGMNR_D.BUILD('C:\logminer\dict.ora'); Add the redo and/or archive log files to be analyzed:

EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'C:\oradata\orcl\redo01.log', OPTIONS=>DBMS_LOGMNR.NEW);

Start LogMiner:

EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_FILE);

Query V$LOGMNR_CONTENTS to locate the UPDATE on TFUNDASSET and capture the original values.

Optionally store the result for later use: 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

Fastest method when the required undo data is still retained.

Ensure Automatic Undo Management is enabled and that UNDO_RETENTION is large enough (seconds). Adjust if needed: ALTER SYSTEM SET UNDO_RETENTION = 86400 SCOPE=BOTH; Query the table as of the point before the error:

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

For DML recovery, enable flashback for the session, insert or update using the rows retrieved in step 2, then disable:

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2023-07-15 09:55:00','YYYY‑MM‑DD HH24:MI:SS'));
-- perform INSERT/UPDATE using the AS OF data
EXEC DBMS_FLASHBACK.DISABLE;

Flashback is unavailable if the undo tablespace has already overwritten the needed undo records.

5. Comparison and best‑practice recommendations

Export/Import – simple, works for low‑change tables; manual re‑application of later changes is required.

Incomplete recovery – restores large data sets without LogMiner; requires database downtime and loses all changes after the recovery point.

LogMiner – no downtime, can recover any DML; needs ARCHIVELOG mode and careful selection of log files.

Flashback – quickest when undo is available; consumes undo tablespace.

Preventive measures to reduce the need for recovery:

Create separate database users and passwords for development, testing, and production environments.

Separate application owners from DBA accounts and grant only the privileges required.

Always preview DML with a SELECT using the same WHERE clause before executing the statement.