使用Flashback Transaction方法来恢复数据表数据

进行精细粒度的数据误操作还原，是我们在实际工作中经常遇到的场景。Oracle基于Redo Log和Undo机制，提供实现了诸多分支技术，如Flashback、Log Miner等来进行多粒度的数据恢复。在Oracle 11g中，dbms_flashback.transaction_backout方法提供了在数据库online状态下，直接逆回数据库事务和相关依赖事务的能力。本篇主要介绍如何使用logminer和Flashback包新方法，来实现Oracle事务的逆回操作。1、环境介绍笔者使用Oracle 11g进行测试，版本为11.2.0.4。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 – Production由于需要使用Logminer组件，所以数据库层面需要切换到归档模式，同时启动最小数据级别的补充日志（Supplemental Log）。SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 973081760 bytesDatabase Buffers 620756992 bytesRedo Buffers 7319552 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database add supplemental log data;Database altered.启动数据库进入read write状态。SQL> alter database open;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 38Next log sequence to archive 40Current log sequence 40SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMENTAL_LOG_DATA_MIN-------------------------YES2、实验数据构建为了有一个干净的数据环境，全新创建一个用户Test，进行测试。SQL> create user test identified by test;User createdSQL> grant connect, resource to test;Grant succeeded构建数据表emp，插入部分数据作为初始状态。SQL> create table test.emp as select * from scott.emp where 1=0;Table createdSQL> select * from test.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------SQL> desc test.emp;Name Type Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO NUMBER（4） Y ENAME VARCHAR2（10） Y JOB VARCHAR2（9） Y MGR NUMBER（4） Y HIREDATE DATE Y SAL NUMBER（7,2） Y COMM NUMBER（7,2） Y DEPTNO NUMBER（2） Y SQL> insert into test.emp values （10,"AAA","STF", null,sysdate-10000,1000,100,"10"）;1 row insertedSQL> insert into test.emp values （20,"BBB","STF", 10,sysdate-10000,500,100,"10"）;1 row insertedSQL> commit;Commit completeSQL> select * from test.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 10 AAA STF 1988/2/5 13 1000.00 100.00 10 20 BBB STF 10 1988/2/5 13 500.00 100.00 10此时，SCN时间点如下，作为工作的起始时间点：SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 1795785之后进行了一系列的DML操作。SQL> insert into test.emp values （30,"CCC","STF", 10,sysdate-10000,500,100,"10"）;1 row insertedSQL> insert into test.emp values （40,"DDD","MANG", null,sysdate-10000,5000,1000,"10"）;1 row insertedSQL> insert into test.emp values （50,"EEE","STF", 10,sysdate-10000,500,100,"10"）;1 row insertedSQL> insert into test.emp values （60,"FFF","STF", null,sysdate-20000,5000,100,"10"）;1 row insertedSQL> commit;Commit completeSQL> update test.emp set comm=1000 where empno=50;1 row updatedSQL> commit;Commit completeSQL> update test.emp set comm=1000 where empno=60;1 row updatedSQL> commit;Commit complete操作之后，数据库时间点如下：SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 1795891数据肯定发生了变化，现在实验目标是将数据恢复回去，恢复到SCN=1795785时间点Emp数据表的状态。3、数据恢复实验首先，需要创建一个数据表changed_tables，记录下从Log Miner中抽取出的与数据表EMP相关的事务信息。SQL> create table changed_tables （table_name varchar2（256）, xid raw（8）, scn number）;Table createdSQL> desc changed_tables;Name Type Nullable Default Comments ---------- ------------- -------- ------- -------- TABLE_NAME VARCHAR2（256） Y XID RAW（8） Y SCN NUMBER Y 创建一个Stored Procedure，用于从Log Miner视图中将相关事务操作保存在changed_tables中。SQL> CREATE OR REPLACE PROCEDURE extract_txn_ids （lcrscn IN NUMBER, escn in number） AS 2 lname VARCHAR2（256）; 3 vsql varchar2（2000）; 4 BEGIN 5 dbms_logmnr.start_logmnr（startscn => lcrscn, 6 endscn => escn, 7 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE）; 8 insert into changed_tables 9 select distinct seg_name,xid,scn 10 from v$logmnr_contents where seg_owner = "TEST" 11 and scn >= lcrscn 12 and scn<= escn; 13 commit; 14 END; 15 /Procedure created执行存储过程，输入起始和截止操作的SCN时间点。SQL> exec extract_txn_ids（1795785,1795891）;PL/SQL procedure successfully completed获取到的数据结果。SQL> select * from changed_tables;TABLE_NAME XID SCN---------- ---------------- ----------EMP 0200150064070000 1795812EMP 06000D00E3050000 1795883EMP 04000D00BC040000 1795877EMP 0200150064070000 1795827EMP 0200150064070000 1795844EMP 0200150064070000 17958356 rows selected创建第二个存储过程，逐事务调用dbms_flashback.transaction_backout方法。SQL> create or replace procedure txn_backout（sscn in number） 2 as 3 txn_array sys.xid_array := sys.xid_array（）; 4 i number; 5 begin 6 i := 1; 7 --initialize xid_array from changed_tables 8 9 for txn in （select distinct xid from changed_tables） 10 loop 11 txn_array.extend; 12 txn_array（i） := txn.xid; 13 i := i + 1; 14 end loop; 15 i := i - 1; 16 -- 3 input variables are passed to transaction_backout 17 -- i number of txns 18 -- txn_array array of txn ids 19 -- sscn starting point to logminer 20 21 dbms_flashback.transaction_backout （ 22 numtxns => i, 23 xids => txn_array, 24 options => dbms_flashback.cascade, 25 scnhint => sscn 26 ）; 27 28 --issue commit as dbms_flashback.transaction_backout does not include commit and ----txn backout. 29 commit; 30 END; 31 /Procedure created执行过程程序。SQL> exec txn_backout（sscn => 1795785）;begin txn_backout（sscn => 1795785）; end;ORA-55510: ？？无法启动挖掘ORA-06512: ？？ "SYS.DBMS_FLASHBACK", line 37ORA-06512: ？？ "SYS.DBMS_FLASHBACK", line 70ORA-06512: ？？ "SYS.TXN_BACKOUT", line 21ORA-06512: ？？ line 1遇到了错误信息，检查错误代码。[oracle@NCR-Standby-Asm ~]$ oerr ora 5551055510, 0000, "Mining could not start"// *Cause: Mining could not start for the following reasons.// 1. A logminer session was processing// 2. The database was not mounted or not opened for read and write// 3. Minimum supplemental logging was not enabled// 4. Archiving was not enabled// *Action: Fix the mentioned problems and try again. Note that if// you enable supplemental logging now, you will not be able to// remove a transaction that has committed without supplemental // logging.Oracle在错误解释中介绍了几种报错的原因情形，只有一种是比较可能，就是当前Logminer的日志操作还存在，没有被停止。Log Miner是需要手工关闭的。SQL> exec dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed重新执行操作。SQL> exec txn_backout（sscn => 1795785）;PL/SQL procedure successfully completedSQL> select * from test.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 10 AAA STF 1988/2/5 13 1000.00 100.00 10 20 BBB STF 10 1988/2/5 13 500.00 100.00 10执行成功，同时数据emp被逆转回原来的时间点。最后，我们补充一下关闭归档和补充日志的操作。SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1603411968 bytesFixed Size 2253664 bytesVariable Size 973081760 bytesDatabase Buffers 620756992 bytesRedo Buffers 7319552 bytesDatabase mounted.SQL> alter database noarchivelog;Database altered.SQL> alter database drop supplemental log data;Database altered.SQL> alter database open;Database altered.SQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 38Current log sequence 40SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEMENTAL_LOG_DATA_MIN-------------------------NO4、结论本篇介绍了一种通过Log Miner和Flashback Transaction结合来恢复小规模事务，逆转误操作的情况。这种操作相对于Flashback Query的好处在于联动Cascade功能，可以将事务全部逆转。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址