Flashback time travel vs Flash back Data Archive

                      Flashback time travel

                                     vs 

         ♨️Flash back Data Archive♨️

                 Oracle 19c and newer

Sometimes DBA need to restore/recover  important data set in production to avoiding data lost or deletion by user interference, and human errors or any problems.

As examples consider a user did some wrong delete on a base table, at this situation if you alert quickly you have a chance to flashback deleted rows on the table by using undo data, for example:

SQL>insert into base_table

Select * from table1  AS OF TIMESTAMP TO_TIMESTAMP('224-02-12 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

Or 

SQL>insert into base_table

Select * from table1  AS OF SCN 123456;

But this chance is depending on sizing of undo_retention parameter and undo tablespace.

that the UNDO_RETENTION is not guaranteed by default so the fact that the UNDO_RETENTION is 900 seconds (=15 minutes) doesn't necessarily mean that you can query data as of 15 minutes ago. Another thing that you should also take into account is the UNDO_RETENTION  behaviour in autoextend undo tabelspace vs. fixed-size undo tablespace because in fixed-size undo tablespace the UNDO_RETENTION is being ignored by Oracle so it's basically meaningless (unless retention guarantee is enabled).

So you can consider a bigger retention for your undo, but if you want to protect a big table in production same as this way, this solution is not applicable (very large undo for recovering inadvertent deleted data is not good solution)

Solution:

Use Flashback Data Archive (FDA).

This solution protects your specific tables and also can be used  for auditing and tracking changes on the tables in a isolated and separated tablespace without need Undo tablespace!

So if we have big or very big tables also we can used this feature and we can enable or disable on demand any time.

The FDA is available feom 11g but has some improvements on 19c and higher.

In Oracle 19c, they rebranded the feature called “Flashback Time Travel”. The Flashback Time travel is a nothing but a combination Flashback Data archive and Flashback Query AS OF and Versions. 

Use Flashback Time Travel to automatically track and archive historical versions of changes to tables enabled for flashback archive, ensuring SQL-level access to the versions of database objects without getting a snapshot-too-old error. 

The Historical data is key for business decisions and having reliable data is very important for organizations. There are frequent implications for both financial and legal data for organizations and having a secure and accurate history of change of data is crucial for business. Oracle Flashback technology offers significant benefits for database management and recovery to meet the organizational needs. 

Using Flashback Time Travel in Orcale 19c and higher 

Flashback Time Travel provides the ability to track and store transactional changes to a table over its lifetime. It is useful for compliance with record stage policies and audit reports.

A Flashback Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Archives. If you are logged on as SYSDBA, you can specify a default Flashback Archive for the system. A Flashback Archive is configured with retention time. Data archived in the Flashback Archive is retained for the retention time specified when the Flashback Archive was created.

Creating a Flashback Archive

You can create a Flashback Archive with the CREATEFLASHBACK ARCHIVE statement.

Create a Flashback Archive with the CREATEFLASHBACK ARCHIVE statement, specifying:

• Name of the Flashback Archive
• Name of the first tablespace of the Flashback Archive
• (Optional) Maximum amount of space that the Flashback Archive can use in the first tablespace
  The default is unlimited. Unless your space quota on the first tablespace is also unlimited, you must specify this value; otherwise, error ORA-55621 occurs.
• Retention time (number of days that Flashback Archive data for the table is guaranteed to be stored)
• (Optional) Whether to optimize the storage of data in the history tables maintained in the Flashback Archive, using [NO]OPTIMIZE DATA.
  The default is NO OPTIMIZE DATA.

If you are logged on as SYSDBA, you can also specify that this is the default Flashback Archive for the system. If you omit this option, you can still make this Flashback Archive as default at a later stage.

Oracle recommends that all users who must use Flashback Archive have unlimited quota on the Flashback Archive tablespace; however, if this is not the case, you must grant sufficient quota on that tablespace to those users.

Examples

• Create a default Flashback Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data are retained for one year:
  
  SQL> CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1  QUOTA 10G RETENTION 1 YEAR;
  
• Create a Flashback Archive named fa2 that uses tablespace tbs2, whose data are retained for two years:
  
  SQL> CREATE FLASHBACK ARCHIVE fa2 TABLESPACE tbs2 RETENTION 2 YEAR;

Examining and Restoring Past Data

By defining FDA or FTT, retrieving data in a older time is same as Flashback method such as:

SQL> Select * from table1  AS OF TIMESTAMP TO_TIMESTAMP('2024-02-12 13:30:12', 'YYYY-MM-DD HH24:MI:SS');

Suppose that you discover at 12:30 PM that the row for employee KAMRANI was deleted from the employees table, and you know that at 9:30AM the data for KAMRANI was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.

Example 1:

Retrieves the state of the record for Kamrani at 9:30AM, April 4, 2024:

SELECT * FROM employees

AS OF TIMESTAMP

TO_TIMESTAMP('2024-02-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE last_name = 'Kamrani ';

Example 2 Restores Kamrani's information to the employees table:

INSERT INTO employees (

  SELECT * FROM employees

  AS OF TIMESTAMP

  TO_TIMESTAMP('2024-02-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

  WHERE last_name = 'Kamrani'

);

Benefits of Flashback data archive:

Uses of Oracle Flashback Query include:

• Recovering lost data or undoing incorrect, committed changes.
  For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
• Comparing current data with the corresponding data at an earlier time.
  For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
• Checking the state of transactional data at a particular time.
  For example, you can verify the account balance of a certain day.
• Selecting data that was valid at a particular time or at any time within a user-defined valid time period.
  For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. 
• Simplifying application design by removing the need to store some kinds of temporal data.
  Oracle Flashback Query lets you retrieve past data directly from the database.
• Applying packaged applications, such as report generation tools, to past data.
• Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

Guidelines for Oracle Flashback Query

• You can specify or omit the AS OF clause for each table and specify different times for different tables.
  
• Note: If a table is a Flashback Time Travel and you specify a time for it that is earlier than its creation time, the query returns zero rows for that table, rather than causing an error.
• You can use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query.
• To use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OFclause inside an INSERT or CREATE TABLEAS SELECT statement.
• If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp. 
• You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
  If you specify a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For example:
  
  CREATE VIEW hour_ago AS SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  
  SYSTIMESTAMP refers to the time zone of the database host environment.
• You can use the AS OF clause in self-joins, or in set operations such as INTERSECTand MINUS, to extract or compare data from two different times.
  You can store the results by preceding Oracle Flashback Query with a CREATETABLE AS SELECT or INSERT INTO TABLESELECT statement. For example, this query reinserts into table employees the rows that existed an hour ago:
  
  INSERT INTO employees  (SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE) MINUS SELECT * FROM employees;
  
  SYSTIMESTAMP refers to the time zone of the database host environment.

• You can use the AS OF clause in queries to check for data that was valid at a particular time.

Using Oracle Flashback Version Query

Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

Note:After executing a CREATE TABLE statement, wait at least 15 seconds to commit any transactions, to ensure that Oracle Flashback Version Query reflects those transactions.

Specify Oracle Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is either:

VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end

where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end). 

or:

VERSIONS PERIOD FOR user_valid_time [ BETWEEN TIMESTAMP start AND end ]

where user_valid_time refers to the user-specified valid time period.

Oracle Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudocolumns of metadata about the row version, which can reveal when and how a particular change (perhaps erroneous) occurred to your database.

For example, this output indicates that the salary was 10243 from September 9, 2023, included, to November 25, 2024, excluded.

VERSIONS_START_TIME     VERSIONS_END_TIME     SALARY

-----------------     -----------------     ------

09-SEP-2"24             25-NOV-2024           10243

Here is a typical use of Oracle Flashback Version Query:

SELECT versions_startscn, versions_starttime,

       versions_endscn, versions_endtime,

       versions_xid, versions_operation,

       last_name, salary

  FROM employees

  VERSIONS BETWEEN TIMESTAMP

      TO_TIMESTAMP('2024-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')

  AND TO_TIMESTAMP('2024-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')

  WHERE first_name = 'John';

You can use VERSIONS_XID with Oracle Flashback Transaction Query to locate this transaction's metadata, including the SQL required to undo the row change and the user responsible for the change.

Flashback Version Query allows index-only access only with IOTs (index-organized tables), but index fast full scan is not allowed.

Using Flashback Time Travel

Flashback Time Travel provides the ability to track and store transactional changes to a table over its lifetime. It is useful for compliance with record stage policies and audit reports.

A Flashback Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Archives. If you are logged on as SYSDBA, you can specify a default Flashback Archive for the system. A Flashback Archive is configured with retention time. Data archived in the Flashback Archive is retained for the retention time specified when the Flashback Archive was created.

By default, Flashback Archive is not enabled for any tables. You can enable Flashback Archive for a table if all of these conditions are true:

• You have the FLASHBACK ARCHIVE object privilege on the Flashback Archive to use for that table.
• The table is not nested, temporary, remote, or external.
• The table contains neither LONG nor nested columns.
• The table does not use any of these Flashback Archive reserved words as column names:
• STARTSCN
• ENDSCN
• RID
• XID
• OP
• OPERATION

You cannot enable Flashback Archive on a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, or non-table object.

After Flashback Archive is enabled for a table, you can disable it only if you either have the FLASHBACKARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

When choosing a Flashback Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Archives on which you have the FLASHBACK ARCHIVEobject privilege.

Effective with Oracle Database 12c Release 1 (12.1.0.1), Flashback Archive is enhanced to include the following:

• User context tracking. The metadata information for tracking transactions can include (if the feature is enabled) the user context, which makes it easier to determine which user made which changes to a table.
  To set the user context level (determining how much user context is to be saved), use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure. To access the context information, use the DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT function.
• Database hardening. This feature enables you to associate a set of tables together in an "application", and then enable Flashback Archive on all those tables with a single command. Also, database hardening enables you to lock all the tables with a single command, preventing any DML on those tables until they are subsequently unlocked. Database hardening is designed to make it easier to use Flashback Time Travel to track and protect the security-sensitive tables for an application.

DDL Statements on Tables Enabled for Flashback Archive

Flashback Archive supports only these DDL statements:

• ALTER TABLE statement that does any of the following:
• Adds, drops, renames, or modifies a column
• Adds, drops, or renames a constraint
• Drops or truncates a partition or subpartition operation
• TRUNCATE TABLE statement
• RENAME statement that renames a table

Flashback Archive does not support DDL statements that move, split, merge, or coalesce partitions or subpartitions, move tables, or convert LONG columns to LOB columns.

For example, the following DDL statements cause error ORA-55610 when used on a table enabled for Flashback Archive:

• ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
• ALTER TABLE statement that moves or exchanges a partition or subpartition operation
• DROP TABLE statement

If you must use unsupported DDL statements on a table enabled for Flashback Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBAprocedure to disassociate the base table from its Flashback Archive. To reassociate the Flashback Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBAprocedure. Also, to drop a table enabled for Flashback Archive, you must first disable Flashback Archive on the table by using the ALTER TABLE ... NO FLASHBACK ARCHIVE clause.

Consideration:

Before configuration of FDA you should consider following:

Allocate sufficient storage to handle retention period 

•For Faster access create indexes on the History tables

•Ideal approach is to create FDA on tables with primary key

•Enable FDA on tables which are important for business not on all tables in a schema

Limitations:

You can enable flashback data archive, but you must meet below conditions:

You cannot enable Flashback Archive on a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, or non-table object.

•The table is not nested, remote, external, or temporary 

•The table has neither nested column or LONG column

•The table does not any reserved words as column names such as START SCN, END SCN, OP, OPERATION, XID, RID

•The user should have FLASHBACK ARCHIVE object privilege.

Conclusion:

The historical data is key for business decisions and having reliable data is very important for organizations. Flashback Data Archive and Time Travel provides centralized historical data store by tracking changes in a secure and efficient way, which helps organization with compliance, audit, historical reporting, selective data recovery and restore lost data cause of human interference 

and many other capabilities, but this feature have some limitations aslo.

For more information see:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html

Sincerely,

Alireza Kamrani.