# Oracle Dba Flashback Technologies

> USE THIS SKILL WHENEVER the user mentions Oracle Flashback in any form — Flashback Query / AS OF, Flashback Version Query / VERSIONS BETWEEN, Flashback Table, Flashback Drop / recyclebin / "undrop" a dropped table, Flashback Transaction / TRANSACTION_BACKOUT / backing out a committed transaction, restore points (normal or GUARANTEE FLASHBACK DATABASE), Flashback Data Archive / FDA / "Total Recall" / long-term row history, or Flashback Database / FLASHBACK DATABASE TO RESTORE POINT / rewinding the whole database — EVEN IF they do not name the exact task. Also triggers on undo-based "get the old rows back", "recover a dropped/truncated table without a restore", or errors ORA-01555 (snapshot too old), ORA-08180 (no snapshot at this time), ORA-38xxx (flashback/restore point), ORA-55xxx (flashback archive / transaction backout). Covers read-only inspection (Flashback Query/Version Query, recyclebin/FDA/restore-point listing), reversible T2 recovery (Flashback Table, Flashback Drop undrop, Flashback Transaction bac

- Skill: `shreyas70773/oracle-dba-flashback-technologies` (Agent Skill, multi-file: 28 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-flashback-technologies`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-flashback-technologies/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Productivity
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-flashback-technologies

---

# Oracle DBA - Flashback Technologies

Oracle's flashback family for **19c and 23ai** (EE; single_instance and Data Guard;
on-prem): undo-based inspection and rewind (Flashback Query / Version Query, Flashback
Table, Flashback Transaction), recyclebin recovery (Flashback Drop), restore points
(normal and GUARANTEE FLASHBACK DATABASE), Flashback Data Archive (FDA) for long-term
history, and the plan-only **Tier-3 FLASHBACK DATABASE** rewind. Everything self-executing
is **T1 (observe)** or **T2 (reversible, dry-run-default)**. **FLASHBACK DATABASE rewinds the
whole DB and is T3** — it lives only in `references/runbooks.md#flashback-database`, prints
commands for a human, and never self-executes.

> Decision order, least-disruptive first: **Flashback Query** (just look) -> **Flashback
> Table / Drop / Transaction** (one object/txn, online, T2) -> **TSPITR / RECOVER TABLE**
> (backup-and-recovery skill, T3) -> **FLASHBACK DATABASE** (whole DB, T3, last resort
> before full PITR). Always inspect with a read-only query before any mutating flashback.

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Flashback Query (AS OF) + Flashback Version Query (VERSIONS BETWEEN) | L2 | T1 | `scripts/flashback_query.sh` / `.ps1` + `sql/flashback_query.sql` | yes — read-only (no DML/DDL; emits row sample + context) |
| Recyclebin inventory (undrop candidates) | L2 | T1 | `scripts/flashback_drop.sh list` / `.ps1 list` + `sql/recyclebin_list.sql` | yes — read-only |
| Flashback Table (`FLASHBACK TABLE ... TO {SCN\|TIMESTAMP\|RESTORE POINT}`) | L2 | T2 | `scripts/flashback_table.sh` / `.ps1` + `sql/flashback_table.sql` | yes — enables row movement idempotently; re-flash to same point is harmless; pre-SCN logged |
| Flashback Drop (`FLASHBACK TABLE ... TO BEFORE DROP`) | L2 | T2 | `scripts/flashback_drop.sh undrop` / `.ps1 undrop` + `sql/flashback_drop.sql` | yes — already live + nothing in bin -> noop |
| Flashback Transaction — report (FLASHBACK_TRANSACTION_QUERY, UNDO_SQL preview) | L2 | T1 | `scripts/flashback_transaction.sh report` / `.ps1 report` + `sql/flashback_transaction_report.sql` | yes — read-only |
| Flashback Transaction — backout (`DBMS_FLASHBACK.TRANSACTION_BACKOUT`) | L2 | T2 | `scripts/flashback_transaction.sh backout` / `.ps1 backout` + `sql/flashback_transaction_backout.sql` | yes — nothing to compensate (ORA-55504/55505) -> noop; default ROLLS BACK after a preview (persists nothing) unless `--commit` |
| Restore point — list (+ flashback DB window) | L2 | T1 | `scripts/restore_point.sh list` / `.ps1 list` + `sql/restore_point_list.sql` | yes — read-only |
| Restore point — create (normal or `--guarantee`) | L2 | T2 | `scripts/restore_point.sh create` / `.ps1 create` + `sql/restore_point_create.sql` | yes — existing name -> noop |
| Restore point — drop | L2 | T2 | `scripts/restore_point.sh drop` / `.ps1 drop` + `sql/restore_point_drop.sql` | yes — missing name -> noop; needs `--execute` + `--confirm-drop` |
| Flashback Data Archive — status | L2 | T1 | `scripts/fda.sh status` / `.ps1 status` + `sql/fda_status.sql` | yes — read-only |
| Flashback Data Archive — setup (create/alter archive; enable on table) | L2 | T2 | `scripts/fda.sh setup` / `.ps1 setup` + `sql/fda_setup.sql` | yes — existing archive -> ALTER retention only; already-archived table -> noop |
| **Flashback Database** (`FLASHBACK DATABASE TO {RESTORE POINT\|SCN\|TIMESTAMP}`) | **L3** | **T3** | `references/runbooks.md#flashback-database` | no — rewinds whole DB; `OPEN RESETLOGS` makes a new incarnation |
| Enable/disable FLASHBACK DATABASE / size flashback logs (structural) | L3 | **T3** | `references/runbooks.md#enable-flashback` | n/a — DB-wide setting; refused by T2 scripts |
| Supplemental logging enable (prereq for Transaction Backout) | L2 | **T3** | `references/runbooks.md#supplemental-logging` | n/a — DB-wide redo change; deliberate DBA action, not self-executed |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** reachable and wallet-backed. All in-DB prechecks,
  postchecks, idempotency probes, and `sql/*.sql` run through it via `connect_db` /
  `Connect-Db`. Never SYS/SYSTEM — except inside the T3 runbook step that explicitly says
  `AS SYSDBA` (FLASHBACK DATABASE needs `STARTUP MOUNT` / `ALTER DATABASE`), supplied
  interactively by the operator.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere** (argv, env, files). `connect_db` rejects any `user/pass@db` argument with
  `ERR_SECRET` (8).
- **Privileges (granted to `dba_ai_conn` out of band, per site policy):** Flashback Query
  needs `SELECT`/`FLASHBACK`; Flashback Table needs `FLASHBACK`/`SELECT` + row movement;
  Flashback Drop reads `DBA_RECYCLEBIN`; Flashback Transaction needs `FLASHBACK` +
  `SELECT ANY TRANSACTION` + `EXECUTE ON DBMS_FLASHBACK` and **minimal supplemental
  logging ON**; restore points / FDA need the relevant admin privilege.
- **Capability prerequisites:** undo retention must still cover the target point for
  Flashback Query/Table/Transaction (else ORA-01555/ORA-08180 → use FDA or a backup-based
  recovery). A **guaranteed restore point** needs ARCHIVELOG + a configured FRA
  (`db_recovery_file_dest`) but NOT FLASHBACK DATABASE ON. **FLASHBACK DATABASE itself**
  needs FLASHBACK DATABASE ON *or* a guaranteed restore point pinned beforehand — that is
  the whole reason restore points exist here.
- **Helpers sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to `/var/log/oracle-dba/flashback/flashback.log`
  (Linux) / `C:/ProgramData/oracle-dba/logs/flashback/flashback.log` (Windows); skill
  segment `flashback`.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. Identifiers (owner/table/RP name/XID) are validated to block injection;
  AS OF / VERSIONS / target clauses are operator-supplied and passed verbatim.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2 scripts
default to `--dry-run`; pass `--execute` to act. See each script's `--help`.

### Flashback Query / Version Query (T1)
- **Trigger**: inspect a past state before any mutating flashback; "what did this row look
  like 15 minutes / SCN N ago"; capture a bad transaction's XID from VERSIONS_XID.
- **Precheck**: none (read-only). Reports undo retention, current SCN, FDA coverage.
- **Action**: `scripts/flashback_query.sh --owner HR --table EMPLOYEES --asof "TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)"` (or `--versions [--vstart ... --vend ...]`) / `.ps1`.
- **Postcheck**: n/a — emits the row sample + `KEYVAL current_scn=`.
- **Rollback note**: none — read-only.

### Flashback Table (T2)
- **Trigger**: a bad bulk DML/DDL hit one table and the data is still within undo (or the
  table is FDA-enabled); rewind that table only.
- **Precheck**: table exists/reachable. SQL enables row movement idempotently.
- **Action**: `scripts/flashback_table.sh --owner HR --table EMPLOYEES --target "TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)" --execute` / `.ps1` (target may be `SCN n` / `RESTORE POINT name`).
- **Postcheck**: row movement enabled; post-flashback row sample printed.
- **Rollback note**: the wrapper logs `pre_flashback_scn`; re-run with that SCN to flash
  back to the prior state while undo still holds it. Beyond undo -> T3 recovery.

### Flashback Drop — list / undrop (T1 / T2)
- **Trigger**: a table was `DROP`ped (not purged) and must come back; recyclebin is ON.
- **Precheck (undrop)**: object is in the recyclebin OR already live (idempotent noop).
- **Action**: `scripts/flashback_drop.sh list [--table T]` (T1); `scripts/flashback_drop.sh undrop --table EMPLOYEES [--owner HR] [--rename NEW] --execute` (T2) / `.ps1`.
- **Postcheck**: the (possibly renamed) object is live in `DBA_TABLES`.
- **Rollback note**: reversible — `DROP` it again. Indexes/constraints return with `BIN$`
  names; rename if standards require.

### Flashback Transaction — report / backout (T1 / T2)
- **Trigger**: a single committed transaction must be reversed without rewinding the DB.
- **Precheck (backout)**: minimal supplemental logging ON (else stop with hint to
  `#supplemental-logging`). Report subcommand previews the exact UNDO_SQL first.
- **Action**: `scripts/flashback_transaction.sh report --xid <XID>` (T1); `scripts/flashback_transaction.sh backout --xid <XID> [--option NOCASCADE|CASCADE|NOCASCADE_FORCE|NONCONFLICT_ONLY] [--commit] --execute` (T2) / `.ps1`.
- **Postcheck**: with `--commit`, a backout-state row exists in `DBA_FLASHBACK_TXN_STATE` for
  the XID. Without `--commit` (preview) nothing is persisted, so there is nothing durable to
  verify — success is the report being produced.
- **Rollback note**: each invocation is its own SQLcl session, so "leave it uncommitted for a
  later run" is impossible (uncommitted DML dies with the session). Therefore the default
  generates the backout, prints the `DBA_FLASHBACK_TXN_STATE`/UNDO report, then **ROLLS BACK**
  (persists nothing — fully reversible); re-run with `--commit` to apply it for real.
  `EXITCOMMIT` is forced OFF so SQLcl cannot silently commit a preview on exit. Default
  `NOCASCADE` refuses if dependent transactions exist.

### Restore point — list / create / drop (T1 / T2)
- **Trigger**: pin a known-good point before a risky change (`create [--guarantee]`); audit
  flashback-window/FRA pressure (`list`); release pinned space (`drop`).
- **Precheck**: `create --guarantee` requires ARCHIVELOG + configured FRA (a normal RP only
  needs the DB open).
- **Action**: `scripts/restore_point.sh create --name BEFORE_PATCH --guarantee --execute`; `... list` (T1); `... drop --name BEFORE_PATCH --confirm-drop --execute` / `.ps1`.
- **Postcheck**: `sql/restore_point_list.sql` shows expected presence/absence in `V$RESTORE_POINT`.
- **Rollback note**: a guaranteed RP IS the anchor for `FLASHBACK DATABASE TO RESTORE POINT`
  (that rewind is T3, `#flashback-database`). Dropping a guaranteed RP irreversibly frees
  pinned flashback space.

### Flashback Data Archive — status / setup (T1 / T2)
- **Trigger**: enable long-term, tamper-resistant row history beyond undo (compliance,
  "as-of last quarter" queries) on chosen tables.
- **Precheck (setup)**: backing tablespace exists.
- **Action**: `scripts/fda.sh status` (T1); `scripts/fda.sh setup --name FBA_7Y --tbs FDA_TBS --retain 2555 [--quota MB] [--default] [--owner HR --table EMPLOYEES] --execute` (T2) / `.ps1`.
- **Postcheck**: archive present in `DBA_FLASHBACK_ARCHIVE`.
- **Rollback note**: reversible — `ALTER TABLE ... NO FLASHBACK ARCHIVE` / `DROP FLASHBACK
  ARCHIVE`. Enabling captures history forward only; it does not rewrite existing rows.

## Tier-3 runbooks

The **whole-database rewind and its structural prerequisites** are Tier 3 and live in
**`references/runbooks.md`**:
- `#flashback-database` — `FLASHBACK DATABASE TO {RESTORE POINT | SCN | TIMESTAMP}` (rewinds
  the entire DB; `OPEN RESETLOGS`; new incarnation; standby/DG coordination).
- `#enable-flashback` — enable/disable FLASHBACK DATABASE and size flashback logs (DB-wide
  structural change).
- `#supplemental-logging` — enable minimal + PK supplemental logging (DB-wide redo change;
  the prerequisite the Transaction Backout precheck enforces).

These runbooks **PRINT exact SQL/RMAN commands for a human operator and NEVER self-execute.**
They are gated by `require_approval_token` / `Require-ApprovalToken`: an automated agent's
only sanctioned action is to print the relevant section and stop with `ERR_APPROVAL` (6)
until a human supplies a real change ticket/token (`--token <TICKET>` or
`ODB_APPROVAL_TOKEN`; placeholders are rejected). The T2 scripts deliberately refuse the
whole-DB rewind and the DB-wide structural toggles and point to the runbook instead. SYSDBA
is a T3-only, interactively-supplied exception. When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas, and `cron/` for the
schedulable T1/T2 jobs (no recovery action is ever scheduled).