From the lab / Oracle recovery

Recover deleted Oracle rows without Flashback

Five rows gone. The coffee mug included. Here’s how I got them back from the datafile with reclaimDB.

A wrong DELETE is annoying. A wrong DELETE followed by COMMIT is where the afternoon gets interesting.

If Flashback Query can still see the old rows, I would start there. This is the other route: reading deleted row images that are still present in an Oracle datafile, using reclaimdb restore --include-deleted.

1. A small table. A committed mistake.

I used Oracle 19c and a plain heap table, RECLAIMDB.DESK_KIT: an ID, an item name and a quantity. Fifteen rows are enough to see what happens.

01 — before the deleteSQL*Plus
SQL> select * from RECLAIMDB.DESK_KIT order by ID;

 ID ITEM              QTY
--- ---------------- ----
  1 Notebook           12
  2 Pencil             30
  3 Pen                20
  4 Eraser              8
  5 Ruler               6
  6 Coffee mug          5
  7 USB cable           9
  8 Mouse pad           7
  9 Desk lamp           3
 10 Headphones          4
 11 Sticky notes       16
 12 Paper clips        50
 13 Stapler             4
 14 Folder             18
 15 Water bottle        6

15 rows selected.
02 — the mistakeSQL*Plus
SQL> delete from RECLAIMDB.DESK_KIT where ID between 6 and 10;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) as ROWS_LEFT from RECLAIMDB.DESK_KIT;

 ROWS_LEFT
----------
        10

1 row selected.

2. Preserve the datafile.

Stop writes to the affected table and preserve the files before blocks get reused. In this lab, I took the dedicated test tablespace OFFLINE NORMAL, then copied its datafile. Recovery ran against that copy.

03 — freeze this lab tablespaceSQL*Plus
SQL> alter tablespace BLOG_UNDELETE_261109 offline normal;

Tablespace altered.

On the recovery machine, my copy is source/desk_kit.dbf. I also copied the database’s SYSTEM datafile to read the table definition:

04 — read the table metadataShell
$ ./reclaimdb --quiet metadata --schema RECLAIMDB \
  > -o metadata.json source/system.dbf

The affected tablespace stays offline until the extraction is finished. Plan that interruption for a real system; don’t make an ordinary filesystem copy of a datafile while it is being written.

3. Include the deleted rows.

A normal export returned ten rows. Adding --include-deleted returned fifteen, with an extra _DELETED column.

05 — extract from the preserved copyShell · actual output
$ ./reclaimdb --quiet restore --include-deleted \
  > -m metadata.json --tables RECLAIMDB.DESK_KIT \
  > --csv-dialect rfc4180 --csv recovered source/desk_kit.dbf

  Exporting 1 tables (single-pass)...
  ✓ DESK_KIT                             15 rows  → recovered/reclaimdb.desk_kit.csv

  !  RESTORE COMPLETE — blocks failed validation
    15 rows exported to recovered (CSV, 0.0s)
    4 block(s) failed validation and were skipped; rows stored in them, if any, are not in the output.

This warning was a false alarm in this lab: the RC1 build counted four empty blocks as validation failures. Oracle DBVERIFY found no corruption, and all 15 rows and all 45 original values matched. A fix for the reporting is in our internal backlog. Other skip warnings still need checking.

06 — the recovered CSVCSV · 15 rows
$ cat recovered/reclaimdb.desk_kit.csv
ID,ITEM,QTY,_DELETED
1,Notebook,12,FALSE
2,Pencil,30,FALSE
3,Pen,20,FALSE
4,Eraser,8,FALSE
5,Ruler,6,FALSE
6,Coffee mug,5,TRUE
7,USB cable,9,TRUE
8,Mouse pad,7,TRUE
9,Desk lamp,3,TRUE
10,Headphones,4,TRUE
11,Sticky notes,16,FALSE
12,Paper clips,50,FALSE
13,Stapler,4,FALSE
14,Folder,18,FALSE
15,Water bottle,6,FALSE

There’s the coffee mug. The five TRUE rows are the deleted ones. The other ten are still live, so importing the whole file back into the original table would be a bad idea.

4. Bring the tablespace back online.

The extraction is finished. Before creating the staging table or inserting any rows, bring the test tablespace online again:

07 — online before writing backSQL*Plus
SQL> alter tablespace BLOG_UNDELETE_261109 online;

Tablespace altered.

5. Review, then put back the five rows.

I reviewed the five TRUE rows and put their ID, item and quantity into RECLAIMDB.DESK_KIT_RECOVERED, a small staging table. For this five-row lab, I used the SQL below. The _DELETED flag stays out of the application table.

Staging SQL for these five recovered rows
Staging — values read from the recovered CSVSQL*Plus
SQL> create table RECLAIMDB.DESK_KIT_RECOVERED
  (ID number(3) primary key, ITEM varchar2(24), QTY number(3));

Table created.

insert all
 into RECLAIMDB.DESK_KIT_RECOVERED values (6,'Coffee mug',5)
 into RECLAIMDB.DESK_KIT_RECOVERED values (7,'USB cable',9)
 into RECLAIMDB.DESK_KIT_RECOVERED values (8,'Mouse pad',7)
 into RECLAIMDB.DESK_KIT_RECOVERED values (9,'Desk lamp',3)
 into RECLAIMDB.DESK_KIT_RECOVERED values (10,'Headphones',4)
select 1 from dual;

5 rows created.

For a small CSV import without typing the values, SQL Developer’s Import Data wizard is another option: import a reviewed CSV containing only the five deleted rows and the three business columns into the empty staging table. Then use the same delta insert below.

With the reviewed rows in staging, I inserted only IDs that were still missing:

08 — reinsert the reviewed rowsSQL*Plus
SQL> insert into RECLAIMDB.DESK_KIT (ID, ITEM, QTY)
select r.ID, r.ITEM, r.QTY from RECLAIMDB.DESK_KIT_RECOVERED r
where not exists (select 1 from RECLAIMDB.DESK_KIT d where d.ID = r.ID);

5 rows created.

SQL> commit;

Commit complete.
15 → 10 → 15. Every ID, item name and quantity matched the original Oracle result after the reimport.

What this can—and cannot—recover

This works while the deleted row images are still in the blocks. It does not reconstruct overwritten rows, and the deletion flag is not a timestamp or proof that a row belongs to your particular incident. Review the recovered values before merging them into a real table.

I tested a committed DELETE on a small, uncompressed heap table. No Flashback query, undo file or redo log was used for the extraction. I did not force undo to expire in this run; the test proves the datafile recovery path, not a simulated Flashback failure.

For the broader workflow, see offline Oracle datafile extraction and the current recovery support and limitations.

If you want to try this with your own files, request a reclaimDB evaluation.

Oracle data recovery · Deleted rows · Flashback · Datafiles

← All posts