New Features of Oracle 11g Flashback Data Archive instance, 11 gflashback

New Features of Oracle 11g Flashback Data Archive instance, 11 gflashback

Flashback Data Archive (flash back log archiving) is actually used to save undo Data for a long time. For some important tables, you can customize the retention period of their historical records, its behaviors are very similar to those of the undo tablespace, and they are completely transparent when used. users do not know whether its query consistency View Data comes from undo or Flashback Data Archive, now we can analyze the differences between it and undo:

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

2. Row databases in Flashback Data Archive can be stored for a very long period of time, or even decades. In contrast, objects in undo Data are usually retained for only a few hours or days.

3. The essential functions of Flashback Data Archive and undo are different. It only focuses on the historical changes of table rows, rather than undo, to achieve read consistency of the overall database transactions and rollback operations.

So how is Flashback Data Archive implemented? Assume that the row record in the table should be stored in a separate storage area to specify the retention period for this region, in this way, the data will be retained for a long time, and then the tracking mark will be added to the table to be recorded, so that the row record can be archived and saved, in fact, this is the implementation principle of Oracle. The storage area here is Flashback Data Archive, which is created using the create flashback archive command during use.

1. Create an archive

Oracle recommends that you use a separate tablespace to store Flashback Data Archive. Of course, you can also create one or more Flashback Data Archive tables in an existing tablespace,

-Before creating a table, make sure that the execution user has the FLASHBACK_ARCHIVE_ADMINISTRER permission, including creating and modifying flashback archive, enabling table tracing, and managing tablespaces in the archive)
Copy codeThe Code is as follows:
SQL> create tablespace fbda1
2 datafile '/u01/app/oracle/oradata/prod/fbda01.dbf'
3 size 5g;

Tablespace created.

Next, create the Flashback Data Archive:
Copy codeThe Code is as follows:
SQL> create flashback archive fb_01
2 tablespace fbda1 quota 300 m
3 retention 10 year;

Flashback archive created.

The above command creates a Flashback Data Archive for ten years. It can use MB space in the fbda1 tablespace (a little small ...), We can create another default Archive as needed, using the default keyword, as shown in figure
Copy codeThe Code is as follows:
SQL> create flashback archive default fb_dflt
2 tablespace fbda1
3 retention 1 year
4/

Flashback archive created.

Query the DBA_FLASHBACK_ARCHIVE view to obtain the archive information, including the retention period and status.

Copy codeThe Code is as follows:
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 3650
SYS FB_DFLT 2 365 DEFAULT

Query the FLASHBACK_ARCHIVE_TS view to obtain the ing between the tablespace and the archive.

Copy codeThe Code is as follows:
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. Track the table to the specified Flashback Data Archive.

Allocate scott's emp table to the fb_01 archive:

Copy codeThe Code is as follows:
SQL> alter table scott. emp flashback archive fb_01;

Table altered.

Query the DBA_FLASHBACK_ARCHIVE_TABLES view to obtain the archived tables:

Copy codeThe Code is as follows:
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. Use Flashback Data Archive for query

The usage here is no different from that of undo. You can specify the as of in the query. Here we will show how to query the emp table data three years ago:
Copy codeThe Code is as follows:
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