13 Using Flashback Technology

Overview of Flashback Technology

Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:

• Perform queries that return past data

• Perform queries that return metadata that shows a detailed history of changes to the database

• Recover tables or rows to a previous point in time

• Automatically track and archive transactional data changes

• Roll back a transaction and its dependent transactions while the database remains online

Flashback features use the Automatic Undo Management (AUM) system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user executes an UPDATE statement to change a salary from 1000 to 1100, then Oracle Database stores the value 1000 in the undo data.

Undo data is persistent and survives a database shutdown. By using flashback features, you can employ undo data to query past data or recover from logical corruptions. Besides using it in flashback features, Oracle Database uses undo data to perform the following actions:

• Roll back active transactions

• Recover terminated transactions by using database or process recovery

• Provide read consistency for SQL queries

Topics:

• Application Development Features

• Database Administration Features

For additional general information about flashback features, see Oracle Database Concepts

Configuring Your Database for Flashback Technology

Before you can use flashback features in your application, you or your database administrator must perform the configuration tasks described in the following topics:

• Configuring Your Database for Automatic Undo Management

• Configuring Your Database for Flashback Transaction Query

• Configuring Your Database for Flashback Transaction Backout

• Enabling Flashback Operations on Specific LOB Columns

• Granting Necessary Privileges

Using Flashback Query (SELECT AS OF)

To use Flashback Query, use a SELECT statement with an AS OF clause. Flashback Query retrieves data as it existed at some time in the past. The query explicitly references a past time through a timestamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Flashback Query include:

• Recovering lost data or undoing incorrect, committed changes.

  For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.

• Comparing current data with the corresponding data at some time in the past.

  For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.

• Checking the state of transactional data at a particular time.

  For example, you can verify the account balance of a certain day.

• Simplifying application design by removing the need to store some kinds of temporal data.

  Flashback Query lets you retrieve past data directly from the database.

• Applying packaged applications, such as report generation tools, to past data.

• Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

Topics:

• Example of Examining and Restoring Past Data

• Guidelines for Flashback Query

For more information about the SELECT AS OF statement, see Oracle Database SQL Language Reference.

SELECT * FROM employees AS OF TIMESTAMP
   TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE last_name = 'Chung';

INSERT INTO employees
    (SELECT * FROM employees AS OF TIMESTAMP
     TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE last_name = 'Chung');

Using Flashback Version Query

Use Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT statement is executed.

Specify Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. The syntax is:

VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}

where start and end are expressions representing the start and end, respectively, of the time interval to be queried. The time interval includes (start and end).

Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudocolumns of metadata about the row version, described in Table 13-1. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.

A given row version is valid starting at its time VERSIONS_START* up to, but not including, its time VERSIONS_END*. That is, it is valid for any time t such that VERSIONS_START* <= t < VERSIONS_END*. For example, the following output indicates that the salary was 10243 from September 9, 2002, included, to November 25, 2003, excluded.

VERSIONS_START_TIME     VERSIONS_END_TIME     SALARY
-------------------     -----------------     ------
09-SEP-2003             25-NOV-2003           10243

Here is a typical use of Flashback Version Query:

SELECT versions_startscn, versions_starttime,
       versions_endscn, versions_endtime,
       versions_xid, versions_operation,
       name, salary
  FROM employees
  VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE name = 'JOE';

You can use VERSIONS_XID with Flashback Transaction Query to locate this transaction's metadata, including the SQL required to undo the row change and the user responsible for the change—see "Using Flashback Transaction Query".

Using Flashback Transaction Query

Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY. Use Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each change that the transaction made.

The following statement queries the FLASHBACK_TRANSACTION_QUERY view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code to undo the operation:

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
     FROM flashback_transaction_query
     WHERE xid = HEXTORAW('000200030000002D');

The following statement uses Flashback Version Query as a subquery to associate each row version with the LOGON_USER responsible for the row data change.

SELECT xid, logon_user FROM flashback_transaction_query
     WHERE xid IN
       (SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
        TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
        TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));

Using Flashback Transaction Query with Flashback Version Query

This example uses simple versions of the employees and departments tables in the sample HR schema.

In this example, a database administrator uses SQL*Plus to do the following:

CONNECT HR/password
CREATE TABLE emp 
   (empno   NUMBER PRIMARY KEY,
    empname VARCHAR2(16)
    salary  NUMBER);
INSERT INTO emp VALUES (111, 'Mike', 555);
COMMIT;

CREATE TABLE dept
   (deptno   NUMBER,
    deptname VARCHAR2(32));
INSERT INTO dept VALUES (10, 'Accounting');
COMMIT;

Now emp and dept have one row each. In terms of row versions, each table has one version of one row. Suppose that an erroneous transaction deletes empno 111 from table emp:

UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;

Next, a transaction reinserts empno 111 into the emp table with a new employee name:

INSERT INTO emp VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;

The database administrator detects the application error and needs to diagnose the problem. The database administrator issues the following query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Flashback Version Query pseudocolumns:

CONNECT dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
  versions_endscn END_SCN, versions_operation OPERATION,
  empname, salary FROM hr.emp
  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  where empno = 111;

XID              START_SCN  END_SCN   OPERATION  EMPNAME    SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855               I          Tom        927
000200030000002D 113564               D          Mike       555
000200030000002E 112670     113564    I          Mike       555
3 rows selected

The results table rows are in descending chronological order. The third row corresponds to the version of the row in the table emp that was inserted in the table when the table was created. The second row corresponds to the row in emp that the erroneous transaction deleted. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.

The database administrator identifies transaction 000200030000002D as the erroneous transaction and uses Flashback Transaction Query to audit all changes made by this transaction:

SELECT  xid, start_scn START, commit_scn COMMIT,
        operation OP, logon_user USER,
        undo_sql FROM flashback_transaction_query
        WHERE xid = HEXTORAW('000200030000002D');

XID               START   COMMIT  OP       USER   UNDO_SQL
----------------  -----   ------  --       ----   ---------------------------
000200030000002D  195243  195244  DELETE   HR     insert into "HR"."EMP" 
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

000200030000002D  195243  195244  INSERT   HR     delete from "HR"."DEPT" 
where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D  195243  195244  UPDATE   HR     update "HR"."EMP" 
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';

000200030000002D  195243  113565  BEGIN  HR

4 rows selected

The (UNDO_SQL) column contains the SQL code that the database administrator can execute to undo the changes made by that transaction. The USER column (logon_user) shows the user responsible for the transaction.

To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs the following query:

SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
  FROM flashback_transaction_query
  WHERE table_owner = 'HR' AND
        start_timestamp >=
          TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');

XID               START_SCN  COMMIT_SCN  OPERATION  TABLE_NAME  TABLE_OWNER
----------------  ---------  ----------  ---------  ----------  -----------
0004000700000058  195245     195246      UPDATE     EMP         HR
0004000700000058  195245     195246      UPDATE     EMP         HR
0004000700000058  195245     195246      INSERT     EMP         HR
000200030000002D  195243     195244      DELETE     EMP         HR
000200030000002D  195243     195244      INSERT     DEPT        HR
000200030000002D  195243     195244      UPDATE     EMP         HR

6 rows selected

Using ORA_ROWSCN

ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row; that is, the latest COMMIT operation for the row. For example:

SELECT ora_rowscn, last_name, salary
  FROM employees
  WHERE employee_id = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    202553    Fudd      3000

The latest COMMIT operation for the row took place at approximately SCN 202553. To convert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP.

ORA_ROWSCN is a conservative upper bound of the latest commit time—the actual commit SCN can be somewhat earlier. ORA_ROWSCN is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE with the ROWDEPENDENCIES clause).

Uses of ORA_ROWSCN in application development include concurrency control and client cache invalidation.

Scenario: Concurrency Control

Your application examines a row of data and records the corresponding ORA_ROWSCN as 202553. Later, the application needs to update the row, but only if the row has not changed. The operation is made conditional on the ORA_ROWSCN being still 202553. An equivalent interactive statement is:

UPDATE employees
  SET salary = salary + 100
  WHERE employee_id = 7788
  AND ora_rowscn = 202553;

0 rows updated.

The conditional update fails in this case, because the ORA_ROWSCN is no longer 202553. This means that a user or another application changed the row and performed a COMMIT more recently than the recorded ORA_ROWSCN.

Your application queries again to obtain the new row data and ORA_ROWSCN. Suppose that the ORA_ROWSCN is now 415639. The application tries the conditional update again, using the new ORA_ROWSCN. This time, the update succeeds, and it is committed. An interactive equivalent is:

SQL> UPDATE employees SET salary = salary + 100
     WHERE empno = 7788 AND ora_rowscn = 415639;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT ora_rowscn, name, salary FROM employees WHERE empno = 7788;

ORA_ROWSCN    NAME    SALARY
----------    ----    ------
    465461    Fudd      3100

The SCN corresponding to the new COMMIT is 465461.

Besides using ORA_ROWSCN in an UPDATE statement WHERE clause, you can use it in a DELETE statement WHERE clause or the AS OF clause of Flashback Query.

Using DBMS_FLASHBACK Package

The DBMS_FLASHBACK package provides the same functionality as Flashback Query, but Flashback Query is sometimes more convenient.

The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, carry out normal queries as if you were at that time in the past, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at times in the past.

You must have the EXECUTE privilege on the DBMS_FLASHBACKpackage.

To use the DBMS_FLASHBACK package in your PL/SQL code:

1. Specify a past time by invoking either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.

2. Perform regular queries (that is, queries without special flashback-feature syntax such as AS OF). Do not perform DDL or DML operations.

   The database is queried at the specified past time.

3. Return to the present time by invoking DBMS_FLASHBACK.DISABLE.

   You must invoke DBMS_FLASHBACK.DISABLE before invoking DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER again. You cannot nest enable/disable pairs.

You can use a cursor to store the results of queries. To do this, open the cursor before invoking DBMS_FLASHBACK.DISABLE. After storing the results and invoking DBMS_FLASHBACK.DISABLE, you can do the following:

• Perform INSERT or UPDATE operations to modify the current database state by using the stored results from the past.

• Compare current data with the past data. After invoking DBMS_FLASHBACK.DISABLE, open a second cursor. Fetch from the first cursor to retrieve past data; fetch from the second cursor to retrieve current data. You can store the past data in a temporary table and then use set operators such as MINUS or UNION to contrast or combine the past and current data.

You can invoke DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER at any time to get the current System Change Number (SCN). DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER always returns the current SCN regardless of previous invocations of DBMS_FLASHBACK.ENABLE.

Using Flashback Transaction Backout

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure ("TRANSACTION_BACKOUT") rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and execute the compensating transactions that return the affected data to its original state.

Topics:

• TRANSACTION_BACKOUT Parameters

• TRANSACTION_BACKOUT Reports

Using Flashback Data Archives

A Flashback Data Archive 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.

A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. 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.

By default, flashback archiving is off for any table. You can enable flashback archiving (and then disable it again) for a table. While flashback archiving is enabled for a table, some DDL statements are not allowed on that table.

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.

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

DROP FLASHBACK ARCHIVE fla1;

CREATE FLASHBACK ARCHIVE taxes_archive TABLESPACE tbs1 RETENTION 10 YEAR;

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

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

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');

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;

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

ALTER TABLE investments FLASHBACK ARCHIVE;

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

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

ALTER TABLE Billings FLASHBACK ARCHIVE;

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');

FLASHBACK TABLE employee TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY);

INSERT INTO employee SELECT * FROM employee AS OF TIMESTAMP
  TO_TIMESTAMP('2007-11-2 23:00:00', 'YYYY-MM-DD HH24:MI:SS')
  WHERE name = 'JOE';

General Guidelines for Flashback Technology

• Use the DBMS_FLASHBACK.ENABLE and DBMS_FLASHBACK.DISABLE procedures around SQL code that you do not control, or when you want to use the same past time for several consecutive queries.

• Use Flashback Query, Flashback Version Query, or Flashback Transaction Query for SQL code that you write, for convenience. A Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.

• To obtain an SCN to use later with a flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.

• To compute or retrieve a past time to use in a query, use a function return value as a timestamp or SCN argument. For example, add or subtract an INTERVAL value to the value of the SYSTIMESTAMP function.

• Use Flashback Query, Flashback Version Query, and Flashback Transaction Query locally or remotely. An example of a remote Flashback Query is:

  (SELECT * FROM employees@some_remote_host AS OF 
      TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  

• To ensure database consistency, always perform a COMMIT or ROLLBACK operation before querying past data.

• Remember that all flashback processing uses the current session settings, such as national language and character set, not the settings that were in effect at the time being queried.

• Remember that DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, you will get error ORA-1466. DDL operations that alter the storage attributes of a table (such as PCTFREE, INITRANS, and MAXTRANS) do not invalidate undo data.

• To query past data at a precise time, use an SCN. If you use a timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Oracle Database uses SCNs internally and maps them to timestamps at a granularity of 3 seconds.

  For example, suppose that the SCN values 1000 and 1005 are mapped to the timestamps 8:41 AM and 8:46 AM, respectively. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; a Flashback Query for 8:46 AM is mapped to SCN 1005.

  Due to this time-to-SCN mapping, if you specify a time that is slightly after a DDL operation (such as a table creation) Oracle Database might use an SCN that is just before the DDL operation, causing error ORA-1466.

• You cannot retrieve past data from a dynamic performance (V$) view. A query on such a view always returns current data.

• You can perform queries on past data in static data dictionary views, such as *_TABLES.

Performance Guidelines for Flashback Technology

• Use the DBMS_STATS package to generate statistics for all tables involved in a Flashback Query. Keep the statistics current. Flashback Query uses the cost-based optimizer, which relies on these statistics.

• Minimize the amount of undo data that must be accessed. Use queries to select small sets of past data using indexes, not to scan entire tables. If you must scan a full table, add a parallel hint to the query.

  The performance cost in I/O is the cost of paging in data and undo blocks that are not already in the buffer cache. The performance cost in CPU use is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback features essentially CPU bound.

• For Flashback Version Query, use index structures. Oracle Database keeps undo data for index changes as well as data changes. Performance of index lookup-based Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.

• In a Flashback Transaction Query, the xid column is of the type RAW(8). To take advantage of the index built on the xid column, use the HEXTORAW conversion function: HEXTORAW(xid).

• A Flashback Query against a materialized view does not take advantage of query rewrite optimization.