Flashback Data Archive in Oracle Database

Hi,

I will explain what is the Flashback Data Archive feature in Oracle in this article.

Read previous article before this. If you don’t know What is the Flashback and its architecture.

Oracle Flashback Architecture

Flashback Data Archive feature is very much loved by Oracle DBA’s and it is come with Oracle 11g. The purpose of Flashback Data Archive is to store all DML and transaction on the important tables in a different tablespace and see the changes whenever you want.

This feature was used before the log mineral feature. But thanks to this feature we can see faster. The flashback data archiver (FBDA) process is running in the background while using this feature.

Let’s go to make an example about Flashback Data Archive to learn this feature very well.

Firstly create tablespace for storing Flashback Data Archive objects.

CREATE TABLESPACE Archive DATAFILE
‘/u01/oracle/recovery/arc_01.DBF’ SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 10000M

Tablespace created.

Let’s define the tablespace as flashback data archive space and limit it to 10 Gb.

create flashback archive flashback_archive tablespace Archive quota 1G retention;

Lets enable our tables to flashback archive to be saved in this field.

alter table test flashback archive flashback_archive;

Let’s do an update on the table and then see how the previous version is stored and how to access it.

select owner, segment_name from test; 

OWNER, SEGMENT_NAME
TEST LOGMNR_I1COL$
TEST LOGMNR_I2COL$
TEST LOGMNR_I3COL$
TEST LOGMNR_I1ATTRCOL$
TEST LOGMNR_I1TS$

update test set owner = 'USER';

commit;

The history of all DML operations on this table will now be stored in this tablespace.

select owner, segment_name from deveci.test as of timestamp sysdate-2/1440;

OWNER SEGMENT_NAME
TEST LOGMNR_I1COL$
TEST LOGMNR_I2COL$
TEST LOGMNR_I3COL$
TEST LOGMNR_I1ATTRCOL$
TEST LOGMNR_I1TS$

If we you want to remove the table from flashback archive mode and delete all the history data, execute following code.

alter table deveci.test no flashback archive;

If You want to delete all of the flashback data archives, you can delete it as follows.

Alter flashback archive purge all

The following command is executed to delete before specific timestamp or SCN.

Alter flashback archive purge before scn | timestamp

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/