Flashback Data Archive - New Feature Oracle Database 11g

Set Up and Use a Flashback Data Archive

Retaining a historical record of all changes to rows in critical tables is error prone and requires custom applications or database triggers to maintain repositories for the historical changes. Every time you create a new application or update a table in an application that requires historical tracking, you must make changes to your tracking application as well. You can use Flashback Data Archive to save historical changes automatically to all key tables for as long as possible.

Understanding Flashback Data Archive

In Flashback Data Archive you create one or more repository areas (dedicated tablespace) (one of which can be the default), assign a default retention period for objects in the repository, and then mark the appropriate tables for tracking.

A Flashback Data Archive acts much like an undo tablespace. However, a Flashback Data Archive records only UPDATE and DELETE statements but not INSERT statements. In addition, undo data is typically retained for a period of hours or days for all objects. Rows in Flashback Data Archives can span years or even decades.

Creating an Archive

You can create one or several Flashback Data Archives in existing tablespaces using the CREATE FLASHBACK ARCHIVE command. However, Oracle best practices recommend that you use dedicated tablespaces. All archives must have a default retention period using the RETENTION clause and can optionally be identified as the default archive using the DEFAULT keyword. The disk quota in an archive is limited by the disk space within the tablespace, unless you assign a maximum amount of disk space in the archive using the QUOTA keyword.

In this example, you first create a dedicated tablespace for your Flashback Data Archive:

 CREATE tablespace sample datafile 'C:\sample01.dbf' SIZE 10g;  

Or you can use the inbuild tablespace USERS

Next you create your flashback archive in the desired tablespace as shown below.

 create flashback archive test tablespace users retention 1 year;  

Data Dictionary Views of Flashback Data Archive

 col FLASHBACK_ARCHIVE_NAME format a20  
 select flashback_archive_name, flashback_archive#, retention_in_days, status from dba_flashback_archive;  
 TEST                                     1                 365  

 select * from dba_flashback_archive_ts;  
 TEST                           1             USERS       

 alter table scott.emp flashback archive test;  

 col owner_name format a5  
 col ARCHIVE_TABLE_NAME format a30  
 select * from dba_flashback_archive_tables;  
 EMP          SCOTT       TEST              SYS_FBA_HIST_81465      ENABLED  

 select to_char(current_timestamp-2/1440,'DD-MON-YYYY HH24:MI:SS') from dual;  
 18-DEC-2014 13:48:51  
 delete from scott.emp where empno = 1;  

If we want to see the deleted row again.



Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

Oracle Database SQL - Practise - Question - Scott Schema Examples

Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command