本文介绍了Oracle数据库中的闪回技术，包括闪回查询、闪回版本查询、闪回事务查询等功能，详细阐述了这些功能的使用场景、配置方法及权限要求，并提供了实际应用案例。
Oracle把闪回技能分为Database Administration Features与Application Development Features：Database Administration Features供DBA使用，主要用于数据恢复，它包括Oracle Flashback Table，Oracle Flashback Drop以及Oracle Flashback Database，在官方手册的《Database Backup and Recovery User's Guide》中介绍
Application Development Features供开发者使用，用于报告历史数据或撤消错误的更改，它包括Oracle Flashback Query，Oracle Flashback Version Query，Oracle Flashback Transaction Query，DBMS_FLASHBACK Package，Flashback Transaction以及Flashback Data Archive，它们均依赖于UNDO DATA，在官方手册的《Database Development Guide》中介绍
本文为介绍Application Development Features
（一）General Guidelines for Oracle Flashback Technology
1) 闪因查询、闪回版本查询以及闪回事务查询均可以远程执行
Use Oracle Flashback Query, Oracle Flashback Version Query, and Oracle Flashback Transaction Query locally or remotely. An example of a remote Oracle Flashback Query is:
SELECT * FROM employees@some_remote_host AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
- 在查询旧数据时最好先执行COMMIT或ROLLBACK
To ensure database consistency, perform a COMMIT or ROLLBACK 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.
- DDL导致表结构变化会导致之前UNDO不可用
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, error ORA-01466 occurs. DDL operations that alter the storage attributes of a table (such as PCTFREE, INITRANS, and MAXTRANS) do not invalidate undo data.
- 闪回旧时间最好使用SCN更准确
To query past data at a precise time, use an SCN. If you use a time stamp, 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 time stamps at a granularity of 3 seconds.
For example, suppose that the SCN values 1000 and 1005 are mapped to the time stamps 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; an Oracle Flashback Query for 8:46 AM is mapped to SCN 1005. Therefore, 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 immediately before the DDL operation, causing error ORA-01466.
- 无法对动态视图进行闪回，它永远只记录当前数据；但可以闪回数据字典视图
You cannot retrieve past data from a dynamic performance (V$) view. A query on such a view returns current data.
You can perform queries on past data in static data dictionary views, such as *_TABLES.
- 可以对Flashback Data Archive指定OPTIMIZE DATA
You can enable optimization of data storage for history tables maintained by Flashback Data Archive by specifying OPTIMIZE DATA when creating or altering a Flashback Data Archive.
OPTIMIZE DATA optimizes the storage of data in history tables by using any of these features:
- Advanced Row Compression
- Advanced LOB Compression
- Advanced LOB Deduplication
- Segment-level compression tiering
- Row-level compression tiering
The default is not to optimize the storage of data in history tables.
Caution: Importing user-generated history can lead to inaccurate, or unreliable results. This procedure should only be used after consulting with Oracle Support.
（二）Configuring Your Database for Oracle Flashback
详见《Managing Undo》
To configure your database for the Oracle Flashback Transaction Query feature, you or your database administrator must:
- Ensure that Oracle Database is running with version 10.0 compatibility.
- Enable supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- Configuring Your Database for Flashback Transaction
To configure your database for the Flashback Transaction feature, you or your database administrator must:
- With the database mounted but not open, enable ARCHIVELOG:
ALTER DATABASE ARCHIVELOG;
- Open at least one archive log:
ALTER SYSTEM ARCHIVE LOG CURRENT;
- If not done, enable minimal and primary key supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
- If you want to track foreign key dependencies, enable foreign key supplemental logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
Note: If you have very many foreign key constraints, enabling foreign key supplemental logging might not be worth the performance penalty.
To enable flashback operations on specific LOB columns of a table, use the ALTER TABLE statement with the RETENTION option.
Because undo data for LOB columns can be voluminous, you must define which LOB columns to use with flashback operations.
You or your database administrator must grant privileges to users, roles, or applications that must use these flashback features.
- For Oracle Flashback Query and Oracle Flashback Version Query
To allow access to specific objects during queries, grant FLASHBACK and either READ or SELECT privileges on those objects.
To allow queries on all tables, grant the FLASHBACK ANY TABLE privilege.
- For Oracle Flashback Transaction Query
Grant the SELECT ANY TRANSACTION privilege.
To allow execution of undo SQL code retrieved by an Oracle Flashback Transaction Query, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables.
- For DBMS_FLASHBACK Package
To allow access to the features in the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK.
- For Flashback Data Archive
To allow a specific user to enable Flashback Data Archive on tables, using a specific Flashback Data Archive, grant the FLASHBACK ARCHIVE object privilege on that Flashback Data Archive to that user. To grant the FLASHBACK ARCHIVE object privilege, you must either be logged on as SYSDBA or have FLASHBACK ARCHIVE ADMINISTER system privilege.
To allow execution of these statements, grant the FLASHBACK ARCHIVE ADMINISTER system privilege:
- CREATE FLASHBACK ARCHIVE
- ALTER FLASHBACK ARCHIVE
- DROP FLASHBACK ARCHIVE
To grant the FLASHBACK ARCHIVE ADMINISTER system privilege, you must be logged on as SYSDBA.
To create a default Flashback Data Archive, using either the CREATE FLASHBACK ARCHIVE or ALTER FLASHBACK ARCHIVE statement, you must be logged on as SYSDBA.
To disable Flashback Data Archive for a table that has been enabled for Flashback Data Archive, you must either be logged on as SYSDBA or have the FLASHBACK ARCHIVE ADMINISTER system privilege.
（三）Using Oracle Flashback Query (SELECT AS OF)
Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.
Note1: If a table is a Flashback Data Archive and you specify a time for it that is earlier than its creation time, the query returns zero rows for that table, rather than causing an error.
Note2: You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view. If you specify a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For example:
CREATE VIEW hour_ago AS
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
SYSTIMESTAMP refers to the time zone of the database host environment.
Example: Examining and Restoring Past Data
Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the employees table, and you know that at 9:30AM the data for Chung was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.
Example 19-1 Retrieving a Lost Row with Oracle Flashback Query
SELECT * FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
Example 19-2 Restoring a Lost Row After Oracle Flashback Query
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 Oracle Flashback Version Query(VERSION BETWEEN)
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.
Note: After executing a CREATE TABLE statement, wait at least 15 seconds to commit any transactions, to ensure that Oracle Flashback Version Query reflects those transactions.
The syntax is either:
VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end
VERSIONS PERIOD FOR user_valid_time [ BETWEEN TIMESTAMP start AND end ]
Oracle 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, which can reveal when and how a particular change (perhaps erroneous) occurred to your database.
Table 19-1 describes the pseudocolumns of metadata about the row version.
| Pseudocolumn Name | Description |
| VERSIONS_STARTSCN VERSIONS_STARTTIME | Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudo column identifies the time when the data first had the values reflected in the row version. Use this pseudo column to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. If this pseudocolumn is NULL, then the row version was created before start. 如果为空表示开始时间早于你指定的VERSIONS BETWEEN TIMESTAMP的起始时间 |
| VERSIONS_ENDSCN VERSIONS_ENDTIME | SCN or TIMESTAMP when the row version expired. If this pseudo column is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation. 如果为空表示进行了delete操作或是在你指定的VERSIONS BETWEEN TIMESTAMP结果时间一直保持此状态 |
| VERSIONS_XID | Identifier of the transaction that created the row version. |
| VERSIONS_OPERATION | Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION. |
注时间区间是[ version_start*,version_end*)，可使用MINVALUE、MAXVALUE表示可查询最早时间到当前的各版本
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*.
Note: Oracle Database recommends to avoid the usage of versions_starttime, versions_endtime or scn_to_timestamp columns in VERSIONS queries (including CTAS queries) to improve the performance.
Here is a typical use of Oracle Flashback Version Query:
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE first_name = 'John';
VERSION_XID伪列可以用于定位Oracle Flashback Transaction Query事务
You can use VERSIONS_XID with Oracle 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.
Note: Flashback Version Query allows index-only access only with IOTs (index-organized tables), but index fast full scan is not allowed.
（五）Using Oracle Flashback Transaction Query (FLASHBACK_TRANSACTION_QUERY)
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY.
FLASHBACK_TRANSACTION_QUERY的UNDO_SQL会显示undo操作
The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted.
This 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 that shows the logical opposite of the operation:
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
This statement uses Oracle 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')
);
Example:Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
闪回版本查询可以查看各版本的列值及对应的XID，看不了xid执行了什么；而闪回事务查询无法查看列值但可以查看具体事务XID执行了什么(准确说是UNDO SQL)，因此两者可以很好互补查询，通过闪回版本查询来定位数据变化对应的XID，再用XID查看闪回事务查询的UNDO SQL
In this example, a database administrator does this:
DROP TABLE emp;
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(16), salary NUMBER);
INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);
COMMIT;
DROP TABLE dept;
CREATE TABLE dept ( deptno NUMBER, deptname VARCHAR2(32));
INSERT INTO dept (deptno, deptname) VALUES (10, 'Accounting');
COMMIT;
Suppose that an erroneous transaction deletes empno 111 from table emp:
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept (deptno, deptname) 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 (empno, empname, salary) 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 must diagnose the problem. The database administrator issues this query to retrieve versions of the rows in the emp table that correspond to empno 111. The query uses Oracle Flashback Version Query pseudocolumns:
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno = 111;
Results are similar to:
XID START_SCN END_SCN O EMPNAME SALARY
---------------- ---------- ---------- - ---------------- ----------
09001100B2200000 10093466 I Tom 927
030002002B210000 10093459 D Mike 555
0800120096200000 10093375 10093459 I Mike 555
3 rows selected.
The database administrator identifies transaction 030002002B210000 as the erroneous transaction and uses Oracle Flashback Transaction Query to audit all changes made by this transaction:
SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
Note: In an Oracle 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). 但这里不清楚为什么不是HEXTORAW('030002002B210000')
Results are similar to:
XID START_SCN COMMIT_SCN OPERATION LOGON_USER UNDO_SQL
--------------------------------------------------------------------------------------------------------------
030002002B210000 10093452 10093459 DELETE HR
insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
030002002B210000 10093452 10093459 INSERT HR
delete from "HR"."DEPT" where ROWID = 'AAATjuAAEAAAAJrAAB';
030002002B210000 10093452 10093459 UPDATE HR
update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA';
030002002B210000 10093452 10093459 BEGIN HR
4 rows selected.
To make the result of the next query easier to read, the database administrator uses these SQL*Plus commands:
COLUMN operation FORMAT A9
COLUMN table_name FORMAT A10
COLUMN table_owner FORMAT A11
To see the details of the erroneous transaction and all subsequent transactions, the database administrator performs this 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');
Results are similar to:
XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
---------------- ---------- ---------- --------- ---------- -----------
02000E0074200000 10093435 10093446 INSERT DEPT HR
030002002B210000 10093452 10093459 DELETE EMP HR
030002002B210000 10093452 10093459 INSERT DEPT HR
030002002B210000 10093452 10093459 UPDATE EMP HR
0800120096200000 10093374 10093375 INSERT EMP HR
09001100B2200000 10093462 10093466 UPDATE EMP HR
09001100B2200000 10093462 10093466 UPDATE EMP HR
09001100B2200000 10093462 10093466 INSERT EMP HR
（六）Using DBMS_FLASHBACK Package
1. FLASHBACK QUERY USING DBMS_FLASHBACK
使用DBMS_FLASHBACK与闪回查询效果相同，它相当于时间机器，回到之前时间后所有查询都是那个时间点的数据
The DBMS_FLASHBACK package provides the same functionality as Oracle Flashback Query, but Oracle Flashback Query is sometimes more convenient.
The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, perform normal queries as if you were at that earlier time, and then return to the present.
You must have the EXECUTE privilege on the DBMS_FLASHBACK package.
Grant EXECUTE on DBMS_FLASHBACK to scott;
To use the DBMS_FLASHBACK package in your PL/SQL code:
- Specify a past time by invoking either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
- 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.
此时无法执行CREATE TABLE AS SELECT或INSERT INTO SELECT，只能用CURSOR来保存旧数据
- Return to the present by invoking DBMS_FLASHBACK.DISABLE.
Note1: You must invoke DISABLE procedure before invoking DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER again. You cannot nest enable/ disable pairs.
Note2: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 returns the current SCN regardless of previous invocations of DBMS_FLASHBACK.ENABLE.
Example:
The following example illustrates how Flashback can be used when the deletion of a senior employee triggers the deletion of all the personnel reporting to him. Using the Flashback feature, you can recover and re-insert the missing employees.
DROP TABLE employee;
DROP TABLE keep_scn;
REM -- Keep_scn is a temporary table to store scns that we are interested in
CREATE TABLE keep_scn (scn number);
SET ECHO ON
CREATE TABLE employee (
employee_no number(5) PRIMARY KEY,
employee_name varchar2(20),
employee_mgr number(5)
CONSTRAINT mgr_fkey REFERENCES EMPLOYEE ON DELETE CASCADE,
salary number,
hiredate date );
REM -- Populate the company with employees
INSERT INTO employee VALUES (1, 'John Doe', null, 1000000, '5-jul-81');
INSERT INTO employee VALUES (10, 'Joe Johnson', 1, 500000, '12-aug-84');
INSERT INTO employee VALUES (20, 'Susie Tiger', 10, 250000, '13-dec-90');
INSERT INTO employee VALUES (100, 'Scott Tiger', 20, 200000, '3-feb-86');
INSERT INTO employee VALUES (200, 'Charles Smith', 100, 150000, '22-mar-88');
INSERT INTO employee VALUES (210, 'Jane Johnson', 100, 100000, '11-apr-87');
INSERT INTO employee VALUES (220, 'Nancy Doe', 100, 100000, '18-sep-93');
INSERT INTO employee VALUES (300, 'Gary Smith', 210, 75000, '4-nov-96');
INSERT INTO employee VALUES (310, 'Bob Smith', 210, 65000, '3-may-95');
COMMIT;
REM -- Show the entire org
SELECT lpad(' ', 2*(level-1)) || employee_name Name
FROM employee
CONNECT BY PRIOR employee_no = employee_mgr
START WITH employee_no = 1
ORDER BY LEVEL;
REM -- Sleep for a short time (approximately 10 to 20 seconds) to avoid
REM -- querying close to table creation
EXECUTE DBMS_LOCK.SLEEP(10);
REM -- Store this snapshot for later access through Flashback
DECLARE
I NUMBER;
BEGIN
I := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
INSERT INTO keep_scn VALUES (I);
COMMIT;
END;
/
REM -- Scott decides to retire but the transaction is done incorrectly
DELETE FROM EMPLOYEE WHERE employee_name = 'Scott Tiger';
COMMIT;
REM -- notice that all of scott's employees are gone
SELECT lpad(' ', 2*(level-1)) || employee_name Name
FROM EMPLOYEE
CONNECT BY PRIOR employee_no = employee_mgr
START WITH employee_no = 1
ORDER BY LEVEL;
REM -- Flashback to see Scott's organization
DECLARE
restore_scn number;
BEGIN
SELECT scn INTO restore_scn FROM keep_scn;
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (restore_scn);
END;
/
REM -- Show Scott's org.
SELECT lpad(' ', 2*(level-1)) || employee_name Name
FROM employee
CONNECT BY PRIOR employee_no = employee_mgr
START WITH employee_no =
(SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger')
ORDER BY LEVEL;
REM -- Restore scott's organization.
DECLARE
scotts_emp NUMBER;
scotts_mgr NUMBER;
CURSOR c1 IS
SELECT employee_no, employee_name, employee_mgr, salary, hiredate
FROM employee
CONNECT BY PRIOR employee_no = employee_mgr
START WITH employee_no =
(SELECT employee_no FROM employee WHERE employee_name = 'Scott Tiger');
c1_rec c1 % ROWTYPE;
BEGIN
SELECT employee_no, employee_mgr INTO scotts_emp, scotts_mgr FROM employee
WHERE employee_name = 'Scott Tiger';
/* Open c1 in flashback mode */
OPEN c1;
/* Disable Flashback */
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;
/*
Note that all the DML operations inside the loop are performed
with Flashback disabled
*/
IF (c1_rec.employee_mgr = scotts_emp) then
INSERT INTO employee VALUES (c1_rec.employee_no,
c1_rec.employee_name,
scotts_mgr,
c1_rec.salary,
c1_rec.hiredate);
ELSE
IF (c1_rec.employee_no != scotts_emp) THEN
INSERT INTO employee VALUES (c1_rec.employee_no,
c1_rec.employee_name,
c1_rec.employee_mgr,
c1_rec.salary,
c1_rec.hiredate);
END IF;
END IF;
END LOOP;
END;
/
REM -- Show the restored organization.
select lpad(' ', 2*(level-1)) || employee_name Name
FROM employee
CONNECT BY PRIOR employee_no = employee_mgr
START WITH employee_no = 1
ORDER BY LEVEL;
- FLASHBACK TRANSACTION USING DBMS_FLASHBACK
DBMS_FLASHBACK.TRANSACTION_BACKOUT与使用FLASHBACK_TRANSACTION_QUERY相比优点在于可以把闪回事务依赖的事务一起闪回. 此功能是11g出的特性
The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and its dependent transactions while the database remains online. This recovery operation uses undo data to create and run the compensating transactions that return the affected data to its original state.
The transactions being rolled back are subject to these restrictions:
- They cannot have performed DDL operations that changed the logical structure of database tables.
- They cannot use Large Object (LOB) Data Types:
BFILE,BLOB,CLOB,NCLOB
- They cannot use features that LogMiner does not support.
The features that LogMiner supports depends on the value of the COMPATIBLE initialization parameter for the database that is rolling back the transaction. The default value is the release number of the most recent major release.
Flashback Transaction inherits SQL data type support from LogMiner. Therefore, if LogMiner fails due to an unsupported SQL data type in a the transaction, Flashback Transaction fails too.
Some data types, though supported by LogMiner, do not generate undo information as part of operations that modify columns of such types. Therefore, Flashback Transaction does not support tables containing these data types. These include tables with BLOB, CLOB and XML type.
In the context of Flashback Transaction, transaction 2 can depend on transaction 1 in any of these ways: 以下情况事务2会依赖于事务1
- Write-after-write dependency
Transaction 1 changes a row of a table, and later transaction 2 changes the same row.
- Primary key dependency
A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c.
- Foreign key dependency
In table b, column b1 has a foreign key constraint on column a1 of table a. Transaction 1 changes a value in a1, and later transaction 2 changes a value in b1.
2.2 TRANSACTION_BACKOUT Procedures
DBMS_FLASHBACK.TRANSACTION_BACKOUT
numtxns NUMBER,
xids XID_ARRAY,
options NUMBER default NOCASCADE,
timeHint TIMESTAMP default MINTIME);
DBMS_FLASHBACK.TRANSACTION_BACKOUT
numtxns NUMBER,
xids XID_ARRAY,
options NUMBER default NOCASCADE,
scnHint TIMESTAMP default 0 );
DBMS_FLASHBACK.TRANSACTION_BACKOUT
numtxns NUMBER,
txnnames TXNAME_ARRAY,
options NUMBER default NOCASCADE,
timehint TIMESTAMP MINTIME );
DBMS_FLASHBACK.TRANSACTION_BACKOUT
numtxns NUMBER,
txnNames TXNAME_ARRAY,
options NUMBER default NOCASCADE,
scnHint NUMBER 0);
TXNAME_ARRAY: Creates a VARRAY for holding Transaction Names or Identifiers (XIDs)
Table 68-6 TRANSACTION_BACKOUT Procedure Parameters
| Parameter | Description |
| numtxns | Number of transactions passed as input |
| xids | List of transaction IDs in the form of an array |
| txnnames | List of transaction names in the form of an array |
| options | Back out dependent transactions:
4) CASCADE - This completely removes the given transactions including their dependents in a post order fashion (reverse order of commit times). |
| timehint | Time hint on the start of the transaction |
| scnhint | SCN hint on the start of the transaction |
TRANSACTION_BACKOUT会分析依赖事务，执行闪回并生成报告，但闪回操作并不提交，如果你确认闪回操作要手动COMMIT
TRANSACTION_BACKOUT analyzes the transactional dependencies, performs DML operations, and generates reports. TRANSACTION_BACKOUT does not commit the DML operations that it performs as part of transaction backout, but it holds all the required locks on rows and tables in the right form, preventing other dependencies from entering the system. To make the transaction backout permanent, you must explicitly commit the transaction.
Note: If transaction name is used, a time hint must be provided. The time hint should be a time before the start of all the given transactions to back out.
Note: If the SCN hint is provided, it must be before the start of the earliest transaction in the specified input set, or this raises an error and terminates. If it is not provided and the transaction has committed within undo retention, the database system is able to determine the start time.
2.3 TRANSACTION_BACKOUT Reports
To see the reports that TRANSACTION_BACKOUT generates, query the static data dictionary views DBA_FLASHBACK_TXN_STATE and DBA_FLASHBACK_TXN_REPORT.
- DBA_FLASHBACK_TXN_STATE
The static data dictionary view DBA_FLASHBACK_TXN_STATE shows whether a transaction is active or backed out. If a transaction appears in this view, it is backed out.
DBA_FLASHBACK_TXN_STATE is maintained atomically for compensating transactions. If a compensating transaction is backed out, all changes that it made are also backed out, and DBA_FLASHBACK_TXN_STATE reflects this.
For example, if compensating transaction ct backs out transactions t1 and t2, then t1 and t2 appear in DBA_FLASHBACK_TXN_STATE. If ct itself is later backed out, the effects of t1 and t2 are reinstated, and t1 and t2 disappear from DBA_FLASHBACK_TXN_STATE.
COMPENSATING_XID 补偿事务的事务id
XID 补偿的是哪个事务
DEPENDENT_XID 依赖的事务
BACKOUT_MODE NOCASCADE|NOCASCADE_FORCE|NONCONFLICT_ONLY|CASCADE
USERNAME 进行补偿操作的用户
The static data dictionary view DBA_FLASHBACK_TXN_REPORT provides a detailed report for each backed-out transaction.
COMPENSATING_TXN_NAME
COMMIT_TIME 补偿事务提交时间
XID_REPORT An XML report describing the details of the transactions backed out by the compensating transaction
USERNAME
2.4 Example
create table flash_test2 (empno varchar2(20),salary number);
insert into flash_test2 values('1',10000);
insert into flash_test2 values('2',13000);
insert into flash_test2 values('3',8000);
insert into flash_test2 values('4',5000);
commit;
transaction1：
update flash_test2 set salary=salary*3;
Commit;
transaction2：
update flash_test2 set salary=salary*1.1 where empno='1';
Commit;
事务1为闪回事务，事务2为依赖事务，查询事务号：
select distinct xid,commit_scn
from flashback_transaction_query
where table_owner='SYS' and
table_name='FLASH_TEST2' and
commit_timestamp > systimestamp - interval '30' minute
order by commit_scn;
执行闪回事务
declare
xids sys.xid_array;
begin
xids := sys.xid_array('05001200412C0000');
dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
end;
/
SQL> select * from DBA_FLASHBACK_TXN_STATE;
SQL> select * from DBA_FLASHBACK_TXN_REPORT;
SQL> commit;
（七）Using Flashback Data Archive
UNDO DATA会被循环使用，所以依赖undo的闪回技术闪回的时间有限制。闪回归档将UNDO DATA另外存到Flashback Data Archives(由一个或多个表空间组成)从而让闪回不再受UNDO保留时间限制，之前闪回的限制并未改变只是可以闪回的时间更早。相关权限：
FLASHBACK ARCHIVE ADMINISTER //授予用户创建，修改或删除闪回回档
FLASHBACK ARCHIVE //授予用户对表进行归档。
By default, Flashback Data Archive is not enabled for any tables. You can enable Flashback Data Archive for a table if all of these conditions are true:
- You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.
- You cannot enable Flashback Data Archive on a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, or non-table object.
- The table contains neither LONG nor nested columns.
- The table does not use any of these Flashback Data Archive reserved words as column names:
STARTSCN,ENDSCN,RID,XID,OP,OPERATION
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1 QUOTA 10G RETENTION 1 YEAR;
CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETENTION 2 YEAR;
Note: 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.
With the ALTER FLASHBACK ARCHIVE statement, you can:
- Change the retention time of a Flashback Data Archive
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
- Purge some or all of its data
ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;
- Add, modify, and remove tablespaces
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs3 QUOTA 20G; --更改QUOTA
ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1; --清除QUOTA限制
ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;
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.
ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;
- Dropping a Flashback Data Archive
Dropping a Flashback Data Archive deletes its historical data, but does not drop its tablespaces.
DROP FLASHBACK ARCHIVE fla1;
By default, Flashback Data Archive is disabled for any table. To enable Flashback Data Archive for a table, include the FLASHBACK ARCHIVE in either the CREATE TABLE or ALTER TABLE statement.
已指定Flashback Data Archive的表不能再指定
If a table has Flashback Data Archive enabled, and you try to enable it again with a different Flashback Data Archive, an error occurs. After Flashback Data Archive 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 Data Archive 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.)
After enabling Flashback Data Archive on a table, Oracle recommends waiting at least 20 seconds before inserting data into the table, and waiting up to 5 minutes before using Flashback Query on the table.
Examples
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE; ---使用默认
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;
ALTER TABLE employee FLASHBACK ARCHIVE; ---使用默认
ALTER TABLE employee FLASHBACK ARCHIVE fla1;
ALTER TABLE employee NO FLASHBACK ARCHIVE;
- DDL Statements on Tables Enabled for Flashback Data Archive
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
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. Also, to drop a table enabled for Flashback Data Archive, you must first disable Flashback Data Archive on the table by using the ALTER TABLE ... NO FLASHBACK ARCHIVE clause.
Table 19-3 lists and briefly describes the static data dictionary views that you can query for information about 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. |
（八）Performance Guidelines for Oracle Flashback Technology
1) Use the DBMS_STATS package to generate statistics for all tables involved in an Oracle Flashback Query. Keep the statistics current. Oracle Flashback Query uses the cost-based optimizer, which relies on these statistics.
2) 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 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 operations are CPU-bound.
Oracle recommends that you have enough buffer cache, so that the versions query for the archiver finds the undo data in the buffer cache. Buffer cache access is significantly faster than disk access.
3) If very large transactions (such as affecting more than 1 million rows) are performed on tracked tables, set the large pool size high enough (at least 1 GB) for Parallel Query not to have to allocate new chunks out of the SGA.
4) For Oracle Flashback Version Query, use index structures. Oracle Database keeps undo data for index changes and data changes. Performance of index lookup-based Oracle Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.
5) An Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.
（九）Multitenant Container Database Restrictions for Oracle Flashback Technology
These Oracle Flashback Technology features are unavailable for a multitenant container database (CDB):
For Oracle Database 12c Release 1 (12.1.0.1), Flashback Data Archive (FDA) is not supported in a CDB.For Oracle Database 12c Release 1 (12.1.0.2), this restriction is removed.
Flashback Transaction Query is not supported in a CDB.
Flashback Transaction Backout is not supported in a CDB.
扫一扫
873
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
