Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Flashback Data Archive (FDA) tips

Oracle Database Tips by Donald BurlesonFebruary 19, 2015

Question:  How does the flashback data archive feature in 11g work to help archive older data for auditing purposes?

Answer:  Also see my notes on long term data archiving and see my Oracle Total Recall Tips.

With the flashback data archive functionality of 11g the Oracle database is capable of automatically tracking transactional changes to data over very long periods. Oracle achieves this by storing UNDO information in special segments within dedicated tablespaces.

The usage of this feature is completely transparent for the application and the end user, who can view historical data from the flashback archive seamlessly with regular SQL statements. This is done by utilizing traditional flashback functionalities, such as flashback query, flashback versions query, flashback transaction query, etc.

The historical information in the flashback data archive ages out automatically and Oracle automatically purges it after the specified retention period has exceeded. However, the flashback data archive provides the DBA with a central interface for the management of historical data and change tracking.

% With a flashback data archive it is possible to view data as any point in time since the flashback data archive was created. However, attempting to view data as a timestamp before the data archive is created causes the following error: ORA-01466: unable to read data - table definition has changed

The technology behind flashback data archive

With every Oracle 11g database startup, the new flashback data archiver background process, FBDA, is automatically started. This is what generates and archives the historical data.

Transactions encounter very little performance impact from flashback data archiving because Oracle only marks DML operations as candidates for archiving. A special background process then generates and archives the history information asynchronously for tables enabled for flashback archival.

Oracle also automatically compresses the internally used history tables and partitions them based on a range partitioning scheme. The partitioning and compression of the history tables is fully transparent and does not require any additional administrative intervention.

A flashback data archive consists of at least one tablespace, and can span multiple tablespaces. It is possible to add a new tablespace to a flashback archive at any time.

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:

SQL>
create
user usr
identified by
usr;
User created.

SQL>
grant
connect, resource, flashback archive administer to usr;
Grant succeeded.
SQL>

Create a new separate tablespace for data archive:

SQL>
create
tablespace tbs_arch datafile 'c:\flashback_archive.dbf' size 10m;
Tablespace created.
SQL>

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

SQL>
create
flashback archive fl_arch
2 tablespace tbs_arch retention 1 year;
Flashback archive created.

SQL>

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:

SQL>
create
table tbl_fl_archive (id number, name varchar2(20));
Table created.

SQL>
insert into
tbl_fl_archive values(1,'Flashback Archive');
1 row created.

SQL>
commit;
Commit complete.

SQL>
select * from
tbl_fl_archive;

ID NAME
---------- --------------------
1 Flashback Archive

SQL>
alter
table tbl_fl_archive flashback archive fl_archive;
Table altered.
SQL>

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:

SQL>
select
to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate
from
dual;

DDATE
-----------------
13022010 12:46:49

SQL>
delete
from tbl_fl_archive;
1 row deleted.

SQL>
commit;
Commit complete.

SQL>
select * from
tbl_fl_archive;
no rows selected

SQL>
select * from
tbl_fl_archive as of timestamp to_timestamp('13022010
12:46:49','ddmmyyyy hh24:mi:ss');

ID NAME
---------- --------------------
1 Flashback Archive

SQL>

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:

SQL>
conn / as sysdba
Connected.

SQL>
show
parameter undo_tablespace;

NAME TYPE VALUE
--------------------- ----------- --------- ------------
undo_tablespace string UNDOTBS1

SQL>
select
a.name
from
v$datafile a, v$tablespace b
where
a.ts#=b.ts# and b.name='UNDOTBS1';

NAME
------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS01.DBF

SQL>
create
undo tablespace undotbs2 datafile
'c:\app\administrator\oradata\db2\undotbs02.dbf' size 10m;
Tablespace created.

SQL>
alter
system set undo_tablespace='undotbs2';
System altered.

SQL>
startup
force
ORACLE instance started.

Total System Global Area 431038464 bytes
Fixed Size 1333676 bytes
Variable Size 251659860 bytes
Database Buffers 171966464 bytes
Redo Buffers 6078464 bytes
Database mounted.
Database opened.

SQL>
show
parameter undo_tablespace;

NAME TYPE VALUE
--------------------------- ----------- ------------
undo_tablespace string UNDOTBS2

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:

SQL>
conn
us1/us1
Connected.
SQL>
select * from
tbl_fl_archive as of timestamp to_timestamp('13022010
12:45:30','ddmmyyyy hh24:mi:ss');

ID NAME
---------- --------------------
1 Flashback Archive

SQL>

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.

 

SQL> create tablespace flash_archive

datafile 'c:\oracle\oradata\ordb1\flash_archive.dbf' size 50M;

 

 

Tablespace created

 

SQL> create flashback archive default flash_archive tablespace flash_archive retention 30 day;

 

Flashback archive created.

 

SQL> create table bert.junk (c1 int, c2 int) flashback archive flash_archive;

 

Table created.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster