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.

What did you break, and which Flashback undoes it. Start narrow: a query to look, a table-level flashback for one object, the whole-database rewind only when the damage is broad. The heavier the tool, the more it needs set up in advance.

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.

Frequently asked questions

What is Oracle Flashback?

Oracle Flashback is a family of features that let you reverse the effects of human error at a point in time without restoring a backup. It includes Flashback Query and Version Query (read a table as it was in the past, from undo data), Flashback Table (rewind a table’s rows to a past SCN or time), Flashback Drop (recover a dropped table from the recycle bin), Flashback Database (rewind the entire database to a past point using flashback logs or a restore point), and Flashback Data Archive (retain long-term history for chosen tables). Each reads from different underlying data — undo, the recycle bin, or flashback logs — which determines how far back it can go and what it requires.

What is the difference between Flashback and restoring from a backup?

Restoring from a backup (for example with RMAN) copies datafiles from a saved backup and rolls forward, which is slower, usually needs a separate location or downtime, and typically loses everything since the backup unless you also apply archived logs. Flashback undoes changes in place using data the database already keeps — undo, the recycle bin, or flashback logs — so recovery is fast and surgical, from seconds to minutes, with no restore. Flashback is the right tool for recent human error (a bad UPDATE, a dropped table, a bad deployment); RMAN restore is the fallback for media loss, corruption, or when the change is older than your flashback and undo retention allows.

Why does FLASHBACK TABLE ... TO SCN require row movement?

Flashback Table recovers rows by physically deleting the current rows and re-inserting the past versions, which gives them new rowids. Oracle only permits an operation that changes rowids if the table has row movement enabled, so FLASHBACK TABLE ... TO SCN (or TO TIMESTAMP) fails on a table created without it. You enable it with CREATE TABLE ... ENABLE ROW MOVEMENT or ALTER TABLE name ENABLE ROW MOVEMENT. This is the most common reason a table-level flashback is refused. Note that Flashback Drop (TO BEFORE DROP) and Flashback Query do not need row movement — only the table-to-SCN rewind does.

How far back can Flashback Query go?

Flashback Query and Flashback Table read from undo, so they can go back only as far as undo for the changed blocks still exists — governed by the UNDO_RETENTION parameter and the size of the undo tablespace. Under write pressure Oracle may overwrite unexpired undo to avoid failing DML, so a query too far back can return ORA-01555 (snapshot too old). To make the reach reliable, set RETENTION GUARANTEE on the undo tablespace (which prevents overwriting unexpired undo) and size it accordingly, or use a Flashback Data Archive for tables that need long-term, guaranteed history measured in months or years.

What is FLASHBACK TABLE ... TO BEFORE DROP and the recycle bin?

When you DROP a table, Oracle does not immediately destroy it by default — it renames the object and its dependents and moves them to the recycle bin, a per-user logical holding area, where they remain until the tablespace needs the space. FLASHBACK TABLE name TO BEFORE DROP restores the table and its data from the recycle bin, optionally renaming it with RENAME TO. Two caveats: DROP TABLE name PURGE bypasses the recycle bin and destroys the object immediately (no flashback), and objects in the recycle bin are purged automatically under space pressure, so it is a safety net for a recent accidental drop, not a long-term backup.

What does Flashback Database require?

Flashback Database rewinds the entire database to a past SCN, timestamp, or restore point using flashback logs, and it must be set up in advance. The database must be in ARCHIVELOG mode with a fast recovery area (FRA) configured, and either flashback logging turned on (ALTER DATABASE FLASHBACK ON) or a guaranteed restore point created, so the flashback logs exist to rewind through. The operation itself is run with the database mounted (not open): SHUTDOWN, STARTUP MOUNT, FLASHBACK DATABASE TO ..., then ALTER DATABASE OPEN RESETLOGS. Because these prerequisites cannot be added after the fact, enable Flashback Database before you need it and test a flashback-to-restore-point drill.

Can I flash back a single pluggable database (PDB)?

Yes. In a multitenant database you can rewind one PDB independently with FLASHBACK PLUGGABLE DATABASE, leaving the other PDBs and the rest of the CDB untouched, provided the CDB is in ARCHIVELOG mode and local undo is in use (the default in recent releases). You can flash a PDB back to a PDB-level restore point, an SCN, or a timestamp, and you can create guaranteed restore points scoped to a single PDB. This is far less disruptive than flashing back the whole container database, so for damage confined to one PDB it is the right level to operate at — the same "match the tool to the blast radius" principle as choosing between table and database flashback.

Does a Data Guard standby protect against an accidental DELETE?

No, not by itself. A standby applies the same redo the primary generated, so a committed accidental DELETE or a bad UPDATE is replicated to the standby within seconds, just like legitimate changes — replication cannot tell a mistake from intended work. Standbys protect against media and site failure, not logical or human error. The defenses for human error are Flashback (Query, Table, Drop, or Database) on the primary, and, at the standby level, a deliberately delayed apply (DELAY on the apply, or a snapshot standby) that gives you a window to intervene before the mistake reaches the standby. Treat replication and point-in-time undo as complementary, not interchangeable.

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.

---

Originally published at uptimearchitect.com.