Oracle 11g New Feature Flashback Data Archive Use Example

Flashback Data Archive (Flashback Log Archive) is actually understood as saving undo data for a long time. For some important tables, you can customize its historical record preservation period. Its various behaviors are similar to undo tablespace 10 points, and it is completely transparent when used. Users don't know whether its query 1-oriented view data comes from undo or Flashback Data Archive. Now, let's analyze several differences between it and undo:

1. Flashback Data Archive only records UPDATE and DELETE statements, not INSERT statements.

2. The row database in Flashback Data Archive can be kept for a very long time, even a few ten years, while the objects in undo data usually have a retention period of only a few hours or days.

3. The essential functions of Flashback, Data, Archive and undo are different. It only pays attention to the historical changes of table rows, but not undo to realize the read uniformity and rolled-back operation of the whole database transaction.

So how is Flashback Data Archive realized? Imagine that since you are saving row records in the table, Should require a separate storage area to record row records, Specify the retention period for this area, so that the data in it will be retained for a long time, and then add tracking marks on the table you want to record, so that the archiving of row records can be realized. In fact, this is the implementation principle of Oracle. The storage area here is Flashback Data Archive, which is created by using create flashback archive command during use.

Step 1 Create an archive

Oracle recommends using a separate tablespace to store Flashback Data Archive, or you can create one or more Flashback Data Archive on an existing tablespace,

-Ensure that the executing user has FLASHBACK_ARCHIVE_ADMINISTRER permissions prior to creation, which include creating and modifying flashback archive, enabling table tracing, managing table spaces in the archive, etc.)

SQL> create tablespace fbda1

2  datafile '/u01/app/oracle/oradata/prod/fbda01.dbf'

3  size 5g;
Tablespace created.

Next, start creating Flashback Data Archive:

SQL> create flashback archive fb_01

2  tablespace fbda1 quota 300m

3  retention 10 year;
Flashback archive created.

The above command creates a 10-year-old Flashback Data Archive that can use 300m in the fbda1 table space (a little small...) As needed, we can create another default archive, using default keywords, such as

SQL> create flashback archive default fb_dflt

2  tablespace fbda1

3  retention 1 year

4  /
Flashback archive created.

Query the DBA_FLASHBACK_ARCHIVE view for archived information, including retention period, status, and so on

SQL> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,

2  FLASHBACK_ARCHIVE#,RETENTION_IN_DAYS,STATUS

3  from dba_flashback_archive;

 

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS STATUS

---------- ---------------------- ------------------ ----------------- -------

SYS        FB_01                                   1              3650

SYS        FB_DFLT                                 2               365 DEFAULT

Query the FLASHBACK_ARCHIVE_TS view for the tablespace and archive correspondence

SQL> select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME    FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_MB

------------------------- ------------------ -------------------- ------------

FB_01                                      1 FBDA1                300

FB_DFLT                                    2 FBDA1

2. Trace the table into the specified Flashback Data Archive

Assign the emp table of the scott user to the fb_01 archive:

SQL> alter table scott.emp flashback archive fb_01;
Table altered.

Query the DBA_FLASHBACK_ARCHIVE_TABLES view to obtain the archived table:

SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS

---------- ---------- -------------------- -------------------- --------

EMP        SCOTT      FB_01                SYS_FBA_HIST_73181   ENABLED

3. Query using Flashback Data Archive

The use here is no different from undo at all. It is enough to specify as of in the query. Here, it is demonstrated to query emp table data 3 years ago:

SQL> select empno,ename,hiredate

 2 from scott.emp

 3 as of timestamp (systimestamp - interval '3' year)

 4 where empno=7934;
EMPNO ENAME      HIREDATE

 ---------- ---------- ---------

 7934 MILLER     23-JAN-82