Oracle 11gR2 中 Flashback 技术说明

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 theAutomatic Undo Management (AUM)system to obtain metadata and historical data for transactions. They rely onundo data, which are records of the effects of individualtransactions. For example, if a user runs anUPDATEstatement 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 theASOFclauseof theSELECTstatement.

--使用这个特性可以在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 theVERSIONSBETWEENclauseof theSELECTstatement.

--使用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 viewFLASHBACK_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 (seeFlashbackTransaction).

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 ofDBMS_FLASHBACKpackage.)

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 aDROPTABLEstatement.

--使用该特性可以恢复被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 inOracleDatabase Administrator's Guide. 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 theUNDO_RETENTION parameter.

Note:

You can queryV$UNDOSTAT.TUNED_UNDORETENTIONtodetermine the amount of time for which undo is retained for the current undotablespace.

--可以通过V$UNDOSTAT.TUNED_UNDORETENTION 来查看事务对应的undo存在当前undo 表空间的时间。

SettingUNDO_RETENTIONdoesnot 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 theRETENTIONGUARANTEEclause for the undo tablespaceto ensure that unexpired undo data is not discarded.

--设置RETENTIONGUARANTEE 属性来确保没有过期的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, enableARCHIVELOG:

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 theALTERTABLEstatementwith theRETENTIONoption.

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, grantFLASHBACKandSELECTprivilegeson those objects.

To allow querieson all tables, grant theFLASHBACKANYTABLEprivilege.

2.5.2 For OracleFlashback Transaction Query

Grant theSELECTANYTRANSACTIONprivilege.

To allowexecution of undo SQL code retrieved by an Oracle Flashback Transaction Query,grantSELECT,UPDATE,DELETE, andINSERTprivilegesfor specific tables.

2.5.3 For DBMS_FLASHBACK Package

To allow accessto the features in theDBMS_FLASHBACKpackage, grant theEXECUTEprivilegeonDBMS_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 theFLASHBACKARCHIVEobjectprivilege on that Flashback Data Archive to that user. To grant the FLASHBACKARCHIVEobjectprivilege, you must either be logged on asSYSDBAor haveFLASHBACKARCHIVEADMINISTERsystemprivilege.

To allowexecution of these statements, grant theFLASHBACKARCHIVEADMINISTERsystemprivilege:

--为了执行，需要如下权限：

(1)CREATEFLASHBACKARCHIVE

(2)ALTERFLASHBACKARCHIVE

(3)DROPFLASHBACKARCHIVE

To grant theFLASHBACKARCHIVEADMINISTERsystemprivilege, you must be logged on asSYSDBA.

To create adefault Flashback Data Archive, using either theCREATEFLASHBACKARCHIVEorALTERFLASHBACKARCHIVEstatement,you must be logged on asSYSDBA.

To disableFlashback Data Archive for a table that has been enabled for Flashback DataArchive, you must either be logged on asSYSDBAor have theFLASHBACKARCHIVEADMINISTERsystemprivilege.

三.UsingOracle Flashback Query (SELECT AS OF)

To use Oracle Flashback Query, use aSELECTstatementwith anASOFclause. 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 theemployeestable,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-1retrieves the state of the record forChungat 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-2restores Chung's information to theemployeestable:

--还原之前的数据，重新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 theASOFclause 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 usetheASOFclause in queries to perform data definition language(DDL) operations (such as creating and truncating tables) or data manipulationlanguage (DML) statements (such asINSERTandDELETE) 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 anASOFclause inside anINSERTorCREATETABLEASSELECTstatement.

（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 theASOFclausein theSELECTstatement 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);

SYSTIMESTAMPrefers to the time zoneof the database host environment.

（6）You can use theASOFclausein self-joins, or in set operations such asINTERSECTandMINUS,to extract or compare data from two different times.

You can storethe results by preceding Oracle Flashback Query with aCREATETABLEASSELECTorINSERTINTOTABLESELECTstatement.For example, this query reinserts into tableemployeesthe rows thatexisted an hour ago:

INSERT INTO employees

(SELECT * FROM employees

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

)

MINUS SELECT * FROM employees);

SYSTIMESTAMPrefers 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 aCOMMITstatement is executed.

--使用该特性可以查看指定时间，特定行的不同version，该version 在用户commit时创建。

Specify OracleFlashback Version Query using theVERSIONSBETWEENclauseof theSELECTstatement. The syntax is:

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

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

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

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 inTable12-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 beforestart.
`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`IVERSIONS_OPERATION`.

A given rowversion is valid starting at its timeVERSIONS_START*up to, but notincluding, its timeVERSIONS_END*. That is, it is valid for any timetsuchthatVERSIONS_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,<version_end.

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 useVERSIONS_XIDwithOracle 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 viewFLASHBACK_TRANSACTION_QUERY, whosecolumns are described inOracleDatabase Reference.

--Flashback Transaction Query 用来接收在提供的时间内一个事务或者所有事务metadata和历史数据。 Flashback Transaction 查询静态的数据字典视图：FLASHBACK_TRANSACTION_QUERY。

The columnUNDO_SQLshowsthe 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 theSQL_UNDOcodeis not the exact opposite of the original transaction. For example, aSQL_UNDOINSERToperationmight not insert a row back in a table at the sameROWIDfrom whichit was deleted.

--UNDO_SQL 列显示的就是事务操作相反的SQL。我们可以使用这个SQL 来还原之前的事务操作。在一些案例下，SQL_UNDO里的内容不是完全正确的原始数据。

This statementqueries theFLASHBACK_TRANSACTION_QUERYview 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 theLOGON_USERresponsible 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 queryFLASHBACK_TRANSACTION_QUERYwithoutspecifyingXIDin theWHEREclause, 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;

Nowempanddepthaveone row each. In terms of row versions, each table has one version of one row.Suppose that an erroneous transaction deletesempno111fromtableemp:

--操作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 reinsertsempno111into theemptablewith 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 intheemptable 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:

XIDSTART_SCN END_SCN O EMPNAME SALARY

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

09001100B2200000 10093466 I Tom 927

030002002B210000 10093459 D Mike 555

0800120096200000 1009337510093459 I Mike555

3 rows selected.

The resultstable rows are in descending chronological order. The third row corresponds tothe version of the row in the tableempthat was inserted in thetable when the table was created. The second row corresponds to the row inempthatthe erroneous transaction deleted. The first row corresponds to the version ofthe row inempthat was reinserted with a new employee name.

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

--使用Flashback Transaction Query 来解决问题：

SELECTxid, 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 1009345210093459 DELETE HR

insert into"HR"."EMP"("EMPNO","EMPNAME","SALARY")values ('111','Mike','655');

030002002B210000 1009345210093459 INSERT HR

delete from "HR"."DEPT"where ROWID = 'AAATjuAAEAAAAJrAAB';

030002002B210000 1009345210093459 UPDATE HR

update "HR"."EMP" set"SALARY" = '555' where ROWID = 'AAATjsAAEAAAAJ7AAA';

030002002B210000 1009345210093459 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 1009343510093446 INSERT DEPT HR

030002002B210000 1009345210093459 DELETE EMP HR

030002002B210000 1009345210093459 INSERT DEPT HR

030002002B210000 10093452 10093459 UPDATE EMPHR

0800120096200000 1009337410093375 INSERT EMP HR

09001100B2200000 1009346210093466 UPDATE EMP HR

09001100B2200000 1009346210093466 INSERT EMP HR

8 rows selected.

Note:

Because thepreceding query does not specify theXIDin theWHEREclause,it scans many unrelated rows, degrading performance.

七. Using ORA_ROWSCN

ORA_ROWSCNis 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 recentCOMMIToperation 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 Whalen2800

The most recentCOMMIToperationfor the row in the current session took place at approximately SCN 9371092. Toconvert an SCN to the correspondingTIMESTAMPvalue, use the functionSCN_TO_TIMESTAMP(documentedinOracleDatabase SQL Language Reference).

--可以通过SCN_TO_TIMESTAMP 将SCN 转换成TIMESTAMP.

ORA_ROWSCNisa conservative upper bound of the latest commit time—the actual commit SCN canbe somewhat earlier.ORA_ROWSCNis more precise (closer to the actualcommit SCN) for a row-dependent table (created usingCREATE TABLEwiththeROWDEPENDENCIESclause).

Note:

ORA_ROWSCNisnot supported for Flashback Query. Instead, use the version querypseudocolumns, which are provided explicitly for Flashback Query. Forinformation about these pseudocolumns, seeOracleDatabase SQL Language Reference.

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

7.1 Scenario:Package Subprogram Might Change Row

Your applicationexamines a row of data and records the correspondingORA_ROWSCNas202553. 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_ROWSCNis 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, thenORA_ROWSCNis no longer 9371092, andthe update fails.

Your applicationqueries again to obtain the new row data andORA_ROWSCN. Suppose that theORA_ROWSCNisnow415639. The application tries the conditional update again, using thenewORA_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 theORA_ROWSCNpseudocolumn.However, becauseORA_ROWSCNis available inside the policy function,you can:

--当在表上使用VPD策略后，就不能使用ORA_ROWSCN列，但是可以在该策略函数里面使用，具体方法如下：

（1）Create a function that returns arow SCN, as inExample12-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 runExample12-3, you needCREATEANYCONTEXTsystem 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

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

BEGIN

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

END;

八．UsingDBMS_FLASHBACK Package

TheDBMS_FLASHBACKpackage provides the same functionalityas Oracle Flashback Query, but Oracle Flashback Query is sometimes moreconvenient.

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

TheDBMS_FLASHBACKpackageacts 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_FLASHBACKpackage to perform queries on past data withoutspecial clauses such asASOForVERSIONSBETWEEN, youcan reuse existing PL/SQL code to query the database at earlier times.

--DBMS_FLASHBACK 包扮演着一个时间机器的作用。

You must have theEXECUTEprivilegeon theDBMS_FLASHBACKpackage.

--必须有对dbms_flashback包的execute权限。

To use theDBMS_FLASHBACKpackagein your PL/SQL code:

--在PL/SQL 代码中使用该包的方法：

（1）Specify apast time by invoking eitherDBMS_FLASHBACK.ENABLE_AT_TIMEorDBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.

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

（3）Return to thepresent by invokingDBMS_FLASHBACK.DISABLE.

You must invokeDBMS_FLASHBACK.DISABLEbeforeinvokingDBMS_FLASHBACK.ENABLE_AT_TIMEorDBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBERagain.You cannot nest enable/disable pairs.

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

--使用游标来存储query 的结果：

（1）PerformINSERTorUPDATEoperationsto modify the current database state by using the stored results from the past.

（2）Comparecurrent data with the past data. After invokingDBMS_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 asMINUSorUNIONtocontrast or combine the past and current data.

You can invokeDBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERatany time to get the current System Change Number (SCN).DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERalwaysreturns the current SCN regardless of previous invocations ofDBMS_FLASHBACK.ENABLE.

--可以在任何时候条用DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER来查看当前的SCN值。

九.Using Flashback Transaction

TheDBMS_FLASHBACK.TRANSACTION_BACKOUTprocedurerolls back a transaction and its dependent transactions while the databaseremains online. This recovery operation uses undo data to create and run the compensatingtransactionsthat 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 theCOMPATIBLEinitializationparameter 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 theTRANSACTION_BACKOUTprocedureare:

--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 fromTable12-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_BACKOUTanalyzesthe transactional dependencies, performs DML operations, and generates reports.TRANSACTION_BACKOUTdoesnot 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 thatTRANSACTION_BACKOUTgenerates, query the static datadictionary views*_FLASHBACK_TXN_STATEand*_FLASHBACK_TXN_REPORT.

--可以通过如下2个静态视图来查看TRANSACTION_BACKOUT的报告.

9.3.1 *_FLASHBACK_TXN_STATE

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

*_FLASHBACK_TXN_STATEismaintained atomically for compensating transactions. If a compensatingtransaction is backed out, all changes that it made are also backed out, and*_FLASHBACK_TXN_STATEreflectsthis. For example, if compensating transactionctbacks outtransactionst1andt2, thent1andt2appearin*_FLASHBACK_TXN_STATE. Ifctitself is later backed out, theeffects oft1andt2are reinstated, andt1andt2disappearfrom*_FLASHBACK_TXN_STATE.

9.3.2 *_FLASHBACK_TXN_REPORT

The static datadictionary view*_FLASHBACK_TXN_REPORTprovides 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 asSYSDBA, 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 theFLASHBACKARCHIVEobjectprivilege 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 neitherLONGnornested columns.

After flashback archiving is enabled for a table, you can disable it only if you either havetheFLASHBACKARCHIVEADMINISTERsystem privilege or youare logged on asSYSDBA.

--当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 theFLASHBACKARCHIVEobject privilege.

这部分的详细内容参考我的Blog：

oracle 11g 新特性Flashback Data Archive 说明

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

十一.GeneralGuidelines for Oracle Flashback Technology

（1）Use theDBMS_FLASHBACK.ENABLEandDBMS_FLASHBACK.DISABLEproceduresaround 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, useDBMS_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 anINTERVALvalue to the value of theSYSTIMESTAMPfunction.

（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 aCOMMITorROLLBACKoperation 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, errorORA-01466occurs.DDL operations that alter the storage attributes of a table (such asPCTFREE,INITRANS,andMAXTRANS) 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 errorORA-01466.

（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 theDBMS_STATSpackage 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, thexidcolumnis of the typeRAW(8). To take advantage of the index built on thexidcolumn,use theHEXTORAWconversion function:HEXTORAW(xid).

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

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

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

Blog: http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823

DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940

分享到：

Win7 下 NLS_LANG 参数位置 | Oracle 11g中 ADD COLUMN 功能增强说明

2012-02-02 22:42
浏览 651
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论