Flashback Data Archive, Blockchain/History Concepts, Retention, Compliance, and Auditing

Learning outcomes

ServiceHub needs seven years of row-history evidence for selected financial/work-order records. Undo retention measured in hours or days is not an appropriate long-term archive. Oracle's feature is now documented as Flashback Time Travel (historically called Flashback Data Archive): it stores historical row lifespans in Oracle-managed history structures according to a configured retention period, while ordinary AS OF/VERSIONS syntax continues to query that history.

1. Flashback Time Travel stores row history outside ordinary undo lifetime

A Flashback Archive is a logical history repository associated with one or more tablespaces, a storage quota, and a retention period. Tables explicitly enabled for Flashback Archive have committed historical lifespans copied into Oracle-managed history structures. Queries continue to use familiar AS OF and VERSIONS syntax.

The feature was widely known as Flashback Data Archive; current 26ai development/licensing documentation uses Flashback Time Travel for the feature while SQL statements/views still use names such as CREATE FLASHBACK ARCHIVE.

2. Basic licensing is broad; optimization is a separate feature

The current 26ai licensing manual states that Basic Flashback Time Travel is in all offerings. The OPTIMIZE DATA history-table optimization uses Advanced Row/LOB Compression, deduplication, and tiering. That optimization is not available in Free; on EE/EE-ES it requires the Oracle Advanced Compression option.

3. Administrative setup for a small Free archive

The archive administrator needs FLASHBACK ARCHIVE ADMINISTER plus tablespace administration privileges. The application owner needs FLASHBACK ARCHIVE privilege on the specific archive to attach its table. Keep this privilege separate from the runtime ServiceHub account.

ALTER SESSION SET CONTAINER=FREEPDB1;CREATE FLASHBACK ARCHIVE sh16_fba  TABLESPACE users  QUOTA 10M  RETENTION 1 DAY  NO OPTIMIZE DATA;GRANT FLASHBACK ARCHIVE ON sh16_fbaTO servicehub_owner;

If USERS is not the correct lab tablespace or capacity/quota is insufficient, do not guess a new datafile location. Use a deliberately provisioned small tablespace or skip the executable archive creation and retain the design path.

4. Enable one table and generate retained history

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_history_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_history_case (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);ALTER TABLE servicehub_history_case  FLASHBACK ARCHIVE sh16_fba;INSERT INTO servicehub_history_case VALUES (1,'OPEN',100);COMMIT;VARIABLE history_scn NUMBERBEGIN :history_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; END;/UPDATE servicehub_history_caseSET status_code='CLOSED',    amount=175WHERE case_id=1;COMMIT;

SELECT case_id,status_code,amountFROM servicehub_history_case AS OF SCN :history_scnWHERE case_id=1;SELECT  versions_startscn,  versions_endscn,  versions_operation,  case_id,  status_code,  amountFROM servicehub_history_caseVERSIONS BETWEEN SCN :history_scn AND MAXVALUEWHERE case_id=1ORDER BY versions_startscn;

When the requested history is older than ordinary undo but still within the archive's retained history, Oracle can satisfy the query from Flashback Time Travel history structures.

5. Observe archive metadata and capacity

SELECT flashback_archive_name,retention_in_days,statusFROM user_flashback_archiveORDER BY flashback_archive_name;SELECT owner_name,table_name,flashback_archive_name,statusFROM user_flashback_archive_tablesORDER BY owner_name,table_name;

Dictionary-view availability/names depend on privilege scope; a DBA can use the corresponding DBA_* views. The important operational evidence is archive identity, tablespaces/quota, retention, tracked tables, and used capacity.

6. Archive capacity is part of write availability

If Flashback Archive storage fills, DML against tracked tables can fail or slow while Oracle cannot maintain required history. Oracle raises an out-of-space alert near the archive quota limit so administrators can add capacity or purge/adjust data according to policy.

7. 26ai blockchain history makes retained history cryptographically verifiable

Oracle AI Database 26ai can track a regular table with a blockchain Flashback Archive. Historical changes are stored in an Oracle-managed blockchain history table whose rows participate in cryptographic hash chains. Built-in blockchain verification can detect tampering with the history chain.

CREATE TABLE servicehub_chain_history (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL)BLOCKCHAIN FLASHBACK ARCHIVE sh16_fba;

This does not turn the application table into an immutable business ledger. The base table remains a normal user table; the archived history is protected by blockchain chaining. Flashback Database/PITR can still rewind the database, including blockchain tables, so external signed digests/independent evidence may be needed for stronger non-repudiation requirements.

8. Tamper evidence, audit identity, and compliance are different controls

No one row-history mechanism answers every compliance question. In particular, a historical value does not necessarily prove the real-world human identity responsible for the transaction.

9. Deliberately wrong: call Flashback Time Travel a backup/archive replacement

Flashback history lives in the database/storage lifecycle and can be affected by storage failure, administrative purge, database recovery, credentials, and application security. A long retention period does not provide an independent disaster copy. Keep RMAN/offsite recovery, restore drills, security auditing, and history retention as complementary controls.

10. Cleanup the disposable basic archive lab

ALTER TABLE servicehub_history_case  NO FLASHBACK ARCHIVE;DROP TABLE servicehub_history_case PURGE;

REVOKE FLASHBACK ARCHIVE ON sh16_fbaFROM servicehub_owner;DROP FLASHBACK ARCHIVE sh16_fba;

Dropping the archive removes its historical data but does not drop the underlying tablespace. Do not run archive purge/drop against production history without governance approval.

11. Production judgment and chapter close

Use ordinary undo-backed Flashback Query for short incident windows, Flashback Table/Drop for targeted online recovery, Flashback Database/GRPs for broader recent rewinds, and Flashback Time Travel for deliberately retained row history. Add blockchain history when cryptographic tamper evidence is required, but combine it with identity/audit controls and external evidence according to the compliance model.

The generation baseline remains 26ai RU 23.26.3. Basic Flashback Time Travel is broadly licensed; OPTIMIZE DATA is not available in Free and requires Advanced Compression on EE/EE-ES. The 26ai blockchain-history capability is current feature behavior, but production deployment still requires an offering/COMPATIBLE/licensing review. None of these controls replaces Chapter 15's independently stored, encrypted, tested backups and measured RPO/RTO.

Authoritative references

• Using Flashback Time Travel — Flashback Archive creation, tracking, retention and blockchain history
• CREATE FLASHBACK ARCHIVE — quota/retention/OPTIMIZE DATA semantics
• Managing Tables — Blockchain History — 26ai blockchain-protected FBA history
• 26ai New Features — blockchain Flashback Archive history enhancements
• Licensing Information — basic Time Travel and optimization licensing boundaries