Oracle Flashback: Undo at the Database Level

Someone runs UPDATE accounts SET balance = 0 and forgets the WHERE. Commits it. A thousand balances are gone. The instinct is to reach for the backup — find last night’s copy, restore it somewhere, extract the rows, lose everything that happened since. Hours, if it works at all.

There’s a faster answer, and it’s built into the database: undo the mistake in place. Oracle Flashback is a family of features that reverse human error at point-in-time — a query, a table, a dropped object, or the entire database — in seconds to minutes, without restoring anything. And here’s the part that catches teams off guard: your standby doesn’t help. A Data Guard replica faithfully replicated that UPDATE to the standby within seconds. Replication is not a backup; it copies your mistakes as diligently as your data. Flashback is what actually saves you.

The confusion is that “Flashback” is not one thing — it’s half a dozen, built on different plumbing, with different reach. Knowing which one to grab, and where each one stops, is the whole skill.

The family, and what each is built on

Every Flashback feature reverses time, but they read from different places, and that determines their limits:

• Flashback Query / Version Query — read a table’s past directly, from UNDO. Nothing is changed; you just SELECT as of a past time. Bounded by undo_retention.
• Flashback Table (to SCN/time) — rewind a table’s rows to a past point, also from UNDO. Actually changes the data back. Bounded by undo_retention, and needs row movement.
• Flashback Drop — recover a dropped table from the recycle bin, where dropped objects sit until space is needed.
• Flashback Database — rewind the entire database to a past point, from flashback logs (or a guaranteed restore point). The heavy hammer; needs ARCHIVELOG.
• Flashback Data Archive — long-term history for chosen tables, so you can query years back, long after UNDO is gone. (Different problem — compliance history, not oops-recovery.)

The rule of thumb: the smaller the blast radius, the cheaper and faster the fix. Reach for the narrowest tool that covers what you broke.

flowchart TD
A["Something got broken"] --> B{"What's the blast radius?"}
B -- "Just need to SEE the old data" --> Q["Flashback Query<br/>SELECT ... AS OF"]
B -- "One table's rows are wrong<br/>(bad UPDATE/DELETE)" --> T["Flashback Table<br/>... TO SCN  (needs row movement)"]
B -- "A table was dropped" --> D["Flashback Drop<br/>... TO BEFORE DROP  (recycle bin)"]
B -- "Broad damage:<br/>many objects / a whole schema" --> DB["Flashback Database<br/>... TO RESTORE POINT  (needs ARCHIVELOG)"]
Q --> R["Recovered without restoring a backup"]
T --> R
D --> R
DB --> R

Look before you leap: Flashback Query

Before you change anything, you can see the past. Flashback Query reads a table as it was, straight from UNDO — no restore, no downtime, just a SELECT:

-- how many rows matched five minutes ago?
SELECT COUNT(*) FROM accounts AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- or as of a precise SCN you captured before the change
SELECT * FROM accounts AS OF SCN 12345678 WHERE id = 42;

Its sibling, Flashback Version Query, shows you the history of a row — every version between two points, with who-did-what pseudo-columns — which is how you find when the damage happened before you undo it:

SELECT versions_startscn, versions_operation, balance
FROM   accounts VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP
WHERE  id = 42;

versions_operation reads I/U/D — insert, update, delete. This is often all you need: read the good values as of the right SCN and put them back yourself. But for a whole table, there’s a cleaner way.

Undo a bad DML: Flashback Table to SCN

Someone zeroed every balance and committed. You don’t need to reconstruct anything — rewind the table itself to the moment before the mistake:

-- capture the SCN BEFORE the change (or find it with a version query afterward)
SELECT dbms_flashback.get_system_change_number FROM dual;   -- e.g. 12345678

-- ... the bad UPDATE happens, gets committed ...

-- rewind just this table to that point
FLASHBACK TABLE accounts TO SCN 12345678;

Two requirements bite people here. First, the table must have been created with ENABLE ROW MOVEMENT — Flashback Table physically re-inserts the recovered rows, changing their rowids, and Oracle refuses unless row movement is on. It’s the single most common reason this fails. Second, you can’t flash back across a structural change (a DDL) to the table, and Oracle won’t let you flash back to within a few seconds of the table’s creation (ORA-01466). Within those limits it’s instant, keeps working within undo_retention, and leaves triggers and indexes intact.

Recover a dropped table: Flashback Drop

DROP TABLE doesn’t (by default) destroy the table — it renames it and moves it to the recycle bin, a per-user holding area where it waits until the tablespace actually needs the space. So a drop is reversible until then:

-- oops
DROP TABLE accounts;

-- bring it back, rows and all, from the recycle bin
FLASHBACK TABLE accounts TO BEFORE DROP;

-- what's in the bin?
SELECT object_name, original_name, droptime FROM recyclebin;

Two things to know. DROP TABLE accounts PURGE bypasses the recycle bin — the object is gone immediately, no flashback. And the recycle bin is not infinite insurance: objects are purged automatically under space pressure, so “it was in the recycle bin last week” is not a recovery plan. It’s a safety net for the recent accidental drop, which is exactly when you need it.

Rewind everything: Flashback Database

Sometimes the damage is broad — a bad deployment, a truncate spree, a dropped schema. Table-level tools won’t cut it. Flashback Database rewinds the entire database to a past SCN, timestamp, or named restore point, using flashback logs it has been keeping since you turned the feature on. It’s the closest thing to a database-wide undo button — and the one that must be set up before the disaster:

-- prerequisites (once): ARCHIVELOG + a flashback recovery area
-- then, before a risky change, drop a marker you can rewind to:
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- ... the disaster: someone drops the whole app schema ...

-- rewind the WHOLE database to the restore point
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
ALTER DATABASE OPEN RESETLOGS;

A guaranteed restore point is the seatbelt you fasten before anything risky — an upgrade, a big data load, a schema migration. It pins the flashback logs so you can always get back to that exact point, no matter how much changes. In a multitenant database you can even rewind a single pluggable database with FLASHBACK PLUGGABLE DATABASE, leaving the others untouched. The catch is the setup: Flashback Database is only available if the database was already in ARCHIVELOG mode with flashback logging (or a guaranteed restore point) in place. Turn it on when things are calm; you can’t retrofit it mid-crisis.

Don’t take my word for it — run it. The Flashback lab builds a 1,000-row ACCOUNTS table and stages three disasters, each reversed and asserted: a committed UPDATE with no WHERE zeroes all 1,000 balances → FLASHBACK TABLE ... TO SCN brings them back (0 still zero); the table is dropped → FLASHBACK TABLE ... TO BEFORE DROP restores it with all 1,000 rows; and the entire LABUSER schema is dropped → the lab enables ARCHIVELOG, creates a guaranteed restore point, and FLASHBACK DATABASE TO RESTORE POINT rewinds the whole database, bringing the schema back with its 1,000 rows. If any recovery doesn’t restore the data, the run fails. All three are proven on every CI push.

What teams get wrong

• Believing a standby is a backup. Data Guard replicates your DELETE to the standby in seconds. A replica protects against hardware loss, not human error — for that you need Flashback (or a delayed standby apply). Don’t confuse the two.
• Creating tables without ENABLE ROW MOVEMENT. Then the day you need FLASHBACK TABLE ... TO SCN, it’s refused. Turn row movement on for tables you’d ever want to rewind.
• Assuming Flashback Query reaches back forever. It’s bounded by undo_retention and the size of the undo tablespace — under pressure, old undo is overwritten and you get ORA-01555. For guaranteed reach, set RETENTION GUARANTEE on undo, or use a Flashback Data Archive.
• DROP ... PURGE out of habit, or a full recycle bin. PURGE skips the recycle bin entirely, and the bin is auto-purged under space pressure. Neither is a bug — just know that “flashback drop” only works while the object is still in the bin.
• Never enabling Flashback Database — or never testing it. It’s unavailable unless ARCHIVELOG + flashback logging were on before the disaster. Turn it on, size the FRA, and actually run a flashback-to-restore-point drill, so it’s not the first time when it counts.
• A guaranteed restore point quietly filling the FRA. Guaranteed restore points pin flashback logs forever; leave one lying around and the recovery area fills, which can hang the database. Drop the restore point once the risky change is safely done.
• Reaching for Flashback Database when a table flashback would do. Rewinding the whole database to fix one table punishes everyone. Match the tool to the blast radius — narrowest first.

Flashback is the recovery discipline’s scalpel, and it belongs next to the heavier tools, not instead of them: RMAN restore and recovery is the fallback for media loss and corruption, Data Guard protects against site and hardware failure, and the HA decision tree places each where it belongs. Human error is the failure they don’t cover — and it’s the most common one. Turn Flashback Database on while things are calm, enable row movement on the tables you’d ever want to rewind, and know which tool matches which mistake. Then prove all three recoveries end to end with the Flashback lab, so the first time you undo a disaster isn’t in production with everyone watching.