Flashback – Part 7 (Flashback Data Archive)

Hey everyone,

Finally, the last part of our flashback posts, FLASHBACK DATA ARCHIVE! If you didn’t have a chance to check the previous posts, please do not hesitate to take a look if you need or if you just get curious.

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)

Well, there we go then 🙂

The Flashback Data Archive is a great option if you need to keep track of all changes for a very long time in your database. I mean, when all other Flashback options aren’t good enough for you and you need to keep way more time of history, you need to use Flashback Data Archive, which is gonna keep the track of lifetime changes.

Why would I want to use that? Well, one of the options that I see, is about auditing your DB.

Considering the configuration and use of Flashback Data Archive, we’re gonna list the steps and then explain them with more details:

1. Create a tablespace with enough space for your data archive (It can be an existing one, but how about we keep ourselves better organized?)
2. Create the Flashback Data Archive using the tablespace created on step1 and define quota to the tablespace (optional) and define the retention of the FDA (optional).
3. Create/Alter a table to use the flashback data archive.

It is pretty straightforward and simple to configure and use it. So let’s get into the details:

1. Create the tablespace:

If you are here reading this post we assume that you already know how to create a simple tablespace 🙂

2.  Create the Flashback Data Archive:

SQL> create flashback archive audit_grepora tablespace tbs_grepora_archive quota 25g retention 2 year;

Please check that here, you can set up:

* Flashback Data Archive NAME
* Define the tablespace
* Define the quota
* Define the retention

Of course, you can change all the parameters as you need using the ALTER command, such as:

SQL> alter flashback archive audit_grepora MODIFY tablespace tbs_greopora_archive quota 10g;

or 

SQL> alter flashback archive audit_grepora retention 200 day;

Also, you can clean up your Flashback Data Archive as you need. Imagine that you are running out of space and your data is too big and you don’t need the oldest data. Then we can PURGE the flashback data archive using SCN or timestamp:

SQL> alter flashback archive audit_grepora purge before SCN 9835743;

or

SQL>alter flashback archive audit_grepora purge before timespamp (SYSDATE - 365);

3. Create/Alter a table to use the flashback data archive:

This is the simplest step. If you want your table to use a specific flashback data archive to keep the history of all its changes, then you need to run the following:

SQL> alter table grepora flashback archive audit_grepora;

Or if you are creating a new table, just add “flashback archive” in the end of your DDL:

SQL> create table grepora 
(column1 varchar2(20),
column2 varchar2(20)),
flashback archive;

If you want to remove your table from using the FDA, simply do it with alter command:

SQL> alter table grepora no flashback archive;

Imagine that you want to check how that table was 200 days ago? Then just use the AS OF TIMESTAMP clause in your SELECT statement, already discussed on previous posts 🙂

If you want to check Flashback Data Archive information, please go through these views:

DBA_FLASHBACK_ARCHIVE – Information about flashback data archive

DBA_FLASHBACK_ARCHIVE_TS – All tablespaces used by FDA

DBA_FLASHBACK_ARCHIVE_TABLES – List all tables with FDA enabled.
I sincerely hope that this flashback tutorial helped you to define your best strategies of recover your data and also cleared your mind about some doubts that may have showed up during your day job.

We from GrepOra.com are very grateful to have the opportunity to share knowledge and experience with everyone and we seriously want to help!

This is the end of Flashback posts. See you next week with some other subject 🙂

Rafael.