Oracle Flashback Data Archive

Using Flashback Data Archive (Oracle Total Recall)
A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime.
A Flashback Data Archive is useful for compliance with record stage policies and audit reports.
 
Topics:
(1)Creating a Flashback Data Archive
(2)Altering a Flashback Data Archive
(3)Dropping a Flashback Data Archive
(4)Specifying the Default Flashback Data Archive
(5)Enabling and Disabling Flashback Data Archive
(6)DDL Statements on Tables Enabled for Flashback Data Archive
(7)Viewing Flashback Data Archive Data
(8)Flashback Data Archive Scenarios

Examples
Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data are 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 are 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.

With the ALTER FLASHBACK ARCHIVE statement, you can:
(1)Change the retention time of a Flashback Data Archive
(2)Purge some or all of its data
(3)Add, modify, and remove tablespaces

Examples

(1)Make Flashback Data Archive fla1 the default Flashback Data Archive:
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

(2)To Flashback Data Archive fla1, add up to 5 G of tablespace tbs3:
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;

(3)To Flashback Data Archive fla1, add as much of tablespace tbs4 as needed:
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs4;

(4)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;

(5)Allow Flashback Data Archive fla1 to use as much of tablespace tbs1 as needed:
ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1;

(6)Change the retention time for Flashback Data Archive fla1 to two years:
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;

(7)Remove tablespace tbs2 from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;
(Tablespace tbs2 is not dropped.)

(8)Purge all historical data from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;

(9)Purge all historical data older than one day from Flashback Data Archive fla1:
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

(10)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.

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.

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.

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

(1)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.

(2)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.

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 to use for that table.
To enable flashback archiving for a table, include the FLASHBACK ARCHIVE clause in either the CREATE TABLE or ALTER TABLE statement. In the FLASHBACK ARCHIVE clause, you can specify the Flashback Data Archive where the historical data for the table are stored. The default is the default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an error occurs.
If you enable flashback archiving for a table, but AUM is disabled, error ORA-55614 occurs when you try to modify the table.
If a table has flashback archiving enabled, and you try to enable it again with a different Flashback Data Archive, an error occurs.
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 FLASHBACK ARCHIVE in the CREATE TABLE statement, because that is the default.)

Examples

(1)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;

(2)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;

(3)Enable flashback archiving for the table employee and store the historical data in the default Flashback Data Archive:
ALTER TABLE employee FLASHBACK ARCHIVE;

(4)Enable flashback archiving for the table employee and store the historical data in the Flashback Data Archive fla1:
ALTER TABLE employee FLASHBACK ARCHIVE fla1;

(5)Disable flashback archiving for the table employee:
ALTER TABLE employee NO FLASHBACK ARCHIVE;

(1)Flashback Data Archive supports only these DDL statements:
ALTER TABLE statement that does any of the following:
Adds, drops, renames, or modifies a column;
Adds, drops, or renames a constraint;
Drops or truncates a partition or subpartition operation;
TRUNCATE TABLE statement;
RENAME statement that renames a table;

(2)Flashback Data Archive does not support DDL statements that move, split, merge, or coalesce partitions or subpartitions, move tables, or convert LONG columns to LOB columns.
For example, the following DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive:
ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause;
ALTER TABLE statement that moves or exchanges a partition or subpartition operation;
DROP TABLE statement;

If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. 
To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.

Table 12-3 lists and briefly describes the static data dictionary views that you can query for information about Flashback Data Archive files.
Table 12-3 Static Data Dictionary Views for Flashback Data Archive Files
(1)
View: *_FLASHBACK_ARCHIVE
Description:Displays information about Flashback Data Archive files.
(2)
View: *_FLASHBACK_ARCHIVE_TS
Description:Displays tablespaces of Flashback Data Archive files.
(3)
View: *_FLASHBACK_ARCHIVE_TABLES
Description:Displays information about tables that are enabled for Data Flashback Archive files;

(1)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.)

(2)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 are 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 this 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 this 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;

(3)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 are 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 this query:
SELECT * FROM investments AS OF TIMESTAMP TO_TIMESTAMP ('2006-12-31 16:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE name = 'LISA';

(4)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 are 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 this query:
SELECT date_billed, amount_billed, patient_name, claim_Id,test_costs, diagnosis FROM Billings AS OF TIMESTAMP TO_TIMESTAMP('2007-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

(5)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 time stamp from the transaction query corresponds to Bob's transaction, two days ago.
Next, Bob uses these 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.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！