Flashback Data Archive (FDA) tips

RMAN Oracle Flashback Data Archive

Starting from the Oracle 11g version, you can keep every transaction made to the table and keep it as long as you want using FDA. Before 11g, in order to get the before image of any row, either you were getting it from archived redo log files (if they are kept) using Log Miner, or were writing a trigger to save the data in another log table. But now by using the flashback data archive feature, you do not need to use Log Miner or a trigger to track changes made to the table.

The new background process, FDA (Flashback Data Archive), tracks all changes made to the table and stores it in a file in a compressed and partitioned format. However, you cannot use this feature with clustered, temporary, nested, remote or external tables and long or nested columns. It tracks all transactional changes made to specific tables for the specific time interval. In the following scenario, you will see the configuration and usage of this feature in detailed examples.

• Scenario 7: Bob got a call from the manager: " Hi Bob. You know we are currently working on a new project and we need to keep all changes made to all tables for one year. We do not want to use trigger and auditing because of performance degradation. We cannot use Log Miner because we do not keep archived redo log files for a long time. Please find another solution!"

As Bob's company uses Oracle 11g, Bob automatically decides to use Oracle's flashback data archive technology to implement this task. Now see the steps of creation of the transactional history of the table using flashback data archive. For this, the user should have the flashback archive administer system privilege to create a new flashback data archive. Moreover, the flashback archive object privilege should be granted to the user to enable historical data tracking.

Create a new user and grant him the required privileges:

Create a new separate tablespace for data archive:

Create flashback archive on this tablespace using the create flashback archive commandas follows:

With the above command, a flashback archive named fl_arch is created which resides in the tablespace tbs_arch and holds information for one year. It means that you can use any flashback query which contains one year of historical information regarding the table that is assigned to this flashback archive.

Now, create a table, insert one row and assign it to the flashback archive:

The historical change on the table tbl_fl_archive will now be written to the flashback archive named fl_archive. To test it, delete all rows from the table and use flashback queryon that table. Remember, it will not look for the undo data; it will look to the flashback archive file for the changes:

In order to show and prove that it does not look to the undo tablespace for the historical information on the rows for the specific time, create a new undo tablespace and make it default by dropping the old one. Then use flashback query on that table:

As can be seen, you are currently using the different undo tablespace that does not have any information about before images of data blocks of the tbl_fl_archive. Now use flashback query against that table:

This query gets the data from flashback data archive.

The final piece of the puzzle in the flashback progression is the new Oracle 11g flashback archive. Define an area that provides the ability to automatically track and archive transactional data changes to specified database objects. These flashback archives become user named and managed persistence of UNDO at the specified object level. So when one needs to do a SELECT with an AS OF, rely on the object being in the chosen container for the specified duration and competing for space only with the objects one chooses. Thus, it is merely a named are to support all the prior flashback features that have just been examined.