Flashback on Oracle SE

(c)Rikard Carlsson 2013   Oracle Flashback Technology

is a group of Oracle Database features that that let you view past states of database objects or t-o return database objects to a previous state without using point-in-time media recovery.

This is a pretty neat feature even for a Standard Edition Database, as long as you remember not to use some of the options that requires Enterprise Edition Database License.

The Flashback feature rely on the Automatic Undo Management, so remember to turn it on (UNDO_RETENTION).

Flashback Query introduced in Oracle9i
So it has been there for a while.  From version 10g Flashback technique was enhanced, and it became more user-friendly and powerful to use.

With flashback features, you can for example do:

  • Perform queries that return past data
  • Perform queries that return metadata that shows a detailed history of changes to the database
  • Recover tables or rows to a previous point in time
  • Automatically track and archive transactional data changes
  • Roll back a transaction and its dependent transactions while the database remains online

Oracle License:
I couldn’t find the license document from Oracle9i, but here is the license information for 10gR2, 11gR1, 11gR2, and 12cR1.

Oracle 10gR2 License

Oracle 11gR1 License

Oracle 11g R2 License

NOTE:  Oracle gave us SE DBA’s a treat by downgrading the Flashback Data Archive (=FDA) feature from 12c to version The Basic FDA can be implemented in a SE Database, but be aware of the possible growing storage needs it will cause. It all depends on for how long you intend to store the data.

Pitfalls for a SE DBA

As you notice from the above license information you should avoid following features, since they will require an Enterprise Edition license:

  • Flashback Database
  • Flashback Table
  • Flashback Drop
  • Flashback Transaction => might be a little confusing, Flashback Query is ok in SE
  • Flashback Transaction Query => might be a little confusing, Flashback Version Query is ok in SE
  • Optimization for Flashback Data Archive

And remember. On the internet you can find so many good scripts and solutions, but it is ALWAYS good manner to verify against the license document before even testing those scripts on your SE database.

Below you can see one possible pitfall.

When you start to google for Flashback Version Query, you will  bump into a script, that use a combination of a method that is ok in SE and a method that requires an EE license. By running this script in a SE Database, you will accidentally violate the license agreement.

The query uses Flashback Version Query pseudocolumns.

ThisIS OK in SE:

SELECT versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN, versions_operation OPERATION,   empname, salary FROM hr.emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE   where empno = 111;

Following IS NOT OK in SE (=EE license)

… following FlashbackTransactionQuery to audit all changes made by this transaction:

SELECT xid, start_scn START, commit_scn COMMIT, operation OP, logon_user USER, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW(‘000200030000002D’);

Example of Flashback Version Query (=ok to use in SE Database)

  1. Get the current SCN by calling dbms_flashback.get_system_change_number
  2. insert some data into some table
  3. commit and check it out
  4. enable the flashback for either a specific point-in-time or for a specific SCN by calling dbms_flashback.enable_at_system_change_number(query_scn) or dbms_flashback.enable_at_time(query_time);
  5. select the table, and notice that the rows inserted are not there.
  6. disable Flashback mode by calling dbms_flashback.disable;

Basic Flashback Data Archive (=ok to use in SE Database)

This feature as I have understood was once called “Total Recall”. In short It gives you the opportunity to query historical data that have been outage by the UNDO_RETENTION policy, that you have implemented into your database. Your historical data will be available as long as the FDA retention time you gave at creation.

Example of creating FDA:

create tablespace fba_no_optimize datafile 'fda1no.dbf' size 100M;
create flashback archive fba_no_optimize
tablespace fba_no_optimize retention 1 year no optimize data; => this is the Basic FDA feature
create table t_tbl (id number) flashback archive fba_no_optimize;

NOTE: by verifying the DDL, we can see everything is ok, since there is the "NOCOMPRESS" word.
If you on the other hand would see "COMPRESS FOR OLTP", it would blow the gaff; the FDA has been created with the advanced option, which requires an EE license.  
select DBMS_METADATA.GET_DDL('TABLE','T_TBL') from dual; => this will show you the tables DDL.

I am sure you will find plenty of examples about FDA and what it might be useful for, or maybe you are having one solution yourself, but if not, then check Lucas Jellema post. It might give you an “a-ha” on what to use it for. Check it out!

Have a lovely day!

Turku, Finland – 30 July 2014


2 thoughts on “Flashback on Oracle SE”

  1. Hi Ann. I’m a Oracle SE dba and today at office we were wondering about this: what if i have recyblebin on and issue flashback table to before drop ? Still honoring SE or requires EE?
    Thanks a lot!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow me

Click to Follow

%d bloggers like this: