oracle11g flashback技術

http://blog.csdn.net/tianlesoftware/article/details/72298http://blog.csdn.net/tianlesoftware/article/details/7229802http://blog.csdn.net/tianlesoftware/article/details/7229802

之前整理了一個Flashback 技術的總結，但是和官網的資料對比，還是有很多的遺漏，所以重新學習一些官網的說明，之前的連結如下：

OracleFlashback 技術 總結

http://blog.csdn.net/tianlesoftware/article/details/4677378

Oracle 11gR2中Flashback的連結如下：

http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

(1)    Overviewof Oracle Flashback Technology

(2)    ConfiguringYour Database for Oracle Flashback Technology

(3)    UsingOracle Flashback Query (SELECT AS OF)

(4)    UsingOracle Flashback Version Query

(5)    UsingOracle Flashback Transaction Query

(6)    UsingOracle Flashback Transaction Query with Oracle Flashback Version Query

(7)    UsingORA_ROWSCN

(8)    UsingDBMS_FLASHBACK Package

(9)    UsingFlashback Transaction

(10) UsingFlashback Data Archive (Oracle Total Recall)

(11) GeneralGuidelines for Oracle Flashback Technology

(12) PerformanceGuidelines for Oracle Flashback Technology

一. Overview of Oracle Flashback Technology

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

--Flashback 技術是一組Oracle 的特性，可以讓我們將資料庫物件恢復到之前的狀態而不需要使用基於時間的介質恢復。

With flashback features, you can:

（1）Perform queries that return past data

（2）Perform queries that return metadata that shows a detailed historyof changes to the database

（3）Recover tables or rows to a previous point in time

（4）Automatically track and archive transactional data changes

（5）Roll back a transaction and its dependent transactions while thedatabase remains online

Oracle Flashbackfeatures 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 individualtransactions. For example, if a user runs an UPDATE statement tochange a salary from 1000 to 1100, then Oracle Database stores the value 1000in the undo data.

--Flashback 特性使用AUM系統來獲取事務的metadata 和 歷史資料。他們依賴與undo data。

Undo data ispersistent and survives a database shutdown. By using flashback features, youcan use undo data to query past data or recover from logical damage. Besidesusing it in flashback features, Oracle Database uses undo data to perform theseactions:

(1)    Roll back active transactions

(2)    Recover terminated transactionsby using database or process recovery

(3)    Provide read consistency forSQL queries

1.1 Application Development Features

In application development, you can use these flashback features toreport historical data or undo erroneous changes. (You can also use thesefeatures interactively as a database user or administrator.)

--在應用開發部門，可以使用flashback 特性來查詢歷史資料或者還原錯誤的修改。

1.1.1 Oracle FlashbackQuery

Use this featureto retrieve data for an earlier time that you specify with the AS OF clauseof the SELECT statement.

--使用這個特性可以在select 語句中使用as of 子句來查詢之前某個時間的資料。

1.1.2 Oracle FlashbackVersion Query

Use this featureto retrieve metadata and historical data for a specific time interval (forexample, to view all the rows of a table that ever existed during a given timeinterval). Metadata for each row version includes start and end time, type ofchange operation, and identity of the transaction that created the row version.To create an Oracle Flashback Version Query, use the VERSIONS BETWEEN clauseof the SELECTstatement.

--使用Flashback Version Query 特性可以來查詢指定時間內的metadata 和 historical data。每行記錄version的metadata包括開始時間和結束時間，操作型別，使用時，在SQL 語句中指定versions between 子句就可以了。

1.1.3 Oracle FlashbackTransaction Query

Use this featureto retrieve metadata and historical data for a given transaction or for alltransactions in a given time interval. To perform an Oracle FlashbackTransaction Query, select from the static data dictionary view FLASHBACK_TRANSACTION_QUERY.

--使用該特性可以來查詢指定事務或者提供的時間內所有事務對應的metadata 和 historical data。

Typically, youuse Oracle Flashback Transaction Query with an Oracle Flashback Version Querythat provides the transaction IDs for the rows of interest.

--可以結合Flashback transaction Query 和Flashback version Query 使用。

1.1.4 DBMS_FLASHBACKPackage

Use this featureto set the internal Oracle Database clock to an earlier time so that you canexamine data that was current at that time, or to roll back a transaction andits dependent transactions while the database remains online (see FlashbackTransaction).

1.1.5 FlashbackTransaction

Use FlashbackTransaction to roll back a transaction and its dependent transactions while thedatabase remains online. This recovery operation uses undo data to create andrun the corresponding compensating transactions that return the affected datato its original state. (Flashback Transaction is part of DBMS_FLASHBACK package.)

1.1.6 Flashback DataArchive (Oracle Total Recall)

Use FlashbackData Archive to automatically track and archive both regular queries and OracleFlashback Query, ensuring SQL-level access to the versions of database objectswithout getting a snapshot-too-old error. For more information, see "UsingFlashback Data Archive (Oracle Total Recall)".

1.2 DatabaseAdministration Features

These flashback features are primarily for data recovery. Typically,you use these features only as a database administrator.

1.2.1 Oracle Flashback Table

Use this featureto restore a table to its state at a previous point in time. You can restore atable while the database is on line, undoing changes to only the specifiedtable.

--使用該特性可以將表恢復到之前的某個時間點。

1.2.2 Oracle Flashback Drop

Use this featureto recover a dropped table. This feature reverses the effects of a DROP TABLE statement.

--使用該特性可以恢復被drop 的表。

1.2.3 Oracle Flashback Database

Use this featureto quickly return the database to an earlier point in time, by undoing all ofthe changes that have taken place since then. This is fast, because you do nothave to restore database backups.

--該特性可以快速的將整個資料庫返回到之前的某個點。

二.ConfiguringYour Database for Oracle Flashback Technology

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

--在應用中使用flashback 特性之前，必須先執行如下的配置：

(1)    ConfiguringYour Database for Automatic Undo Management

(2)    ConfiguringYour Database for Oracle Flashback Transaction Query

(3)    ConfiguringYour Database for Flashback Transaction

(4)    EnablingOracle Flashback Operations on Specific LOB Columns

(5)    GrantingNecessary Privileges

2.1 Configuring Your Database for Automatic Undo Management

To configureyour database for Automatic Undo Management (AUM), you or your databaseadministrator must:

--配置資料庫使用AUM：

（1）Create anundo tablespace with enough space to keep the required data for flashbackoperations.

The more oftenusers update the data, the more space is required. The database administratorusually calculates the space requirement.

--建立一個合適的undo 表空間，如果update 操作頻繁，那麼就需要更多的空間。

（2）Enable AUM,as explained in . Set these database initializationparameters:

--啟用AUM，相關的引數如下：

1）UNDO_MANAGEMENT

2）UNDO_TABLESPACE

3）UNDO_RETENTION

      

For a fixed-sizeundo tablespace, Oracle Database automatically tunes the system to give theundo tablespace the best possible undo retention.

For anautomatically extensible undo tablespace, Oracle Database retains undo datalonger than the longest query duration and the low threshold of undo retentionspecified by the UNDO_RETENTION parameter.

Note:

You can query V$UNDOSTAT.TUNED_UNDORETENTION todetermine the amount of time for which undo is retained for the current undotablespace.

--可以透過V$UNDOSTAT.TUNED_UNDORETENTION 來檢視事務對應的undo存在當前undo 表空間的時間。

Setting UNDO_RETENTION doesnot guarantee that unexpired undo data is not discarded. If the system needsmore space, Oracle Database can overwrite unexpired undo with more recentlygenerated undo data.

（3）Specify the RETENTION GUARANTEE clause for the undo tablespaceto ensure that unexpired undo data is not discarded.

--設定RETENTION GUARANTEE 屬性來確保沒有過期的undo 資料不被覆蓋。

有關Undo 表空間管理的更多內容參考我的Blog：

Oracle undo 表空間管理

http://blog.csdn.net/tianlesoftware/article/details/5689558

2.2 ConfiguringYour Database for Oracle Flashback Transaction Query

To configureyour database for the Oracle Flashback Transaction Query feature, you or yourdatabase administrator must:

--如果要使用Flashback Transaction Query 特性，比如按如下設定：

（1）Ensure thatOracle Database is running with version 10.0 compatibility.

（2）Enablesupplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2.3 ConfiguringYour Database for Flashback Transaction

To configureyour database for the Flashback Transaction feature, you or your databaseadministrator must:

--如果使用Flashback Transaction 特性，需要設定如下：

（1）With the database mounted but notopen, enable ARCHIVELOG:

ALTER DATABASEARCHIVELOG;

（2）Open at least one archive log:

ALTER SYSTEMARCHIVE LOG CURRENT;

（3）If not done, enable minimal andprimary key supplemental logging:

ALTER DATABASEADD SUPPLEMENTAL LOG DATA;

ALTER DATABASEADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

（4）If you want to track foreign keydependencies, enable foreign key supplemental logging:

ALTER DATABASEADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Note:

If you have verymany foreign key constraints, enabling foreign key supplemental logging mightnot be worth the performance penalty.

2.4 EnablingOracle Flashback Operations on Specific LOB Columns

To enableflashback operations on specific LOB columns of a table, use the ALTER TABLE statementwith the RETENTION option.

Because undodata for LOB columns can be voluminous, you must define which LOB columns touse with flashback operations.

--如果要對LOB 欄位使用flashback 操作，那麼需要設定RETENTION 屬性，

       Retention是表示採用基於時間版本保留策略。簡單的說，就是儘量保證保留一個時間段內的資料lob版本不會清除掉，即多長時間內來保證一致讀。在資料庫版本的相容性版本設定在9.2.0.0以上，並且undo_management引數值為true時，lob是預設直接使用retetion設定的。

       需要注意，不能使用這個子句來指定保留時間；而要從資料庫的UNDO_RETENTION設定來繼承它。

       這個子句與PCTVERSION 子句是互斥的，即RETENTION和 PCTVERSION 只能設定一個，不能兩個都設定。

關於LOB的更多內容參考：

Oracle LOB 詳解

http://blog.csdn.net/tianlesoftware/article/details/6905406

2.5 Granting Necessary Privileges

You or your database administrator must grant privileges to users,roles, or applications that must use these flashback features.

       --與Flashback相關的許可權說明

2.5.1 For OracleFlashback Query and Oracle Flashback Version Query

To allow accessto specific objects during queries, grant FLASHBACK and SELECT privilegeson those objects.

To allow querieson all tables, grant the FLASHBACK ANY TABLE privilege.

2.5.2 For OracleFlashback Transaction Query

Grant the SELECT ANY TRANSACTION privilege.

To allowexecution of undo SQL code retrieved by an Oracle Flashback Transaction Query,grant SELECT, UPDATE, DELETE, and INSERT privilegesfor specific tables.

2.5.3 For DBMS_FLASHBACK Package

To allow accessto the features in the DBMS_FLASHBACK package, grant the EXECUTE privilegeon DBMS_FLASHBACK.

2.5.4 For Flashback DataArchive (Oracle Total Recall)

To allow aspecific user to enable Flashback Data Archive on tables, using a specificFlashback Data Archive, grant the FLASHBACK ARCHIVE objectprivilege on that Flashback Data Archive to that user. To grant the FLASHBACK ARCHIVE objectprivilege, you must either be logged on as SYSDBA or have FLASHBACK ARCHIVE ADMINISTER systemprivilege.

To allowexecution of these statements, grant the FLASHBACK ARCHIVE ADMINISTER systemprivilege:

--為了執行，需要如下許可權：

(1)    CREATE FLASHBACK ARCHIVE

(2)    ALTER FLASHBACK ARCHIVE

(3)    DROP FLASHBACK ARCHIVE

To grant the FLASHBACK ARCHIVE ADMINISTER systemprivilege, you must be logged on as SYSDBA.

To create adefault Flashback Data Archive, using either the CREATE FLASHBACK ARCHIVE or ALTER FLASHBACK ARCHIVE statement,you must be logged on as SYSDBA.

To disableFlashback Data Archive for a table that has been enabled for Flashback DataArchive, you must either be logged on as SYSDBA or have the FLASHBACK ARCHIVE ADMINISTER systemprivilege.

三.UsingOracle Flashback Query (SELECT AS OF)

To use Oracle Flashback Query, use a SELECT statementwith an AS OF clause. Oracle Flashback Query retrieves data asit existed at an earlier time. The query explicitly references a past timethrough a time stamp or System Change Number (SCN). It returns committed datathat was current at that point in time.

--Flashback Query 使用select 語句加as of子句。這個查詢可以根據time stamp 或者SCN 來精確的查詢。

Uses of Oracle Flashback Query include:

（1）    Recovering lost data or undoing incorrect, committed changes.

--恢復丟失的資料，或者撤銷incorrect，commit的資料。

For example, ifyou mistakenly delete or update rows, and then commit them, you can immediatelyundo the mistake.

（2）    Comparing current data with the corresponding data at an earliertime.

--比較當前的資料和之前的資料。

For example, youcan run a daily report that shows the change in data from yesterday. You cancompare individual rows of table data or find intersections or unions of setsof rows.

（3）    Checking the state of transactional data at a particular time.

--檢查指定時間內事務資料的狀態

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

（4）    Simplifying application design by removing the need to store somekinds of temporal data.

Oracle FlashbackQuery lets you retrieve past data directly from the database.

（5）Applying packaged applications,such as report generation tools, to past data.

（6）Providing self-service errorcorrection for an application, thereby enabling users to undo and correct theirerrors.

3.1 Example of Examining and Restoring Past Data

Suppose that youdiscover 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 thedatabase. You can use Oracle Flashback Query to examine the contents of thetable at 9:30 AM to find out what data was lost. If appropriate, you canrestore the lost data.

（1）    Example12-1 retrieves the state of the record for Chung at 9:30AM,April 4, 2004:

--檢視之前的資料

Example 12-1 Retrieving a Lost Row withOracle 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';

（2）    Example12-2 restores Chung's information to the employees table:

--還原之前的資料，重新insert 到表裡。

Example12-2 Restoring a Lost Row After Oracle Flashback Query

INSERT INTO employees (

SELECT * FROM employees

  ASOF TIMESTAMP

TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

WHERE last_name = 'Chung'

);

3.2 Guidelines forOracle Flashback Query

--Flashback Query指南

（1）    You can specify or omit the AS OF clause for eachtable and specify different times for different tables.

Note:

If a table is aFlashback Data Archive and you specify a time for it that is earlier than itscreation time, the query returns zero rows for that table, rather than causingan error.

（2）You can usethe AS OF clause in queries to perform data definition language(DDL) operations (such as creating and truncating tables) or data manipulationlanguage (DML) statements (such as INSERT andDELETE) in the samesession as Oracle Flashback Query.

（3）To use theresult of Oracle Flashback Query in a DDL or DML statement that affects thecurrent state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.

（4）If a possible3-second error (maximum) is important to Oracle Flashback Query in yourapplication, use an SCN instead of a time stamp.

（5）You cancreate a view that refers to past data by using the AS OF clausein the SELECT statement that defines the view.

If you specify arelative time by subtracting from the current time on the database host, thepast 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 zoneof the database host environment.

（6）You can use the AS OF clausein self-joins, or in set operations such as INTERSECT and MINUS,to extract or compare data from two different times.

You can storethe results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement.For example, this query reinserts into table employees the rows thatexisted an hour ago:

INSERT INTO employees

   (SELECT * FROM employees

     AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL'60' MINUTE)

    )

   MINUS SELECT * FROM employees);

SYSTIMESTAMP refers to the time zoneof the database host environment.

四.Using Oracle Flashback Version Query

Use Oracle Flashback Version Query to retrieve the differentversions of specific rows that existed during a given time interval. A rowversion is created whenever a COMMIT statement is executed.

--使用該特性可以檢視指定時間，特定行的不同version，該version 在使用者commit時建立。

Specify OracleFlashback Version Query using the VERSIONS BETWEEN clauseof the SELECT statement. The syntax is:

--指定Flashback Version Query 使用Versionbetween 子句，語法如下：

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

where start and end areexpressions representing the start and end, respectively, of the time intervalto be queried. The time interval includes (start and end).

Oracle FlashbackVersion Query returns a table with a row for each version of the row thatexisted at any time during the specified time interval. Each row in the tableincludes pseudocolumns of metadata about the row version, described in Table12-1. This information can reveal when and how a particular change (perhapserroneous) occurred to your database.

--Flashback Version Query 返回指定時間間隔內的每個version。 每個記錄包含一些描述version 的虛擬列，具體的描述參考下表：

Table 12-1 Oracle Flashback Version Query Row Data Pseudocolumns

Pseudocolumn Name	Description
VERSIONS_STARTSCN VERSIONS_STARTTIME	Starting System  Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the  time when the data first had the values reflected in the row version. Use  this pseudocolumn 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_ENDSCN VERSIONS_ENDTIME	SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then  either the row version was current at the time of the query or the row  corresponds to a DELETE operation.
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.

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

--注意這裡的有效時間範圍是>= version_start,

VERSIONS_START_TIME     VERSIONS_END_TIME     SALARY

-------------------     -----------------     ------

09-SEP-2003             25-NOV-2003           10243

Here is a typical use of Oracle FlashbackVersion Query:

--一個典型的FlashbackVersion 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')

  ANDTO_TIMESTAMP('2008-12-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')

WHERE first_name = 'John';

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

五. Using Oracle Flashback Transaction Query

Use OracleFlashback Transaction Query to retrieve metadata and historical data for agiven transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static datadictionary view FLASHBACK_TRANSACTION_QUERY, whosecolumns are described in .

--Flashback Transaction Query 用來接收在提供的時間內一個事務或者所有事務metadata和 歷史資料。 Flashback Transaction 查詢靜態的資料字典檢視：FLASHBACK_TRANSACTION_QUERY。

The column UNDO_SQL showsthe SQL code that is the is the logical opposite of the DML operation performedby the transaction. You can usually use this code to reverse the logical stepstaken during the transaction. However, there are cases where the SQL_UNDO codeis not the exact opposite of the original transaction. For example, a SQL_UNDO INSERT operationmight not insert a row back in a table at the same ROWID from whichit was deleted.

--UNDO_SQL 列顯示的就是事務操作相反的SQL。 我們可以使用這個SQL 來還原之前的事務操作。 在一些案例下，SQL_UNDO裡的內容不是完全正確的原始資料。

This statementqueries the FLASHBACK_TRANSACTION_QUERY view for transactioninformation, including the transaction ID, the operation, the operation startand end SCNs, the user responsible for the operation, and the SQL code thatshows the logical opposite of the operation:

--以下的SQL 語句從FLASHBACK_TRANSACTION_QUERY 檢視中查詢事務的資訊：

SELECT xid,operation, start_scn, commit_scn, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW('000200030000002D');

This statementuses Oracle Flashback Version Query as a subquery to associate each row versionwith the LOGON_USER responsible for the row data change:

--如下SQL 使用Flashback Version Query 作為一個子查詢，然後查詢指定時間內的事務資訊：

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

);

Note:

If you query FLASHBACK_TRANSACTION_QUERY withoutspecifying XID in the WHERE clause, the query scans manyunrelated rows, degrading performance.

六．Using Oracle Flashback Transaction Query with Oracle Flashback Version Query

--結合FlashbackTransaction Query 和 Flashback Version Query示例：

In this example, a database administratordoes 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;

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

--操作1：

UPDATE emp SET salary = salary + 100 WHEREempno = 111;

INSERT INTO dept (deptno, deptname) VALUES(20, 'Finance');

DELETE FROM emp WHERE empno = 111;

COMMIT;

Next, atransaction reinserts empno 111 into the emp tablewith a new employee name:

--操作2：

INSERT INTO emp (empno, empname, salary)VALUES (111, 'Tom', 777);

UPDATE emp SET salary = salary + 100 WHEREempno = 111;

UPDATE emp SET salary = salary + 50 WHEREempno = 111;

COMMIT;

The databaseadministrator detects the application error and must diagnose the problem. Thedatabase administrator issues this query to retrieve versions of the rows inthe emp table that correspond to empno111. The query uses OracleFlashback Version Query pseudocolumns:

--發現問題，查詢問題：使用Flashback Version Query:

SELECT versions_xid XID, versions_startscnSTART_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 resultstable rows are in descending chronological order. The third row corresponds tothe version of the row in the table emp that was inserted in thetable when the table was created. The second row corresponds to the row in emp thatthe erroneous transaction deleted. The first row corresponds to the version ofthe row in emp that was reinserted with a new employee name.

The databaseadministrator identifies transaction 030002002B210000 as theerroneous transaction and uses Oracle Flashback Transaction Query to audit allchanges made by this transaction:

--使用Flashback Transaction Query 來解決問題：

SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW('000200030000002D');

Results are similar to:

XID               START_SCN COMMIT_SCN OPERATIONLOGON_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 theresult of the next query easier to read, the database administrator uses theseSQL*Plus commands:

--為了結果更可讀，對SQL*Plus 做如下設定：

COLUMN operation FORMAT A9

COLUMN table_name FORMAT A10

COLUMN table_owner FORMAT A11

To see thedetails of the erroneous transaction and all subsequent transactions, thedatabase 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 OPERATIONTABLE_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

8 rows selected.

Note:

Because thepreceding query does not specify the XID in the WHERE clause,it scans many unrelated rows, degrading performance.

七. Using ORA_ROWSCN

ORA_ROWSCN is a pseudocolumn of any table that is not fixed orexternal. It represents the SCN of the most recent change to a given row in thecurrent session; that is, the most recent COMMIT operation for therow in the current session. For example:

--ORA_ROWSCN 是表的一個虛擬列，從Oracle 10g引入，其反應的是最後一次commit時的SCN.

之前整理的一篇相關文章：

OracleORA_ROWSCN 偽列 說明

http://blog.csdn.net/tianlesoftware/article/details/6658529

SELECT ora_rowscn, last_name, salary

FROM employees

WHERE employee_id = 200;

Result is similar to:

ORA_ROWSCN LAST_NAME                     SALARY

---------- -----------------------------------

   884320 Whalen                         2800

The most recent COMMIT operationfor the row in the current session took place at approximately SCN 9371092. Toconvert an SCN to the corresponding TIMESTAMP value, use the function SCN_TO_TIMESTAMP(documentedin ).

--可以透過SCN_TO_TIMESTAMP 將SCN 轉換成TIMESTAMP.

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

Note:

ORA_ROWSCN isnot supported for Flashback Query. Instead, use the version querypseudocolumns, which are provided explicitly for Flashback Query. Forinformation about these pseudocolumns, see .

--注意ORA_ROWSCN 不能在flashback Query中使用，但是可以在flashback version 中使用。

7.1 Scenario:Package Subprogram Might Change Row

Your applicationexamines a row of data and records the corresponding ORA_ROWSCN as202553. Then, your application invokes a package subprogram, whoseimplementation details you cannot see, which might or might not change the samerow (and commit the change). Later, your application must update the row onlyif the package subprogram did not change it. Make the operationconditional—update the row only ifORA_ROWSCN is still 202553, as in thisequivalent interactive statement:

UPDATE employees

SET salary = salary + 100

WHERE employee_id = 200

AND ora_rowscn = 202553;

If the packagesubprogram changed the row, then ORA_ROWSCN is no longer 9371092, andthe update fails.

Your applicationqueries again to obtain the new row data and ORA_ROWSCN. Suppose that the ORA_ROWSCN isnow 415639. The application tries the conditional update again, using thenew ORA_ROWSCN. This time, the update succeeds, and it is committed. Aninteractive equivalent is:

UPDATE employees

SET salary = salary + 100

WHERE employee_id = 7788

AND ora_rowscn = 415639;

7.2 ORA_ROWSCN andTables with Virtual Private Database (VPD)

When a VPDpolicy is added to a table, it is no longer possible to select the ORA_ROWSCN pseudocolumn.However, because ORA_ROWSCN is available inside the policy function,you can:

--當在表上使用VPD策略後，就不能使用ORA_ROWSCN列，但是可以在該策略函式里面使用，具體方法如下：

（1）Create a function that returns arow SCN, as in Example12-3.

（2）In the policy predicate function,add a predicate that stores the row SCN in the context that the function useswhile processing rows. For example:

||' AND f_ora_rowscn('||object_name||'.ora_rowscn)= 1'

（3）Use the function to fetch the row.For example:

SELECT t.*,get_rowscn(t.rowid) "ORA_ROWSCN" FROM test_table t;

Note:

To run Example12-3, you need CREATE ANY CONTEXT system privilege.

Example 12-3 Function that Can Return Row SCN from Table thathas VPD

-- Create context thatfunction uses while processing rows:

CREATE OR REPLACE FUNCTION f_ora_rowscn

(rowscnIN NUMBER)

RETURN NUMBER

AS

BEGIN

DBMS_SESSION.SET_CONTEXT('STORE_ROWSCN','ROWSCN',rowscn);

RETURN 1;

END;

/

CREATE CONTEXT store_rowscn USINGf_ora_rowscn;

-- Create function thatreturns row SCN for each row:

CREATE OR REPLACE FUNCTION get_rowscn

  (rIN ROWID)

RETURN VARCHAR2

AS

BEGIN

RETURN sys_context('STORE_ROWSCN','ROWSCN');

END;

/

八．UsingDBMS_FLASHBACK Package

The DBMS_FLASHBACK package provides the same functionalityas Oracle Flashback Query, but Oracle Flashback Query is sometimes moreconvenient.

--DBMS_FLASHBACK 包提供了一些功能可以作為flashbackquery 使用，但是直接使用flashback query 更方便。

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

--DBMS_FLASHBACK 包扮演著一個時間機器的作用。

You must have the EXECUTE privilegeon the DBMS_FLASHBACK package.

--必須有對dbms_flashback包的execute許可權。

To use the DBMS_FLASHBACK packagein your PL/SQL code:

--在PL/SQL 程式碼中使用該包的方法：

（1）Specify apast time by invoking either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.

（2）Performregular queries (that is, queries without special flashback-feature syntax suchas AS OF). Do not perform DDL or DML operations.The database isqueried at the specified past time.

（3）Return to thepresent by invoking DBMS_FLASHBACK.DISABLE.

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

To use a cursorto store the results of queries, open the cursor before invoking DBMS_FLASHBACK.DISABLE.After storing the results and invoking DBMS_FLASHBACK.DISABLE, you can:

--使用遊標來儲存query 的結果：

（1）Perform INSERT or UPDATE operationsto modify the current database state by using the stored results from the past.

（2）Comparecurrent data with the past data. After invoking DBMS_FLASHBACK.DISABLE,open a second cursor. Fetch from the first cursor to retrieve past data; fetchfrom the second cursor to retrieve current data. You can store the past data ina temporary table and then use set operators such as MINUS or UNION tocontrast or combine the past and current data.

You can invoke DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER atany time to get the current System Change Number (SCN). DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER alwaysreturns the current SCN regardless of previous invocations of DBMS_FLASHBACK.ENABLE.

--可以在任何時候條用DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER 來檢視當前的SCN值。

九.Using Flashback Transaction

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedurerolls back a transaction and its dependent transactions while the databaseremains online. This recovery operation uses undo data to create and run the compensatingtransactions that return the affected data to its original state.

--dbms_flashback.transaction_backout 過程可以用來回滾一個事務，該恢復操作利用undo 資料來建立和執行一個相反的事務，從而還原的之前的事務。

The transactions being rolled back aresubject to these restrictions:

--這種恢復操作有如下限制：

（1）They cannot have performed DDLoperations that changed the logical structure of database tables.

（2）They cannot use Large Object (LOB)Data Types:

BFILE

BLOB

CLOB

NCLOB

（3）They cannot use features thatLogMiner does not support.

The featuresthat LogMiner supports depends on the value of the COMPATIBLE initializationparameter for the database that is rolling back the transaction. The defaultvalue is the release number of the most recent major release.

Flashback Transaction inherits SQL datatype support from LogMiner. Therefore, if LogMiner fails due to an unsupportedSQL data type in a the transaction, Flashback Transaction fails too.

Some data types,though supported by LogMiner, do not generate undo information as part ofoperations that modify columns of such types. Therefore, Flashback Transactiondoes not support tables containing these data types. These include tables withBLOB, CLOB and XML type.

--一些型別可能LogMiner支援，但是這種型別不生成undo 資訊。 所以FlashbackTransaction 不支援。

9.1 Dependent Transactions

--事務依賴

In the contextof Flashback Transaction, transaction 2 can depend on transaction 1 in any of these ways:

（1）Write-after-writedependency

Transaction 1changes a row of a table, and later transaction 2 changes the same row.

（2）Primary key dependency

A table has aprimary key constraint on column c. In a row of the table, column c has thevalue v. Transaction 1 deletes that row, and later transaction 2 inserts a rowinto the same table, assigning the value v to column c.

（3）Foreign key dependency

In table b,column b1 has a foreign key constraint on column a1 of table a. Transaction 1changes a value in a1, and later transaction 2 changes a value in b1.

9.2 TRANSACTION_BACKOUTParameters

The parameters of the TRANSACTION_BACKOUT procedureare:

--TRANSACTION_BACKOUT過程的相關引數：

（1）Number of transactions to be backedout

（2）List of transactions to be backedout, identified either by name or by XID

（3）Time hint, if you identifytransactions by name Specify a time that is earlier than any transactionstarted.

（4）Backout option from Table12-2

Table 12-2 Flashback TRANSACTION_BACKOUT Options

Option	Description
CASCADE	Backs out  specified transactions and all dependent transactions in a post-order fashion  (that is, children are backed out before parents are backed out). Without CASCADE, if  any dependent transaction is not specified, an error occurs.
NOCASCADE	Default. Backs  out specified transactions, which are expected to have no dependent  transactions. First dependent transactions causes an error and appears in *_FLASHBACK_TRANSACTION_REPORT.
NOCASCADE_FORCE	Backs out  specified transactions, ignoring dependent transactions. Server runs undo SQL  statements for specified transactions in reverse order of commit times. If no constraints break and you are satisfied with the  result, you can commit the changes; otherwise, you can roll them back.
NONCONFLICT_ONLY	Backs out  changes to nonconflicting rows of the specified transactions. Database  remains consistent, but transaction atomicity is lost.

TRANSACTION_BACKOUT analyzesthe transactional dependencies, performs DML operations, and generates reports. TRANSACTION_BACKOUT doesnot 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 transactionbackout permanent, you must explicitly commit the transaction.

-- TRANSACTION_BACKOUT 分析事務的依賴關係，然後執行DML操作，生成報告。注意的是，該過程執行完DML 後不會commit，而是繼續保持了物件的所有鎖，需要DBA 手工的顯示的進行commit。

9.3 TRANSACTION_BACKOUT Reports

To see thereports that TRANSACTION_BACKOUT generates, query the static datadictionary views *_FLASHBACK_TXN_STATE and *_FLASHBACK_TXN_REPORT.

      --可以透過如下2個靜態檢視來檢視TRANSACTION_BACKOUT的報告.

9.3.1 *_FLASHBACK_TXN_STATE

The static datadictionary view *_FLASHBACK_TXN_STATE shows whether a transaction is activeor backed out. If a transaction appears in this view, it is backed out.

*_FLASHBACK_TXN_STATE ismaintained atomically for compensating transactions. If a compensatingtransaction is backed out, all changes that it made are also backed out, and *_FLASHBACK_TXN_STATE reflectsthis. For example, if compensating transaction ct backs outtransactions t1 and t2, then t1 and t2 appearin *_FLASHBACK_TXN_STATE. If ct itself is later backed out, theeffects of t1 and t2 are reinstated, and t1 andt2 disappearfrom *_FLASHBACK_TXN_STATE.

9.3.2 *_FLASHBACK_TXN_REPORT

The static datadictionary view *_FLASHBACK_TXN_REPORT provides a detailed report foreach backed-out transaction.

十.Using Flashback Data Archive (Oracle Total Recall)

A Flashback Data Archive provides theability to track and store transactional changes to a table over its lifetime.A Flashback Data Archive is useful for compliance with record stage policiesand audit reports.

--Flashback Data Archive 提供了跟蹤和儲存事務的改變到一張表裡。

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

       -- FDA 包含一個或者多個表空間，我們可以建立多個FDA。 當以sysdba 登陸時，可以指定defaultFDA。

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

  -- 預設情況下，FDA 是關閉的，當具備一下條件時，我們可以啟用FDA。

（1）You have the FLASHBACK ARCHIVE objectprivilege on the Flashback Data Archive to use for that table.

（2）The table is neither nested,clustered, temporary, remote, or external.

（3）The table contains neither LONG nornested columns.

After flashback archiving is enabled for a table, you can disable it only if you either havethe FLASHBACK ARCHIVE ADMINISTER system privilege or youare logged on as SYSDBA.

--當FDA 啟動以後，只有具有FLASHBACK ARCHIVE ADMINISTER 許可權的使用者或者用SYSDBA登陸的使用者才可以禁用FDA。

When choosing aFlashback Data Archive for a specific table, consider the data retentionrequirements for the table and the retention times of the Flashback DataArchives on which you have the FLASHBACKARCHIVE object privilege.

這部分的詳細內容參考我的Blog：

oracle 11g 新特性Flashback Data Archive 說明

http://blog.csdn.net/tianlesoftware/article/details/6412427

十一.GeneralGuidelines for Oracle Flashback Technology

（1）Use the DBMS_FLASHBACK.ENABLE and DBMS_FLASHBACK.DISABLE proceduresaround SQL code that you do not control, or when you want to use the same pasttime for several consecutive queries.

（2）Use Oracle Flashback Query, OracleFlashback Version Query, or Oracle Flashback Transaction Query for SQL codethat you write, for convenience. An Oracle Flashback Query, for example, isflexible enough to do comparisons and store results in a single query.

（3）To obtain an SCN to use later witha flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.

（4）To compute or retrieve a past timeto use in a query, use a function return value as a time stamp or SCN argument.For example, add or subtract an INTERVAL value to the value of the SYSTIMESTAMPfunction.

（5）Use Oracle Flashback Query, OracleFlashback Version Query, and Oracle Flashback Transaction Query locally orremotely. An example of a remote Oracle Flashback Query is:

(SELECT * FROM employees@some_remote_hostAS OF

   TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);

（6）To ensure database consistency,always perform a COMMIT or ROLLBACK operation beforequerying past data.

（7）Remember that all flashbackprocessing uses the current session settings, such as national language andcharacter set, not the settings that were in effect at the time being queried.

（8）Remember that DDLs that alter thestructure of a table (such as drop/modify column, move table, drop partition,truncate table/partition, and add constraint) invalidate any existing undo datafor the table. If you try to retrieve data from a time before such a DDLexecuted, error occurs.DDL operations that alter the storage attributes of a table (such as PCTFREE, INITRANS,and MAXTRANS) do not invalidate undo data.

（9）To query past data at a precisetime, use an SCN. If you use a time stamp, the actual time queried might be upto 3 seconds earlier than the time you specify. Oracle Database uses SCNsinternally 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 AMand 8:46 AM, respectively. A query for a time between 8:41:00 and 8:45:59 AM ismapped to SCN 1000; an Oracle Flashback Query for 8:46 AM is mapped to SCN1005. 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 just beforethe DDL operation, causing error .

（10）You cannot retrieve past data froma dynamic performance (V$) view. A query on such a view always returns currentdata.

（11）You can perform queries on pastdata in static data dictionary views, such as *_TABLES.

十二.Performance Guidelines for Oracle Flashback Technology

（1）    Use the DBMS_STATS package togenerate statistics for all tables involved in an Oracle Flashback Query. Keepthe statistics current. Oracle Flashback Query uses the cost-based optimizer,which relies on these statistics.

--Flashback Query 依賴於統計資訊，所以要保證統計資訊的準確行。

（2）    Minimize the amount of undo data that must be accessed. Use queriesto select small sets of past data using indexes, not to scan entire tables. Ifyou must scan a full table, add a parallel hint to the query.

--減少對undo 資料的訪問，查詢使用索引，避免全表掃描，如果使用全表掃描，可以使用hint 並行查詢。

The performancecost in I/O is the cost of paging in data and undo blocks that are not in thebuffer cache. The performance cost in CPU use is the cost of applying undoinformation to affected data blocks. When operating on changes in the recentpast, flashback operations are CPU-bound.

       --I/O 效能的消耗用在不在buffer cache裡的data 和undoblock。 CPU 的消耗在應用undo 資訊。

（3）    For Oracle Flashback Version Query, use index structures. OracleDatabase keeps undo data for index changes and data changes. Performance ofindex lookup-based Oracle Flashback Version Query is an order of magnitudefaster than the full table scans that are otherwise needed.

（4）    In an Oracle Flashback Transaction Query, the xid columnis of the type RAW(8). To take advantage of the index built on the xid column,use the HEXTORAW conversion function: HEXTORAW(xid).

（5）A Oracle Flashback Query against amaterialized view does not take advantage of query rewrite optimization.

-------------------------------------------------------------------------------------------------------

版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Email:  

Skype:  tianlesoftware

Blog:    

Weibo: 

Twitter:

Facebook: