Recovering a 60-million-row DELETE with a nearly full FRA
HRPROD is Oracle 19c in ARCHIVELOG mode. DB_RECOVERY_FILE_DEST is 500 GB with RMAN retention RECOVERY WINDOW OF 3 DAYS, backup compression and control file autobackup ON. A compressed level-0 backup of the 300 GB database is 140 GB and runs nightly at 01:00; archived log generation averages 40 GB per day, more on payroll days. FLASHBACK DATABASE was enabled six days ago with DB_FLASHBACK_RETENTION_TARGET=1440. At 02:10 last night a developer ran DELETE FROM PAY_DETAIL on a 60-million-row, 12 GB table and committed; it was reported at 09:00. Four indexes on the table total 4 GB, the table has no LOBs, and V$RECOVERY_FILE_DEST reports 96% used. The undo tablespace is 16 GB with retention guarantee off. The business wants the rows back before the 11:00 payroll run.
1. Can FLASHBACK TABLE PAY_DETAIL (or a flashback query) recover the rows? State what you would check before promising the business anything.
Read the worked answer
Probably not, and you must verify before committing to a time. FLASHBACK TABLE and flashback queries reconstruct rows from undo, so they require undo that covers the time before 02:10 to still exist. With guarantee off and a 16 GB undo tablespace, the undo from a 12 GB delete will have been recycled long before 09:00 - likely within minutes of the commit.
Preconditions to check:
SELECT table_name, row_movement FROM dba_tables WHERE table_name='PAY_DETAIL';
SELECT maxquerylen, tuned_undoretention, begin_time
FROM v$undostat WHERE begin_time > SYSDATE-1 ORDER BY begin_time;
SELECT start_time, used_ublk FROM v$transaction;
SELECT COUNT(*) FROM pay_detail AS OF TIMESTAMP
TO_TIMESTAMP('2024-06-13 02:00:00','YYYY-MM-DD HH24:MI:SS');
The AS OF count is the cheap, decisive test: if it returns ORA-01555, undo for that point is gone and FLASHBACK TABLE cannot work. Row movement must be enabled before a table flashback. Note also that any structural DDL on the table after 02:10 (column added/dropped, truncate, partition change) blocks table flashback regardless of undo.
2. If flashback table is not viable, evaluate FLASHBACK DATABASE to 02:05. What must exist, what does 96% FRA utilisation tell you, and what would make it fail anyway?
Read the worked answer
Requirements: V$DATABASE.FLASHBACK_ON = YES; flashback logs reaching back past 02:05 (V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME); and no operation flashback cannot undo in the interval - adding, resizing or renaming a datafile, adding or dropping a tablespace, shrinking a datafile, or restoring a control file.
The 96% FRA is the warning signal. When the recovery area fills, Oracle reclaims space by deleting flashback logs first, because they are re-creatable and not part of the backup chain. So DB_FLASHBACK_RETENTION_TARGET=1440 is an intention, not a guarantee. The arithmetic shows the pressure: 140 GB x 2 retained level-0 backups = 280 GB, three days of archived logs at 40 GB/day = 120 GB, and a 24-hour flashback target needs tens of GB more; 500 GB cannot hold all three, so OLDEST_FLASHBACK_TIME is very likely much later than 02:05 and the flashback target is unreachable.
Even if it were reachable, FLASHBACK DATABASE rewinds the whole database: every other transaction between 02:05 and 09:00 is lost, and you must OPEN RESETLOGS afterwards, which invalidates the existing backups until a new one is taken. For one damaged table, that is almost never the right trade.
3. Describe the fallback recovery path, what the 01:00 backup is worth, and the preconditions and risks.
Read the worked answer
Tablespace point-in-time recovery restores only PAY_TBS and leaves the rest of the database current:
RMAN> RECOVER TABLESPACE pay_tbs
UNTIL TIME "TO_DATE('2024-06-13 02:05:00','YYYY-MM-DD HH24:MI:SS')"
AUXILIARY DESTINATION '/u02/aux'
DATAPUMP DESTINATION '/u02/dp'
DUMP FILE 'pay_tbs_restore.dmp'
NOTABLE IMPORT;
Then import the recovered table back into the live schema (impdp ... REMAP_TABLESPACE).
The 01:00 backup is an ideal base: it was taken 70 minutes before the delete. TSPITR needs that backup plus archived logs from its SCN to 02:05 - roughly one hour of redo, comfortably inside the 3-day window, so the logs are still on disk. That is precisely why this path is viable.
Preconditions and risks: free FRA space first (delete obsolete backups or raise DB_RECOVERY_FILE_DEST_SIZE), because the auxiliary instance needs room; PAY_TBS must be self-contained - no object in it may depend on objects in another tablespace that would be left behind, so check partitions, foreign keys and whether the four indexes live in PAY_TBS (if not, plan rebuilds); and everything written to the table after 02:05 still disappears. Afterwards fix the policy gap: named developer accounts, audit on production DML, and an undo/flashback retention that matches the recovery expectation.