Ensuring Data Protection Using Oracle Flashback Features - Part 4

Introduction

In my previous article we have reviewed Oracle 10g Flashback features. We started with the Flashback Query enhancements (Flashback Version Query, Flashback Transaction Query, Flashback Table) and continued with additional flashback enhancements (Flashback Drop and Flashback Database).
In this part, we will review Oracle 11g and 12c Flashback Features.

Oracle 11gR1 - Flashback Transaction

In the previous part, when we reviewed “Flashback Version Query” section we have demonstrated how to view historical changes of records in a table and also how find the associated Transaction ID (XID) per each version of record in the table.  In the “Flashback Transaction Query” we have demonstrated how we can also view the undo statement for the selected transaction. Starting from Oracle 11g, using the Flashback Transaction feature, we can even take these 2 features one step further by rolling-back the changes made by a transaction and its dependent transactions (optionally).  In the following example, a sample table is created. Afterwards, the first transaction will insert a single record, and then a second transaction updates the record. In the last step of this demo we will perform a rollback for the first transaction:

SQL> CREATE TABLE DEPARTMENTS
    (
    DEPT_ID   NUMBER,
    Name      VARCHAR2 (20),
    CONSTRAINT id_pk PRIMARY KEY (DEPT_ID)
     );
Table created.

SQL> insert into DEPARTMENTS values (1, 'SALES');
1 row created.

SQL> commit;
Commit complete.

SQL> update DEPARTMENTS set name = 'HR' where dept_id = 1;
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from DEPARTMENTS;

   DEPT_ID NAME
---------- --------------------
         1 HR

SQL> SELECT versions_starttime, versions_endtime,
           versions_xid, versions_operation, dept_id, name
      FROM   DEPARTMENTS
    VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V    DEPT_ID NAME
------------------------- ------------------------- ---------------- - ---------- ------
28-JAN-16 06.18.10 PM     28-JAN-16 06.19.01 PM     000A001A0000BF39 I          1 SALES
28-JAN-16 06.19.01 PM                               000800110000648B U          1 HR


SQL> EXECUTE DBMS_FLASHBACK.transaction_backout(numtxns=>1, xids=>xid_array('000A001A0000BF39'));

ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 2

The reason that the “ORA-55504: Transaction conflicts in NOCASCADE mode” error has been raised is because the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure has a parameter named “options” which defaults to NOCASCADE, meaning that if a dependent transaction has been found, Oracle will raise an error. In our demonstration, the second transaction that updates the row depends on the first transaction that inserts the row; therefore, Oracle raises an error. We can tell Oracle to rollback the dependent transactions using the CASCADE value for the “options” parameter, as follows:

SQL> BEGIN 
  DBMS_FLASHBACK.transaction_backout (numtxns=> 1,
                                      xids    => xid_array('000A001A0000BF39'),
                                      options => DBMS_FLASHBACK.cascade);
END;
PL/SQL procedure successfully completed.

SQL> select * from DEPARTMENTS;
no rows selected

Note that supplemental logging for primary key columns must be enabled in order to use Flashback Transaction:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Database altered. 

Oracle 11gR1 - Flashback Data Archive

As mentioned, most of the Flashback Features (including: Flashback Version Query, Flashback Transaction Query, and Flashback Table and Flashback Transaction) rely on the undo information that reside in the Undo Tablespace. If the information is not available in the Undo Tablespace, the flashback operation will fail. Whether the information is available or not depends on several factors including:

• Size of the undo tablespace 
• UNDO_RETENTION parameter 
• Auto extend property
• Undo Guarantee property 

In order to allow an extended and even unlimited historical undo information for flashback purposes, Oracle introduced in version 11gR1 a new feature named Flashback Data Archive (also known as Total Recall). Flashback Data Archive is a repository for storing undo records and it is transparent to the application, i.e. once configured, the usage of the Oracle Flashback features remain the same in terms of syntax. In order to configure this feature, an object named Flashback Archive must be created. Flashback Archive has the following properties:

• Tablespace – Where the information will be stored 
• Quota - Maximum amount of storage that can be allocated on the tablespace 
• Retention – The amount of time that information will be kept in the tablespace 

Oracle introduced a new process named “fbda” that takes care of all the Flashback Data Archive related tasks such as writing the information into the Flashback Archive and purging it once the information is older than the retention period. In the following example a new default Flashback Archive object named “my_fda” with 25GB quota on a tablespace named “my_fda_ts” and a retention period of 5 years is created:

SQL> CREATE TABLESPACE my_fda_ts DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M;
TABLESPACE created.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT my_fda TABLESPACE my_fda_ts QUOTA 25G RETENTION 5 YEAR;
FLASHBACK archive created.

When enabling Flashback Archive for a table, unless explicitly specifying the Flashback Archive name, the default Flashback Archive will be used (in our case, “my_fda”. There can be only 1 default Flashback Archive. Let us create an additional non-default Flashback Archive with a 5GB quota and a retention period of 1 year:

SQL> CREATE FLASHBACK ARCHIVE one_year_fda TABLESPACE my_fda_ts QUOTA 5G RETENTION 1 YEAR;
Flashback archive created.

The retention information for the Flashback Archives is available in the DBA_FLASHBACK_ARCHIVE dictionary view:

SQL> SELECT flashback_archive_name, retention_in_days, status FROM DBA_FLASHBACK_ARCHIVE;

FLASHBACK_ARCHIVE_NAME   RETENTION_IN_DAYS STATUS
------------------------ ----------------- -------
MY_FDA                                1825 DEFAULT
ONE_YEAR_FDA                           365

The information about the quota per each Flashback Archive and the associated tablespace is available in the DBA_FLASHBACK_ARCHIVE_TS dictionary view:

SQL> SELECT flashback_archive_name, tablespace_name, quota_in_mb/1024 QUOTA_GB FROM DBA_FLASHBACK_ARCHIVE_TS;

FLASHBACK_ARCHIVE_NAME    TABLESPACE_NAME                  QUOTA_GB
------------------------- ------------------------------ ----------
MY_FDA                    MY_FDA_TS                              25
ONE_YEAR_FDA              MY_FDA_TS                               5

Once the Flashback Archive has been configured, the user can easily enable and disable this feature for the desired tables, as follows:

SQL> alter table departments flashback archive;
Table altered.

SQL> alter table departments no flashback archive;
Table altered.

To enable FDA for a table using a non-default Flashback Archive, the Flashback Archive must be explicitly set, as follows:

SQL> alter table departments flashback archive ONE_YEAR_FDA;
Table altered.

The DBA_FLASHBACK_ARCHIVE_TABLES dictionary view displays all of the tables that have a Flashback Archive configured:

SQL> SELECT table_name,owner_name,flashback_archive_name FROM DBA_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME  OWNER_NAME  FLASHBACK_ARCHIVE_NAME    
----------- ----------- ------------------------- 
DEPARTMENTS  SALES         ONE_YEAR_FDA            

Oracle 11gR2 Flashback enhancements

In Oracle 11gR2 the following enhancements were introduced:

• Flashback Database – Prior to 11gR2, the DBA had to restart the database to a MOUNTED state and only then enable the Flashback Database feature. Now, the DBA can enable the Flashback Database when the instance is open with no downtime. 
• Flashback Data Archive – Prior to Oracle 11gR2, several DDL commands on a table with Flashback Archive enabled raised an error: “ERROR at line 1 ORA-55610: Invalid DDL statement on history-tracked table”. This includes the following DDL operations:
• TRUNCATE TABLE
• ALTER TABLE [ADD| DROP| RENAME| MODIFY] Column
• DROP|TRUNCATE Partition
• ALTER TABLE RENAME 
• ALTER TABLE [ADD| DROP| RENAME| MODIFY] Constraint

Staring with Oracle 11gR2, these operations are now supported on tables with Flashback Archive enabled.

Oracle 12c Flashback enhancements

12.1.0.1 New Features:

In Oracle version 12.1.0.1, new enhancements were introduced to the Flashback Data Archive feature including:

• The user context information for the transactions is now tracked as well. This allows us to understand not only what the changes were but also who is responsible for those changes.
• Support for tables that use the Hybrid Columnar Compression (HCC) feature.
• Support for exporting and importing the Flashback Data Archive tables.

12.1.0.2 New Features:

In version 12.1.0.1, the Flashback Data Archive feature was supported only for Non-CDB environments. Starting from version 12.1.0.2 the Flashback Data Archive feature is supported for a Container Database (also known as the Multitenant Architecture) in addition to Non-CDB option.

Summary

In this article we have reviewed Oracle 11g and 12c Flashback new features and enhancements.

In the next part (last one in this articles series) we will review the Oracle Flashback licensing. In addition, we will also summarize everything and we will see which Oracle Flashback feature should be used in various human errors use cases.