Ensuring Data Protection Using Oracle Flashback Features - Part 2

Introduction

In the previous article we have reviewed the basics of Oracle Data Protection, and explored Oracle Data Protection solutions. We also covered the data protection objectives which are measured by the RPO (Recovery Point Objective) and RTO (Recovery Time Objective).
In this article we will review the first Oracle Flashback feature which was introduced in Oracle 9i, named "Flashback Query" and we will see how this feature works "behind the scenes". Oracle Flashback Query allows querying a table's data as of a specific point in the past by providing either a TIMESTAMP or an SCN.

Demonstration

In the below first step, we will create a sample table named “EMP” with a single row: 

SQL> CREATE TABLE EMP (ID NUMBER, NAME VARCHAR2(20));
Table created.

SQL> insert into EMP values (1, 'DAVID');
1 row created.

SQL> select * from EMP;
        ID NAME
---------- --------------------
         1 DAVID

SQL> commit;
Commit complete.

Now, we will determine the current SCN and time:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
  476372816

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') CURRENT_TIME from dual;
CURRENT_TIME
-------------------
2016-01-04 14:37:12

In the final step, we will update the row, and by using Flashback Query we will be able to view the contents of the table prior to the data modifications:

SQL> update emp set name = 'ROBERT';
1 row updated.

SQL> select * from EMP;
        ID NAME
---------- --------------------
         1 ROBERT

SQL> commit;
Commit complete.

SQL> select * from EMP as of scn 476372816;
        ID NAME
---------- --------------------
         1 DAVID

SQL> select * from EMP as of TIMESTAMP  
TO_TIMESTAMP('2016-01-04 14:37:12', 'YYYY-MM-DD HH24:MI:SS');
        ID NAME
---------- --------------------
         1 DAVID

This feature can be also very useful for investigating the contents of the table in a specific point in time in the past. It can also be used to restore the value of a row, a set of rows, or even the entire table. In the following example, an update sets the name of EMPLOYEE with ID #1 to be as of a specific time point in the past:

SQL> update EMP set name =
         (select name 
          from EMP 
   as of timestamp TO_TIMESTAMP('2016-01-04 14:37:12', 'YYYY-MM-DD HH24:MI:SS') WHERE  ID=1) 
    WHERE ID=1;
1 row updated.

SQL> select * from EMP;
        ID NAME
---------- --------------------
         1 DAVID

It is possible to specify a relative time by subtracting the current timestamp using the INTERVAL clause, as follows:

select * from emp
     as of timestamp (SYSTIMESTAMP - INTERVAL '60' MINUTE); 

In the following example, an INSERT AS SELECT command is using flashback query in order to insert all the rows that existed in “emp” table 2 hours ago:

insert into emp
   (select *
      from emp as of timestamp (SYSTIMESTAMP - INTERVAL '2' HOUR));

Note: It is possible to convert between SCN to TIMESTAMP using the SCN_TO_TIMESTAMP function

How does the Flashback Query feature work?

The Flashback Query feature uses the contents of the undo tablespace. The undo tablespace is a key component in Oracle Databases. It consists of undo segments which hold the "before" images of the data that has been changed by users running transactions. The undo is essential for rollback operations, data concurrency and read consistency. 

In order to use Flashback Query, the instance must have an automatic undo management by setting the UNDO_MANAGEMENT initialization parameter to be TRUE (default since version 11gR1). It is also important to set a proper value for the UNDO_DETENTION parameter. The UNDO_RETENTION specifies the low threshold (in seconds) for the undo retention period (defaults to 900, i.e. 15 minutes). It is important to bear in mind the different behaviors of this parameter in a fixed-size undo tablespace vs. autoextend undo tablespace.

Fixed-Size Undo Tablespace

For fixed-size undo tablespaces, the UNDO_RETENTION parameter is being ignored and Oracle automatically tunes for the maximum possible undo retention period based on the undo tablespace size and undo usage history.

Autoextend Undo Tablespace

For auto extend undo tablespace, the UNDO_RETENTION parameter specifies the minimum retention period Oracle will attempt to honor. When space in the undo tablespace becomes low (due to running transactions which generate undo records) Oracle will increase the tablespace size (up to the MAXSIZE limit). Once it will reach the upper limit of the MAXSIZE, it will begin to overwrite unexpired undo information; therefore, the retention period defined in the UNDO_RETENTION period is not guaranteed. This is why the actual undo retention period might be lower or even higher than the one defined in the UNDO_RETENTION parameter. The actual undo retention period can be obtained by querying the TUNED_UNDORETENTION column in V$UNDOSTAT dynamic performance view. 

Note: It is possible to specify the RETENTION GUARANTEE clause in the CREATE UNDO TABLESPACE or ALTER TABLESPACE commands, and then Oracle will never overwrite unexpired undo data even if it means that transactions will fail due to lack of space in the undo tablespace.