Oracle 11g 新特性 Flashback Data Archive

Using Flashback Data Archive (Oracle Total Recall)

A Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

           FDA在有效期内保存和跟踪事务的改变信息

A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. If you are logged on as SYSDBA, you can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

       FDA 包含一个或多个表空间，可以创建多个FDA，当以sysdba登录时，可以指定默认FDA。FDA配置了保留时间，在保留时间内，数据归档是保留的。

By default, flashback archiving is off for any table. You can enable flashback archiving for a table if all of the following are true:

       默认情况下FDA是关闭的，当满足一下条件时，可以启用FDA：

• You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.

• The table is neither nested, clustered, temporary, remote, or external.

• The table contains neither LONG nor nested columns.

  有  FLASHBACK ARCHIVE 权限

  表不能是nested, clustered, temporary, remote, or external.

  表不包含LONG 或nested 列。

  


After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. While flashback archiving is enabled for a table, some DDL statements are not allowed on that table.

    要禁用FDA需要有FLASHBACK ARCHIVE ADMINISTER 或者以sysdba登录。若表启用了FDA，一些DDL语句将不被允许。

When choosing a Flashback Data Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Data Archives on which you have the FLASHBACK ARCHIVE object privilege.

     对使用了FDA的表，数据保留需求及保留时间需要有FLASHBACK ARCHIVE权限。

Topics:

• Creating a Flashback Data Archive

• Altering a Flashback Data Archive

• Dropping a Flashback Data Archive

• Specifying the Default Flashback Data Archive

• Enabling and Disabling Flashback Data Archive

• DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

• Viewing Flashback Data Archive Data

• Flashback Data Archive Scenarios

See Also:

http://www.oracle.com/database/total-recall.html for more information about Oracle Total Recall

Creating a Flashback Data Archive

Create a Flashback Data Archive with the CREATE FLASHBACK ARCHIVE statement, specifying the following:

         使用CREATE FLASHBACK ARCHIVE创建FDA

• Name of the Flashback Data Archive

  FDA 名

• Name of the first tablespace of the Flashback Data Archive

  FDA表空间名

• (Optional) Maximum amount of space that the Flashback Data Archive can use in the first tablespace

  The default is unlimited. Unless your space quota on the first tablespace is also unlimited, you must specify this value; otherwise, you will get error ORA-55621.

  可以使用的表空间配额。 

  默认不限制，除非第一个表空间是不限制的，不然必须指定值，否则会报： ORA-55621

• Retention time (number of days that Flashback Data Archive data for the table is guaranteed to be stored)

If you are logged on as SYSDBA, you can also specify that this is the default Flashback Data Archive for the system. If you omit this option, you can still make this Flashback Data Archive the default later (see Specifying the Default Flashback Data Archive).

      以sysdba登录，可以指定默认的FDA。若忽略，也可以以后设置。

Examples

• Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data will be retained for one year:

  CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
    QUOTA 10G RETENTION 1 YEAR;
  

• Create a Flashback Data Archive named fla2 that uses tablespace tbs2, whose data will be retained for two years:

  CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;
  

For more information about the CREATE FLASHBACK ARCHIVE statement, see Oracle Database SQL Language Reference.

Altering a Flashback Data Archive

  修改FDA

With the ALTER FLASHBACK ARCHIVE statement, you can:

     通过 ALTER FLASHBACK ARCHIVE 语句，可以修改：

• Change the retention time of a Flashback Data Archive

  修改FDA的保留时间

• Purge some or all of its data

  清除部分或全部数据

• Add, modify, and remove tablespaces

  增加，修改和删除FDA表空间

  Note:

  Removing all tablespaces of a Flashback Data Archive causes an error.

If you are logged on as SYSDBA, you can also use the ALTER FLASHBACK ARCHIVE statement to make a specific file the default Flashback Data Archive for the system.

    若以sysdba登录，也可以通过使用 ALTER FLASHBACK ARCHIVE 语句为系统指定默认FDA。

Examples

• Make Flashback Data Archive fla1 the default Flashback Data Archive:

   设置默认FDA

  ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
  

• To Flashback Data Archive fla1, add up to 5 G of tablespace tbs3:

  ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
  

• To Flashback Data Archive fla1, add as much of tablespace tbs4 as needed:

  ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs4;
  

• Change the maximum space that Flashback Data Archive fla1 can use in tablespace tbs3 to 20 G: 

  ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs3 QUOTA 20G;
  

• Allow Flashback Data Archive fla1 to use as much of tablespace tbs1 as needed:

  ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1;
  

• Change the retention time for Flashback Data Archive fla1 to two years:

  ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
  

• Remove tablespace tbs2 from Flashback Data Archive fla1:

  ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;
  

  (Tablespace tbs2 is not dropped.)

• Purge all historical data from Flashback Data Archive fla1:

  ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;
  

• Purge all historical data older than one day from Flashback Data Archive fla1:

  ALTER FLASHBACK ARCHIVE fla1
    PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
  

• Purge all historical data older than SCN 728969 from Flashback Data Archive fla1:

  ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;
  

For more information about the ALTER FLASHBACK ARCHIVE statement, see Oracle Database SQL Language Reference.

Dropping a Flashback Data Archive

Drop a Flashback Data Archive with the DROP FLASHBACK ARCHIVE statement. Dropping a Flashback Data Archive deletes its historical data, but does not drop its tablespaces.

  使用DROP FLASHBACK ARCHIVE 删除FDA。删除FDA只是删除它的历史数据而不是删除它的表空间。

Example

Remove Flashback Data Archive fla1 and all its historical data, but not its tablespaces:

DROP FLASHBACK ARCHIVE fla1;

For more information about the DROP FLASHBACK ARCHIVE statement, see Oracle Database SQL Language Reference.

Specifying the Default Flashback Data Archive

 设置默认FDA

By default, the system has no default Flashback Data Archive. If you are logged on as SYSDBA, you can specify one in one of the following ways:

系统默认木有FDA，使用sysdba登录，可以通过以下方式设置：

• Specify the name of an existing Flashback Data Archive in the SET DEFAULT clause of the ALTER FLASHBACK ARCHIVE statement. For example:

  ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
  

  If fla1 does not exist, an error occurs.

• Include DEFAULT in the CREATE FLASHBACK ARCHIVE statement when you create a Flashback Data Archive. For example:

  CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1
    QUOTA 10G RETENTION 1 YEAR;
  

The default Flashback Data Archive for the system is the default Flashback Data Archive for every user who does not have his or her own default Flashback Data Archive.

See Also:

• Oracle Database SQL Language Reference for more information about the CREATE FLASHBACK ARCHIVE statement

• Oracle Database SQL Language Reference for more information about the ALTER DATABASE statement

Enabling and Disabling Flashback Data Archive

启用和禁用FDA

By default, flashback archiving is disabled for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.

默认FDA是禁用的。要启用FDA需要有FLASHBACK ARCHIVE权限。

To enable flashback archiving for a table, include the FLASHBACK ARCHIVE clause in either the CREATE TABLE or ALTER TABLE statement. In the FLASHBACK ARCHIVEclause, you can specify the Flashback Data Archive where the historical data for the table will be stored. The default is the default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an error occurs.

在CREATE TABLE 或者ALTER TABLE语句中包含FLASHBACK ARCHIVE 子句来启用FDA。在FLASHBACK ARCHIVE 子句中你可以指定历史数据存储的FDA，默认是存储在系统默认的FDA，若指定了一个不存在的FDA，将返回错误信息。

If you enable flashback archiving for a table, but AUM is disabled, you will get error ORA-55614 when you try to modify the table.

当修改表时若表启用了闪回归档，但AUM是禁用的，将返回ORA-55614 错误。

If a table already has flashback archiving enabled, and you try to enable it again with a different Flashback Data Archive, an error occurs.

若表已经启用了FDA，当你试图再次启动并指定了其他的FDA，将返回错误信息。

After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. To disable flashback archiving for a table, specify NO FLASHBACK ARCHIVE in the ALTER TABLE statement. (It is unnecessary to specify NO FLASHBACKARCHIVE in the CREATE TABLE statement, because that is the default.)

禁用FDA，需要有FLASHBACK ARCHIVE ADMINISTER 权限或者是以sysdba登录。禁用FDA在ALTER TABLE 语句中指定NO FLASHBACK ARCHIVE。（在创建表时不需要指定 NO FLASHBACKARCHIVE 在 CREATE TABLE语句中，因为默认是禁止的）。

See Also:

Oracle Database SQL Language Reference for more information about the  FLASHBACK  ARCHIVE clause of the  CREATE  TABLEstatement, including restrictions on its use

Examples

• Create table employee and store the historical data in the default Flashback Data Archive:

  CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
    JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
  

• Create table employee and store the historical data in the Flashback Data Archive fla1:

  CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
    JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;
  

• Enable flashback archiving for the table employee and store the historical data in the default Flashback Data Archive:

  ALTER TABLE employee FLASHBACK ARCHIVE;
  

• Enable flashback archiving for the table employee and store the historical data in the Flashback Data Archive fla1:

  ALTER TABLE employee FLASHBACK ARCHIVE fla1;
  

• Disable flashback archiving for the table employee:

  ALTER TABLE employee NO FLASHBACK ARCHIVE;
  

DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

对启用了FDA的表，不允许使用的ＤＤＬ语句：

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:

在启用了FDA的表上，使用了下列DDL语句，都将返回ORA-55610:错误

• ALTER TABLE statement that does any of the following:

  ALTER TABLE 语句：

  • Drops, renames, or modifies a column

    删除，重命名或者修改列

  • Performs partition or subpartition operations

    执行分区或子分区操作

  • Converts a LONG column to a LOB column

    将LONG列转换为LOB列

  • Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

    包括UPGRADE TABLE 子句，有或没有INCLUDING DATA子句

• DROP TABLE statement

  删除表

• RENAME TABLE statement

  修改表名

• TRUNCATE TABLE statement

  TRUNCATE 表

See Also:

Oracle Database SQL Language Reference for information about these DDL statements

Viewing Flashback Data Archive Data

Table 13-3 lists and briefly describes the static data dictionary views that you can query for information about Flashback Data Archive files.

Table 13-3 Static Data Dictionary Views for Flashback Data Archive Files

View	Description
*_FLASHBACK_ARCHIVE	Displays information about Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TS	Displays tablespaces of Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TABLES	Displays information about tables that are enabled for Data Flashback Archive files.

See Also:

• Oracle Database Reference for detailed information about *_FLASHBACK_ARCHIVE

• Oracle Database Reference for detailed information about *_FLASHBACK_ARCHIVE_TS

• Oracle Database Reference for detailed information about *_FLASHBACK_ARCHIVE_TABLES

Flashback Data Archive Scenarios

使用场景

• Scenario: Using Flashback Data Archive to Enforce Digital Shredding

  数字分解

• Scenario: Using Flashback Data Archive to Access Historical Data

  访问历史数据

• Scenario: Using Flashback Data Archive to Generate Reports

  生成报告

• Scenario: Using Flashback Data Archive for Auditing

  审计

• Scenario: Using Flashback Data Archive to Recover Data

  恢复数据

Scenario: Using Flashback Data Archive to Enforce Digital Shredding

Your company wants to "shred" (delete) historical data changes to the Taxes table after ten years. When you create the Flashback Data Archive for Taxes, you specify a retention time of ten years:

CREATE FLASHBACK ARCHIVE taxes_archive TABLESPACE tbs1 RETENTION 10 YEAR;

When history data from transactions on Taxes exceeds the age of ten years, it is purged. (The Taxes table itself, and history data from transactions less than ten years old, are not purged.)

Scenario: Using Flashback Data Archive to Access Historical Data

You want to be able to retrieve the inventory of all items at the beginning of the year from the table inventory, and to be able to retrieve the stock price for each symbol in your portfolio at the close of business on any specified day of the year from the table stock_data.

Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data will be retained for five years (you must be logged on as SYSDBA):

CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
  QUOTA 10G RETENTION 5 YEAR;

Enable Flashback Data Archive for the tables inventory and stock_data, and store the historical data in the default Flashback Data Archive:

ALTER TABLE inventory FLASHBACK ARCHIVE;
ALTER TABLE stock_data FLASHBACK ARCHIVE;

To retrieve the inventory of all items at the beginning of the year 2007, use the following query:

SELECT product_number, product_name, count FROM inventory AS OF
  TIMESTAMP TO_TIMESTAMP ('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

To retrieve the stock price for each symbol in your portfolio at the close of business on July 23, 2007, use the following query:

SELECT symbol, stock_price FROM stock_data AS OF
  TIMESTAMP TO_TIMESTAMP ('2007-07-23 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE symbol IN my_portfolio;

Scenario: Using Flashback Data Archive to Generate Reports

You want users to be able to generate reports from the table investments, for data stored in the past five years.

Create a default Flashback Data Archive named fla2 that uses up to 20 G of tablespace tbs1, whose data will be retained for five years (you must be logged on as SYSDBA):

CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1
  QUOTA 20G RETENTION 5 YEAR;

Enable Flashback Data Archive for the table investments, and store the historical data in the default Flashback Data Archive:

ALTER TABLE investments FLASHBACK ARCHIVE;

Lisa wants a report on the performance of her investments at the close of business on December 31, 2006. She uses the following query:

SELECT * FROM investments AS OF
  TIMESTAMP TO_TIMESTAMP ('2006-12-31 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE name = 'LISA';

Scenario: Using Flashback Data Archive for Auditing

A medical insurance company must audit a medical clinic. The medical insurance company has its claims in the table Billings, and creates a default Flashback Data Archive named fla4 that uses up to 100 G of tablespace tbs1, whose data will be retained for 10 years:

CREATE FLASHBACK ARCHIVE DEFAULT fla4 TABLESPACE tbs1
  QUOTA 100G RETENTION 10 YEAR;

The company enables Flashback Data Archive for the table Billings, and stores the historical data in the default Flashback Data Archive:

ALTER TABLE Billings FLASHBACK ARCHIVE;

On May 1, 2007, clients were charged the wrong amounts for some diagnoses and tests. To see the records as of May 1, 2007, the company uses the following query:

SELECT date_billed, amount_billed, patient_name, claim_Id,
  test_costs, diagnosis FROM Billings AS OF
  TO_TIMESTAMP('2007-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

Scenario: Using Flashback Data Archive to Recover Data

An end user recovers from erroneous transactions that were previously committed in the database. The undo data for the erroneous transactions is no longer available, but because the required historical information is available in the Flashback Data Archive, Flashback Query works seamlessly.

Lisa manages a software development group whose product sales are doing well. On November 3, 2007, she decides to give all her level-three employees who have more than two years of experience a salary increase of 10% and a promotion to level four. Lisa asks her HR representative, Bob, to make the changes.

Using the HR web application, Bob updates the employee table to give Lisa's level-three employees a 10% raise and a promotion to level four. Then Bob finishes his work for the day and leaves for home, unaware that he omitted the requirement of two years of experience in his transaction. A few days later, Lisa checks to see if Bob has done the updates and finds that everyone in the group was given a raise! She calls Bob immediately and asks him to correct the error.

At first, Bob thinks he cannot return the employee table to its prior state without going to the backups. Then he remembers that the employee table has Flashback Data Archive enabled.

First, he verifies that no other transaction modified the employee table after his: The commit timestamp from the transaction query corresponds to Bob's transaction, two days ago.

Next, Bob uses the following statements to return the employee table to the way it was before his erroneous change:

DELETE EMPLOYEE WHERE MANAGER = 'LISA JOHNSON';
INSERT INTO EMPLOYEE
  SELECT * FROM EMPLOYEE
    AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY)
      WHERE MANAGER = 'LISA JOHNSON';

Bob then reexecutes the update that Lisa had requested.