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.

 

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

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *