Flash Back Data archive (Flashback Archive)

Transferred from: http://book.2cto.com/201209/4154.html

The flashback query's dependency on the undo data and parameter undo_retention doomed them to a small amount of time in the event of a lot of traffic, and it would be impossible to query the "old" data for months before, but this is not impossible in the face of the Flashback data archive.

The Flashback data archive works by storing the undo data that would otherwise be saved only in the Undo tablespace in the form of a history table in the specified normal tablespace (permanent type table space). And unlike the Undo_retention parameter, which is a setting that affects the entire database, the Flashback data Archive can serve only a specific table, so that the "old" data of interest can be saved for a long time. For example, in the Users table space to create a data archive that can save "old" data for 1 years, named "FDA1", the operator must have "Flashback archive Administer" System permissions:
Sql> Create Flashback archive fda1 tablespace users retention 1 year;

Flashback Archive created.

or create a default flashback data archive named "Fda_default" and the operator must have SYSDBA permissions:
Sql> Create Flashback archive default Fda_default tablespace users retention 1 year;

Flashback Archive created.

With the archive, you can use the "Flashback archive" clause to enable the Flashback data archiving feature on a particular table. For example, let hr.employees use FDA1, from which the modified history of the table will be retained for 1 years:
Sql> ALTER TABLE hr.employees Flashback archive fda1;

Table altered.

If the above command is a normal account, such as an HR user, it must be granted the "Flashback Archive" object permission before executing the command, such as:
Sql> Grant Flashback Archive on fda1 to HR;

Grant succeeded.

If there is a default Flashback data archive, it is not necessary to give its name when enabled. For example, let hr.departments use the default FDA, and the modified history of this table will remain for 1 years:
Sql> ALTER TABLE hr.departments Flashback archive;

Table altered.

You can also specify which flashback data archive to use when creating a new table:
Sql> CREATE TABLE Oe.inventory
2 (ID number,product_id number,supplier_id number) Flashback archive fda1;

Table created.

Use the No Flashback archive clause to turn off the Flashback data archiving feature on a specific table. Executing this command requires "Flashback archive Administer" System permissions:
sql> ALTER TABLE Oe.inventory no flashback archive;

Table altered.

Enabling the Flashback data archive greatly expands the time window of the flashback query, such as when archiving is enabled for at least 7 months before viewing the contents of the Hr.employees table 7 months ago:
Sql> SELECT * from Hr.employees
2 As of timestamp (systimestamp-interval ' 7 ' month);

Take the hr.employees base table as an example, and use the following query to get a glimpse of the key information for a flashback file:
sql> Select
  2    a.flashback_archive_ Name Fda_name,a.retention_in_days days,
  3    ts.tablespace_name ts,ts.quota_in_mb,
  4    t.archive_table_name
  5  from
  6    Dba_flashback_ Archive A,
  7    dba_flashback_archive_ts ts,
  8    Dba_flashback_ Archive_tables T
  9  where
10    a.flashback_archive_name = ts.flashback_archive_ Name and
11    a.flashback_archive_name = t.flashback_archive_name and
12    T.owner_name = ' HR ' and
13    t.table_name = ' EMPLOYEES ';

Fda_name days TS quota_in_m archive_table_name
---------- ---------- ------- ---------- --------------------
FDA1 365 USERS sys_fba_hist_73953

Where the value of the Archive_table_name field is the name of the history table in the archive--sys_fba_ hist_73953, the table cannot be queried directly, not to mention other operations.

In addition, the result shows that the quota size (QUOTA_IN_MB) is empty, that is, there is no quota limit. When an administrator creates a flashback file, it can actually set the maximum amount of disk space that can be consumed, the so-called quota. For example, when creating an archive fda2 limit its space limit to 40GB:
Sql> Create Flashback archive Fda2 tablespace users quota 40G retention 2 year;

Flashback Archive created.

In addition, historical data in the Flashback data Archive can, of course, be cleared manually. For example, clear the archive FDA one months ago Data:
Sql> Alter FLASHBACK archive FDA
2 purge before timestamp (systimestamp-interval ' 1 ' month);

Flashback Archive altered.

or clear all:
Sql> Alter FLASHBACK archive FDA purge all;

Flashback Archive altered.

Tables that have the Flashback data archiving feature enabled still support the overwhelming majority of DDL commands. However, there are "Ora-55610:invalid DDL statement on history-tracked table" errors when executing a few DDL commands, such as "ALTER TABLE ... shrink space", "ALTER TABLE ... Move "," ALTER TABLE ... exchange partition "and so on. Next, take an example of "ALTER TABLE ... exchange Partition". The following are the DDL definitions for a partitioned table and a stack table, respectively:
Partition table (range class partition--by range):
Sql> CREATE TABLE hr.rpt (ID number,name varchar2 (30))
2 partition by range (ID)
3 (
4 partition P1 values less than tablespace users,
5 partition P2 values less than tablespace users,
6 partition P3 values less than (MaxValue) tablespace users
7);

Table created.

Stack table:
Sql> CREATE TABLE Hr.source (ID number, name VARCHAR2 ()) tablespace users;

Table created.

The segment with the name "P3" in the Exchange Partition Interchange partition table and the segment with the stack table named "Source" are of course not a problem in normal circumstances:
sql> ALTER TABLE HR.RPT Exchange partition P3 with table Hr.source;

Table altered.

If the partition table has a flashback data archive support, the "Exchange Partition" clause will not execute. For example, first enable archiving on a partitioned table rpt:
Sql> ALTER TABLE HR.RPT Flashback archive fda1;

Table altered.

The command to execute the swap partition will be replaced with a ORA-55610 error:
sql> ALTER TABLE HR.RPT Exchange partition P3 with table Hr.source;
ALTER TABLE HR.RPT Exchange partition P3 with table Hr.source
*
ERROR at line 1:
Ora-55610:invalid DDL statement on history-tracked table

Such a DDL can no longer be executed directly on the table using the archive. To achieve the purpose of the DDL Command, Oracle provides a way to temporarily detach the base table from the history table, making the base table (the RPT table in this case) a normal table without opening the archive function, then executing the DDL command that ORA-55610 the error on the RPT table, and then re-correlating the base table (RPT table). Among them, the implementation of the separation and correlation function is the PL/SQL package dbms_flashback_archive. You can start by looking at the name of the RPT history table:
Sql> Select Archive_table_name from Dba_flashback_archive_tables
2 where owner_name= ' HR ' and table_name= ' RPT ';

Archive_table_name
-----------------------------------------------------
sys_fba_hist_74619

It then uses the Dbms_flashback_archive DISASSIOCIATE_FBA procedure to sever the RPT table from its history table (sys_fba_hist_74619) so that all DDL commands are executed on both tables, and If the administrator wishes, you can also execute DML commands on the history table:
sql> exec dbms_flashback_archive.disassociate_fba (' HR ', ' RPT ');

PL/SQL procedure successfully completed.

DDL commands that report ORA-55610 errors before they are executed should now be unblocked:
sql> ALTER TABLE HR.RPT Exchange partition P3 with table Hr.source;

Table altered.

Finally, the RPT table and the history table are re-combined using the REASSOCIATE_FBA process:
sql> exec dbms_flashback_archive.reassociate_fba (' HR ', ' RPT ');

PL/SQL procedure successfully completed.

All DDL commands that encounter ORA-55610 errors can be handled in this way. Of course, if the DDL command changes the field structure of the base table, it must be modified on the history table before the REASSOCIATE_FBA procedure is executed. Common DDL commands are supported directly on the base table and, if necessary, Oracle will automatically reflect the changes on the history table, including the Add, drop, rename, modify, and "ALTER TABLE" command, add, Drop, rename constraint clause, DROP, TRUNCATE PARTITION clause for "ALTER TABLE" command, truncate command and rename command.

Flash Back Data archive (Flashback Archive)