Autonomous Flashback Time Travel

In modern data platforms, the ability to analyze data is fundamental to construct targeted offers for customers. Today, the volume of information grows exponentially, encompassing various data types such as transactional, spatial, graph, NoSQL, JSON, Parquet, and more. It is crucial to aggregate and extract relevant information to build the right offers.

Having all these diverse data models together is highly beneficial, but it is equally essential to have the capability to visualize information at specific points in time, even after modifications have been made. Access to all states of a transaction, in essence, allows us to “time travel” and examine how the data appeared at precise previous instances, along with the operations executed.

Let’s explore a really simple example to gain a clearer understanding. Suppose we have a customer (Customer X) , who purchases four phone contracts (A/B/C/D) and makes subsequent recharges on these contracts at different times (A=50; A=50 / B=20; B=10; B=10; B=10 / C=5). At a later point, the customer decides to terminate two of these contracts (C/D). The operations executed by CustomerX could be as follows:

CustomerX: insert contract A
CustomerX: insert contract B
CustomerX: insert contract C
CustomerX: insert contract D
CustomerX: update contract A=50
CustomerX: update contract A=50
CustomerX: update contract B=20
CustomerX: update contract B=10
CustomerX: update contract B=10
CustomerX: update contract B=10
CustomerX: update contract C=5
CustomerX: delete contract C
CustomerX: delete contract D

Observing the current situation regarding Customer X, we find that the customer owns two contracts with the following situation:

CustomerX: contract A=100
CustomerX: contract B=50

At first glance, it may appear that contract A is the reference contract for Customer X.

However, by analyzing all the states of this customer and being able to “time travel” within specific time windows, we could discover, for example, that contract B experienced more recharges and that these recharges occurred during specific periods, for example in the summer season. Armed with this insight, we could tailor a specific offer for that customer/contract combination. Additionally, knowing that the customer was not just a owner of two contracts but had four contracts and terminated two over time, further informs our decisions.

This example demonstrates the significance of historical analysis in understanding trends, patterns, clusters, and changes, both at an individual customer level and across all customers of an operator. It is a vital resource for various scenarios, including data recovery, auditing, compliance, and data correction.

In many cases, time travel is implemented using data or transaction logs, which can result in complex architectures and make reconstructing historical information and the current state demanding tasks. It requires meticulous planning, analysis, computational resources, storage etc etc.

Oracle Database FlashBack Data Archive

However, Oracle Database, known for its multi-model capabilities and ability to manage and combine different data types, introduced the “Flashback Data Archive” (FDA) feature in version 11g (2013). This allows “time travel” on data, enabling access to historical data snapshots and providing an invaluable tool for analysis and decision-making.

Over subsequent releases, FDA has evolved with new functionalities and is available in various Oracle Database solutions, from on-premise to cloud, including hybrid and multi-cloud.

If you are licensed for the advanced compression option, you can take advantage of the OPTIMIZE DATA clause when using flashback data archive. Using optimization enables Advanced Row Compression, Advanced LOB Compression, Advanced LOB Deduplication, segment-level compression tiering, and row-level compression tiering for the specified FDA.

Autonomous Flashback Time Travel

Today this features is also in Oracle Autonomous Database, called “Flashback Time Travel” and is fully managed, like all other features of Autonomous Database.

TEST CASE

Let’s now explore how to apply this functionality in the simple case we presented earlier.

First of all we have to give the privilege to the user that we will use for the test

grant flashback archive on flashback_archive to TEST_FDA;

Create table with the option FLASHBACK ARCHIVE

CREATE TABLE Customer (
  id               VARCHAR2(50),
  Contract_name    VARCHAR2(50),
  Value            NUMBER
) FLASHBACK ARCHIVE;  

Now, we’ll simulate the operations that would be recorded for the customer. We’ll arrange them in order just to make it easier to read.

INSERT INTO Customer VALUES ('Customer X', 'Contract A' ,0);
commit;
INSERT INTO Customer VALUES ('Customer X', 'Contract B' ,0);
commit;
INSERT INTO Customer VALUES ('Customer X', 'Contract C' ,0);
commit;
INSERT INTO Customer VALUES ('Customer X', 'Contract D' ,0);
commit;



UPDATE Customer SET    Value = '100'
WHERE  id = 'Customer X' and Contract_name='Contract A' ;
commit;

UPDATE Customer SET    Value = '20'
WHERE  id = 'Customer X' and Contract_name='Contract B' ;
commit;
UPDATE Customer SET    Value = '30'
WHERE  id = 'Customer X' and Contract_name='Contract B' ;
commit;
UPDATE Customer SET    Value = '40'
WHERE  id = 'Customer X' and Contract_name='Contract B' ;
commit;
UPDATE Customer SET    Value = '50'
WHERE  id = 'Customer X' and Contract_name='Contract B' ;
commit;

UPDATE Customer SET    Value = '5'
WHERE  id = 'Customer X' and Contract_name='Contract C' ;
commit;


DELETE Customer WHERE  
id = 'Customer X' and Contract_name='Contract C';
commit;

DELETE Customer WHERE  
id = 'Customer X' and Contract_name='Contract D';
commit;

If we take a look at the status of this customer in the database, we will see the following information.

However, this doesn’t fully reveal the entire history of this customer over time. But we can access all this information using the query below, leveraging the time travel feature managed by Autonomous Database.

SELECT id,
       Contract_name,
       Value,
---info for Flashback Time Travel
       versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation
FROM   CUSTOMER
       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(13/24/60) AND SYSTIMESTAMP
ORDER BY versions_startscn; 

So as you can see, you can have both information without any complex architectures or spend a lot of effort to manage it.

I hope it’s useful and … have fun

The post is made for educational purposes and the demo surely could be written better, all checks for all commands haven’t been run; Please use the public documentations.

link https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/flashback-time-travel-autononomous.html#GUID-A98E1F8B-FAE4-4FFF-955D-3A0E5F8EBC4A

Ciao.
Luca

Disclaimer

The views expressed in this paper are my own and do not necessarily reflect the views of Oracle.