在Oracle database10g中，Oracle引入了两个闪回特性，即闪回版本查询和闪回事务处理查询，允许撤销跟随在数据库中逻辑错误后面的数据所发生的错误变化。在一个可以的数据错误之后，首先使用闪回版本查询来确定属于该错误事务处理的表行的版本。在确定出错的事务处理后，使用闪回事务处理查询审计该事务所做的所有变化。使用由闪回事务处理查询的undo_sql列提供的SQL代码，撤销由错误事务处理所做的变化。这样，闪回事务处理提供了强大的撤销数据库中逻辑错误的办法。
在Oracle 11g中，可以使用新的闪回事务处理取消特性完成必须由闪回版本查询和闪回事务处理查询共同完成的任务。通常一个数据错误会引起其他依赖事务处理使用有错的数据执行。闪回事务处理取消时一个新的逻辑恢复特性，它使你返回目标事务处理，并使依赖事务处理回到原来的状态。闪回事务处理取消特性识别并修正内部的事务处理以及以及依赖的事务处理，从而彻底撤销逻辑数据错误的作用。撤销插入时，更新和删除操作的完整集合确保了事务处理的原子性和一致性原理被维护。这样，当数据库联机时，通过执行一个取消命令(单独运行transaction_backout过程），就可以执行数据的逻辑恢复。如果正在使用Database Console，点击一下就可以取消事务。依赖事务处理与目标事务处理可能有以下几种关系：
- 写后写(write-after-write,WAW)关系，依赖事务处理更改了由目标事务处理更改的相同数据。
- 主键约束关系，依赖事务处理插入与目标事务处理删除的相同的逐渐。
通过执行补偿事务处理（compensating transactions,它将受不要的事务处理影响的数据恢复到原来状态),数据库撤销事务处理的变化。闪回事务处理依靠撤销数据（以及为撤销块产生的重做）来创建补偿事务处理。因此，要撤销一组不要的事务处理，需要同时撤销数据和归档重做日志。
- 闪回事务处理取消的先觉条件 ：必须首先开启数据库的补全日志(supplemental logging),然后给要使用闪回事务处理特性的用户授予特定的权限。为了启动数据库补全日志，使用以下命令：alter database add supplemental log data; alter database add supplemental log data (primary key) columns。 除了启动补全日志外，还要给闪回事务处理取消特性的用户授予以下权限： grant execute on dbms_flashback to hr; grant select any transaction to hr; 用户必须有flashback 权限，可以通过授予DBMS_FLASHBACK表的execute权限来授予。另外，用户还需要select any transaction权限。如果用户想要取消属于自己的模式中的事务处理，则无需增加权限。但是，如果某个用户想要取消其他模式的事务处理，还必须授予他对手事务处理取消影响的所有表的DML权限。
- 使用transaction_backout过程 补偿事务处理的思想对于事务处理取消特性至关重要。通过使用撤销数据，一个补偿事务处理可以取消一个或多个事务处理。使用DBMS_FLASHBACK包的transaction_backpout过程很容易回退不想要的事务处理。以下是transaction_backout过程的结构：
dbms_flashback.transaction_backout(
numtxns NUMBER,
xids xid_array,
options BINARY_INTEGER DEFAULT NOCASCADE,
scnhint NUMBER DEFAULT 0);
transaction_backout过程有四个参数，如下：
- NumTxns:此参数为被取消的事务处理的数量。
- Names: 此参数为被取消的事物处理的列表（按名字排序）。
- Timehint: 如果你按名字标识事务处理，则可以提供一个时间提示，如在事务处理开始前的某个时间。
- Options:该事务指定某个事务处理及其依赖的事务处理被取消的顺序。
transaction_backout过程只分析事务处理之间的依赖性，执行DML操作，并提供一个报告。但是此过程不自动提交这些DML操作。该过程所做的就是通过锁定受影响的表行即表本身，保证其他事务处理的依赖性不受取消操作影响。必须明确执行commit语句，确保永久取消。
数据库将自动为取消操作提供一个事务处理名，但是由你给该操作明确的名字有利于后来的神奇。如果transaction_backout过程的执行成功完成。则表明已经取消了单个事务处理且无任何依赖事务处理。一个取消操作花费时间的多少将依赖于被取消的事务处理产生的重做的数量–重做日志量越大，完成transaction_backout操作所花费的时间越长。
transaction_backout过程有4个选项。
- cascade:取消所有事务处理，其中包括依赖的事务处理，这些事务处理在取消父（目标）事务处理之前首先取消。
- nocascade:没有任何依赖事务处理。默认值。
- nicascade_force:只取消目标事务处理，忽略依赖事务处理。
- nonconflict_only:只取消在目标事务处理中不冲突的那些行。
注意：nocascade的默认值期望待取消的事务处理无任何依赖事务处理。
transaction_backout报告
可用DBA_FLASHBACK_TRANSACTION_STATE和DBA_FLASHBACK_TRANSACTION_REPORT视图检查事务处理取消操作的细节。transaction_backout过程提供这两个视图。如果一个事务处理出现在DBA_FLASHBACK _TRANSACTION_STATE视图中，则意味着该事务处理已经成功取消。对于每个被取消的事务处理，DBA_FLASHBACK_TRANSACTION_REPORT视图提供了详细的报告。 如果不想使用DBMS_FLASHBACK包，还可用 Database Console执行事务处理取消操作。
转发请注明源地址：https://www.askmac.cn
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6
Information in this document applies to any platform.
n/a
Purpose
Oracle first introduced logminer technology in 8i. Since then the logminer technology has undergone several improvements. In Oracle 11g the logminer capabilities are extended with the introduction of dbms_flashback.transacton_backout procedure. This procedure can be used to backout a transaction and all its dependent transactions while the database is online. The purpose of this code sample is to demonstrate flashback capabilities of transaction_backout procedure.
Scope and Application
The intended audience is database administrators with strong understanding of database administration.
Flashback transactions using dbms_flashback.transaction_backout procedure
The dbms_flashback.transaction_backout procedure uses logminer data to backout transactions. As long as redo log files are available we can use this procedure to backout any transaction while the database is online. But if the redo log files are large then the time it takes to backout transactions can be slow. To be able to use dbms_flashback.transaction_backout procedure following requirements must be met:
1. Database must be running in archivelog mode.
alter database archivelog;
2. Supplemental logging must be turned on at the database level.
alter database add supplemental log data;
Code sample:
————-
In the following example we use transaction_backout procedure to undo specified transactions on emp table to restore the data to a previous point-in-time.
Note: This note assumes that a user called ‘test’ is precreated with appropriate privs.
1. Create a test table called emp and insert two rows
create table emp(
empno number(6),
ename varchar2(20),
department number(3),
salary number(8,2),
location varchar2(20));
insert into emp values(1000,’AAAAAAA’,100,1000,’TEXAS’);
insert into emp values(2000,’BBBBBBB’,200,2000,’CALIFORNIA’);
commit;
2. Note down the restore point
set serveroutput on
DECLARE
I NUMBER;
BEGIN
I := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
DBMS_OUTPUT.PUT_LINE(‘Start_scn# ‘||I);
END;
/
SQL> @print_start_scn.sql
Start_scn# 5009341
PL/SQL procedure successfully completed.
SQL> insert into emp values(3000,’CCCCCCC’,300,3000,’NEW YORK’);
1 row created.
SQL> commit;
SQL> insert into emp values(4000,’DDDDDDD’,400,4000,’ALASKA’);
1 row created.
SQL> commit;
SQL> insert into emp values(5000,’EEEEEEE’,500,5000,’NEW JERSEY’);
1 row created.
SQL> commit;
Commit complete.
SQL> update emp
2 set salary = 9999
3 where empno = 1000;
1 row updated.
SQL> commit;
Commit complete.
SQL> update emp
2 set location = ‘XXXXX’
3 where salary = 9999;
1 row updated.
SQL> commit;
4. Note down the scn point after the DML
SQL> @print_start_scn.sql
Start_scn# 5010999
PL/SQL procedure successfully completed.
5. Create a table called changed_tables to store transaction_ids
create table changed_tables
(table_name varchar2(256),
xid raw(8),
scn number)
/
6. Now extract all transaction ids between two scn numbers.
connect / as sysdba
— compile and execute extract_txn_ids procedure
— this procedure sets up logminer to extract all transaction identifiers between two —- –scn(Step 2 and 4) numbers
— and stores into another table called changed_tables.
CREATE OR REPLACE PROCEDURE extract_txn_ids (lcrscn IN NUMBER, escn in number) AS
lname VARCHAR2(256);
vsql varchar2(2000);
BEGIN
dbms_logmnr.start_logmnr(startscn => lcrscn,endscn => escn,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE);
insert into test.changed_tables
select distinct seg_name,xid,scn
from v$logmnr_contents where seg_owner = ‘TEST’
and seg_name not in(‘CHANGED_TABLES’)
and scn >= lcrscn
and scn<= escn; commit; END; / SQL> exec extract_txn_ids(5009341,5010999);
PL/SQL procedure successfully completed.
6. Compile transaction backout procedure. This procedure in turn calls dbms_flashback.transaction_backout procedure to backout all transactions (including dependent txns) recorded in the changed_tables.
connect / as sysdba
create or replace procedure txn_backout(sscn in number)
as
txn_array sys.xid_array := sys.xid_array();
i number;
begin
i := 1;
–initialize xid_array from changed_tables
for txn in (select distinct xid from test.changed_tables)
loop
txn_array.extend;
txn_array(i) := txn.xid;
i := i + 1;
end loop;
i := i – 1;
— 3 input variables are passed to transaction_backout
— i number of txns
— txn_array array of txn ids
— sscn starting point to logminer
dbms_flashback.transaction_backout (
numtxns => i,
xids => txn_array,
options => dbms_flashback.cascade,
scnhint => sscn
);
–issue commit as dbms_flashback.transaction_backout does not include commit and —-txn backout.
commit;
END;
/
Note: In the above code sample ‘cascade’ option was used to rollback transactions and all its dependents. Please refer to Oracle documentation for details on other options available and their implications.
The scnhint parameter value provides a starting point for logminr to look for the starting point, if no hint is provided then transaction_backout procedure uses undo_retention target as the hint.
7. Execute transaction backout procedure.
SQL> exec txn_backout(5009341);
PL/SQL procedure successfully completed.
8. Make sure all transactions are rolled back.
SQL> select * from test.emp;
EMPNO ENAME DEPARTMENT SALARY LOCATION
———- ————————- ———- ——————–
1000 AAAAAAA 100 1000 TEXAS
2000 BBBBBBB 200 2000 CALIFORNIA
Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1.0 to 11.2.0.2.0 – Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms
We can receive the following error when using DBMS_FLASHBACK.TRANSACTION_BACKOUT
DBMS_FLASHBACK.TRANSACTION_BACKOUT(i, xid_array, DBMS_FLASHBACK.CASCADE);
ORA-00600: internal error code, arguments: [ktftb_backout_xids_3], [1], [0], [], [], [], [], [], [], [], [], []
The call stack contains the following functions:
ktftbp_backout_xids <- spefcmpa <- spefmccallstd <- pextproc Cause This error can be caused if the length of the array (the first input parameter from the procedure) does not match with the actual length of the array passed as the second argument of the procedure. In the case above, i was higher than xid_array. COUNT. Solution First of all, the error message is very misleading , as we should have a more specific error message (telling us that one of the parameters input is wrong). An Enhancement Request was made for this : Bug 9860300 - BETTER ERROR HANDLING FOR PROCEDURE DBMS_FLASHBACK.TRANSACTION_BACKOUT Also, the ER treats the necessity of the first parameter, as we can take the array size from array.COUNT function. The solution would be to correct the first parameter to match the array size.
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6
This problem can occur on any platform.
Symptoms
DBMS_FLASHBACK fails with ORA-1031 when executed by an user who is not granted the DBA role:
declare
trans_arr sys.xid_array;
begin
trans_arr := sys.xid_array(‘&1’);
dbms_flashback.transaction_backout (numtxns => 1,xids => trans_arr,options =>dbms_flashback.cascade,scnhint => 7643000);
end;
/
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_FLASHBACK”, line 37
ORA-06512: at “SYS.DBMS_FLASHBACK”, line 70
ORA-06512: at line 5
Cause
The user executing this package is not granted the CREATE ANY TABLE privilege.
Solution
The user needs the CREATE ANY TABLE privilege to be able to flashback the transaction.
grant CREATE ANY TABLE to;