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

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 DROPped (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).