| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 1 (10.1) Part Number B10795-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 1 (10.1) Part Number B10795-01 |
|
|
View PDF |
This chapter discusses the following flashback topics:
See Also:
|
Oracle Database has a group of features, known collectively as flashback, that provide ways to view past states of database objects, or to return database objects to a previous state, without using traditional point-in-time recovery.
Flashback features of the database can be used to:
Flashback features use the Automatic Undo Management system to obtain metadata and historical data for transactions. They rely on undo data: records of the effects of individual transactions. Undo data is persistent and survives a database malfunction or shutdown. Using flashback features, you employ undo data to query past data or recover from logical corruptions. Besides your use of it in flashback operations, undo data is used by Oracle Database to do the following:
| See Also:
Oracle Database Concepts for more information about flashback features and automatic undo management |
In application development, flashback features can be used to report on historical data or undo erroneous changes. Flashback features that allow you to do this include:
AS OF clause of the SELECT statement.VERSIONS BETWEEN clause of the SELECT statement to create a Flashback Version Query.FLASHBACK_TRANSACTION_QUERY view.You can use the DBMS_FLASHBACK package, Flashback Query, Flashback Version Query, and Flashback Transaction Query for application development or interactively, as a database user or administrator.
Other flashback features are typically used only in database administration tasks:
DROP TABLE statement.Flashback Database, Flashback Table, and Flashback Drop are primarily provided as data recovery mechanisms and are therefore documented elsewhere. The other flashback features, while valuable in data recovery scenarios, are also used in contexts such as application development. They are therefore the focus of this chapter.
See Also:
|
Before you can use flashback features in your application, the following administrative tasks must be performed to configure your database. Consult with your database administrator to perform these tasks.
UNDO_MANAGEMENT, UNDO_TABLESPACE, and UNDO_RETENTION.RETENTION GUARANTEE clause for the undo tablespace, to ensure that unexpired undo is not discarded - UNDO_RETENTION is not, by itself, a strict guarantee. If the system is under space pressure, then unexpired undo may be overwritten with freshly generated undo; RETENTION GUARANTEE prevents this.EXECUTE privilege on DBMS_FLASHBACK to provide access to the features in this package.FLASHBACK and SELECT privileges on specific objects to be accessed during queries, or grant the FLASHBACK ANY TABLE privilege to allow queries on all tables.SELECT ANY TRANSACTION privilege.SELECT, UPDATE, DELETE, and INSERT privileges for specific tables, as appropriate, to permit execution of undo SQL code retrieved by a Flashback Transaction Query.LOB columns of a table, use the ALTER TABLE command with the RETENTION option. Because undo data for LOB columns can be voluminous, you must define which LOB columns to use with flashback operations.
See Also:
|
You perform a Flashback Query using a SELECT statement with an AS OF clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.
Potential uses of Flashback Query include:
| See Also:
Oracle Database SQL Reference for details on the syntax of the |
This example uses a Flashback Query to examine the state of a table at a previous time. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JOHN had been deleted from the employee table, and the DBA knows that at 9:30AM the data for JOHN was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data had been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2003:
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN';
This update then restores John's information to the employee table:
INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN');
Keep the following in mind when using a Flashback Query (SELECT ... AS OF):
AS OF clause for each table, and specify different times for different tables. Use an AS OF clause in a query to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as the query.AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.AS OF clause in the SELECT statement that defines the view. If you specify a relative time by subtracting from SYSDATE, the past time is recalculated for each query. For example:
CREATE VIEW hour_ago AS SELECT * FROM employee AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
Note that shortly after a change in daylight savings time, SYSDATE - 1 might refer to either 23 or 25 hours ago, not 24.
AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, in order to extract or compare data from two different times. You can store the results by preceding a Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, this query re-inserts into table employee the rows that were present there an hour ago:
INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)) MINUS SELECT * FROM employee);
The DBMS_FLASHBACK package generally provides the same functionality as Flashback Query, but Flashback Query can sometimes be more convenient to use.
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, 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, without change, to interrogate the database at times in the past.
You must have the EXECUTE privilege on the DBMS_FLASHBACK package.
To use the DBMS_FLASHBACK package in your PL/SQL code:
DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to turn back the clock to a given time in the past. After this, all queries retrieve data that was current at the specified time.AS OF). The database is automatically queried at the specified past time. Perform only queries; do not try to perform DDL or DML operations.DBMS_FLASHBACK.DISABLE to return to the present. (You must call DISABLE before calling ENABLE... again for a different time. You cannot nest ENABLE /DISABLE pairs.)You can use a cursor to store the results of queries into the past. To do this, open the cursor before calling DBMS_FLASHBACK.DISABLE. After storing the results and then calling DISABLE, you can do the following:
INSERT or UPDATE operations, to modify the current database state using the stored results from the past.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 call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER at any time to obtain the current System Change Number (SCN). Note that the current SCN is always returned; this takes no account of previous calls to DBMS_FLASHBACK.ENABLE*.
See Also:
|
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:
SQL> SELECT ora_rowscn, name, salary FROM employee WHERE empno = 7788; ORA_ROWSCN NAME SALARY ---------- ---- ------ 202553 Fudd 3000
The latest COMMIT operation for the row took place at approximately SCN 202553. (You can use function SCN_TO_TIMESTAMP to convert an SCN, like ORA_ROWSCN, to the corresponding TIMESTAMP value.)
ORA_SCN is in fact a conservative upper bound of the latest commit time: the actual commit SCN can be somewhat earlier. ORA_SCN is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE with the ROWDEPENDENCIES clause).
Noteworthy uses of ORA_ROWSCN in application development include concurrency control and client cache invalidation. To see how you might use it in concurrency control, consider the following scenario.
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 its record of the data is still accurate. That is, this particular update operation depends, logically, on the row not having been changed. The operation is therefore made conditional on the ORA_ROWSCN being still 202553. Here is an equivalent interactive command:
SQL> UPDATE employee SET salary = salary + 100 WHERE empno = 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 some 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. Here is an interactive equivalent:
SQL> UPDATE employee 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 employee 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 a Flashback Query.
You use a 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.
You specify a Flashback Version Query using the VERSIONS BETWEEN clause of the SELECT statement. Here is the syntax:
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
where start and end are expressions representing the start and end of the time interval to be queried, respectively. The interval is closed at both ends: the upper and lower limits specified (start and end) are both included in the time interval.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version, described in Table 15-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, not included.
VERSIONS_START_TIME VERSIONS_END_TIME SALARY ------------------- ----------------- ------ 09-SEP-2003 25-NOV-2003 10243
Here is a typical Flashback Version Query:
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, name, salary FROM employee 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';
Pseudocolumn VERSIONS_XID provides a unique identifier for the transaction that put the data in that state. You can use this value in connection with a Flashback Transaction Query to locate metadata about this transaction in the FLASHBACK_TRANSACTION_QUERY view, including the SQL required to undo the row change and the user responsible for the change - see "Using Flashback Transaction Query".
| See Also:
Oracle Database SQL Reference for information on the Flashback Version Query pseudocolumns and the syntax of the |
A Flashback Transaction Query is a query on the view FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.
| See Also:
Oracle Database Backup and Recovery Advanced User's Guide. and Oracle Database Administrator's Guide for information on how a DBA can use the Flashback Table feature to restore an entire table, rather than individual rows |
As an example, 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');
As another example, the following query uses a 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 employee 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'));
This example demonstrates the use of a Flashback Transaction Query in conjunction with a Flashback Version Query. The example assumes simple variations of the employee and departments tables in the sample hr schema.
In this example, a DBA carries out the following series of actions in SQL*Plus:
connect hr/hr 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;
At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 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;
Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.
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;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA 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 reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp that was originally inserted in the table when the table was created. The second row corresponds to the row in emp that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D as the erroneous transaction and issues the following 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 rightmost column (undo_sql) contains the SQL code that will undo the corresponding change operation. The DBA can execute this code to undo the changes made by that transaction. The USER column (logon_user) shows the user responsible for the transaction.
A DBA might also be interested in knowing all changes made in a certain time window. In our scenario, the DBA performs the following query to view the details of all transactions that executed since the erroneous transaction identified earlier (including the erroneous transaction itself):
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
The following tips and restrictions apply to using flashback features.
DBMS_STATS package, and keep the statistics current. Flashback Query always uses the cost-based optimizer, which relies on these statistics.xid column is RAW(8). To take advantage of the index built on the xid column, use the HEXTORAW conversion function: HEXTORAW(xid).DBMS_FLASHBACK package or other flashback features? Use ENABLE/DISABLE calls to the DBMS_FLASHBACK package 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 that you write, for convenience. A Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.INTERVAL value to the value of the SYSTIMESTAMP function.(SELECT * FROM employee@some_remote_host AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
COMMIT or ROLLBACK operation before querying past data.PCTFREE, INITTRANS and MAXTRANS.For example, assume that the SCN values 1000 and 1005 are mapped to the times 8:41 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) the database might actually use an SCN that is just before the DDL operation. This can result in error ORA-1466.
V$ view in the data dictionary. Performing a query on such a view always returns the current data. You can, however, perform queries on past data in other views of the data dictionary, such as USER_TABLES.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|