The 12c licensing information document holds great news. In 11g, flashback data archives have only been availabe as a seperate option to Enterprise Edition. This was first sold as the Total Recall Option which was later abandoned and the feature was continued as part of the more expensive Advanced Compression option. But starting with 12c, basic flashback data archives are included in all editions, even Standard Edition. Only if you want or need to employ compression to optimize storage consumption you’ll need the Advanced Compression Option on top of EE. An extra parameter was added to the create flashback archive command defaulting to not use compression. There have been speculations about this change since last year.
Let’s have a look at how this works. But first one more caveat: Flashback Archives are not (yet?) supported with CDBs or PDBs that you would need to take advantage of the new Oracle Multitenant option. This is documented as a restriction. If you try to set up a flashback archive on such a CDB anyway, you will receive this error:
SQL> CREATE FLASHBACK ARCHIVE CDBFBA TABLESPACE FBA_TS RETENTION 1 MONTH NO OPTIMIZE DATA; CREATE FLASHBACK ARCHIVE CDBFBA TABLESPACE FBA_TS RETENTION 1 MONTH NO OPTIMIZE DATA * ERROR at line 1: ORA-65131: The feature Flashback Data Archive is not supported in a pluggable database.
So I had to create a new database on my test system as a non container database and retry. First, I’ll simply create a new tablespace for the flashback data archives. I’ll do this twice to show the difference between an “optimized” and a “basic” flashback archive.
SQL> CREATE TABLESPACE FBA_NO_OPTIMIZATION DATAFILE 'FBA_NO_OPT01.DBF' SIZE 1G; Tablespace created. SQL> CREATE TABLESPACE FBA_OPTIMIZATION DATAFILE 'FBA_OPT01.DBF' SIZE 1G; Tablespace created.
Next, create the flashback archives on those tablespaces. This is where one specifies the retention time and also the new [NO] OPTIMIZE DATA parameter. In this case, I’d like a retention time of 1 month for the archives and create one with and another one without the optimization. See the full syntax for CREATE FLASHBACK ARCHIVE in the documentation.
SQL> CREATE FLASHBACK ARCHIVE FBA_NO_OPTIMIZATION TABLESPACE FBA_NO_OPTIMIZATION RETENTION 1 MONTH NO OPTIMIZE DATA; Flashback archive created. SQL> CREATE FLASHBACK ARCHIVE FBA_OPTIMIZATION TABLESPACE FBA_OPTIMIZATION RETENTION 1 MONTH OPTIMIZE DATA; Flashback archive created.
Now we are ready to create tables and associate them with the FBA. The underlying archive tables will get created with a little delay once we perform DML on the base table so I’ll insert and delete a record to trigger this.
SQL> CREATE TABLE T_NO_OPTIM (ID NUMBER(19), DESCRIPTION VARCHAR2(42)) FLASHBACK ARCHIVE FBA_NO_OPTIMIZATION; Table created. SQL> CREATE TABLE T_OPTIM (ID NUMBER(19), DESCRIPTION VARCHAR2(42)) FLASHBACK ARCHIVE FBA_OPTIMIZATION; Table created. SQL> INSERT INTO T_NO_OPTIM VALUES (42, 'my first 12c insert'); 1 row created. SQL> commit; Commit complete. SQL> DELETE FROM T_NO_OPTIM; 1 row deleted. SQL> COMMIT; Commit complete. SQL> INSERT INTO T_OPTIM VALUES (42, 'my first 12c insert on optimized FBA'); 1 row created. SQL> COMMIT; Commit complete. SQL> DELETE FROM T_OPTIM; 1 row deleted. SQL> COMMIT; Commit complete.
And now we can have a look at the DDL for the actual flashback archive tables. You can find the names for these tables by querying DBA_FLASHBACK_ARCHIVE_TABLES. This one is for the one created with the NO OPTIMIZE DATA option (the new default).
CREATE TABLE "BROST"."SYS_FBA_HIST_93059" ( "RID" VARCHAR2(4000 BYTE), "STARTSCN" NUMBER, "ENDSCN" NUMBER, "XID" RAW(8), "OPERATION" VARCHAR2(1 BYTE), "ID" NUMBER(19,0), "DESCRIPTION" VARCHAR2(42 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FBA_NO_OPTIMIZATION" PARTITION BY RANGE ("ENDSCN") (PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FBA_NO_OPTIMIZATION" ) ;
And this is the DDL for the flashback archive table with the OPTIMIZE DATA parameter enabled which requires the Advanced Compression option on top of Enterprise Edition. The only difference to the one above is the COMPRESS FOR OLTP in the DDL.
CREATE TABLE "BROST"."SYS_FBA_HIST_93060" ( "RID" VARCHAR2(4000 BYTE), "STARTSCN" NUMBER, "ENDSCN" NUMBER, "XID" RAW(8), "OPERATION" VARCHAR2(1 BYTE), "ID" NUMBER(19,0), "DESCRIPTION" VARCHAR2(42 BYTE) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FBA_OPTIMIZATION" PARTITION BY RANGE ("ENDSCN") (PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP LOGGING STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FBA_OPTIMIZATION" ) ;
I have not done any tests to show just how much OLTP COMPRESSION saves in this case which would be important to answer the question of the real benefit of purchasing the advanced compression option if you are not already using it for other parts of your system. But I am pretty sure that in many cases customers can easily get away with using FDAs without compression just fine. As long as you don’t perform a lot of updates or deletes on tables or keep historic data for years, archives won’t grow too big.
One issue remains unresolved in 12c and that is the default partitioning combined with the minextent size. Every flashback data archive will have a new partition created for each day which is mostly a good thing, especially for larger history tables. But this partition will get created even for the smallest of your flashback enabled tables. And since the partitition is created with SEGMENT CREATION IMMEDIATE, even an empty partition will take up the space of the INITIAL extent which was bumped by default to 8MB in 11.2. So if you have flashback enabled for 42 tables, even if you don’t perform any DML on those tables, a month worth of flashback archive partitions will take up more than 10GB of data on your disks. And it does not even matter if you use compression or not. I have already discussed this (and a workaround) in a previous blog post. Setting the hidden parameter _PARTITION_LARGE_EXTENTS to false still seems to do the trick.
The big change with 12c and FBA is obviously the option to not compress the archives with the implication of making this feature free to use, even in SE. It does not happen very often that Oracle hands out presents like that and stops charging for a feature that once required an extra option. I bet many people would love to see the same move with AWR and some other EE-Option features. But I think that more than a gift this is also a very good chance for Oracle to differentiate itself from the competitors. Total Recall and flashback queries are (at least to my knowledge) unique to Oracle, no other database vendor can offer this feature. So if more ISVs pick up this technology and integrate it into their solution it commits them to using the Oracle database. And this added value may just be what drives the decision to use one db vendor over another.
I have presented multiple times about how we used flashback archives in our Global Price Management software to enable end users to start a session within a sort of time machine where we use total recall to put a whole session back in time by up to three years.
The 184.108.40.206 patchset is just out and it looks like the option to leave flashback archives uncompressed was included here aswell. The 11.2 license document states:
Beginning with Oracle Database 11g Release 2 (220.127.116.11): You must license the Oracle Advanced Compression option to use Optimization for Flashback Data Archive history tables. Basic Flashback Data Archive—without history table optimization—is available in all editions.