使用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 Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL 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 bytes
Fixed Size 2253664 bytes
Variable Size 973081760 bytes
Database Buffers 620756992 bytes
Redo Buffers 7319552 bytes
Database 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 Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 40
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
-------------------------
YES
2、实验数据构建
为了有一个干净的数据环境，全新创建一个用户Test，进行测试。
SQL> create user test identified by test;
User created
SQL> grant connect, resource to test;
Grant succeeded
构建数据表emp，插入部分数据作为初始状态。
SQL> create table test.emp as select * from scott.emp where 1=0;
Table created
SQL> 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 inserted
SQL> insert into test.emp values (20,'BBB','STF', 10,sysdate-10000,500,100,'10');
1 row inserted
SQL> commit;
Commit complete
SQL> 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 inserted
SQL> insert into test.emp values (40,'DDD','MANG', null,sysdate-10000,5000,1000,'10');
1 row inserted
SQL> insert into test.emp values (50,'EEE','STF', 10,sysdate-10000,500,100,'10');
1 row inserted
SQL> insert into test.emp values (60,'FFF','STF', null,sysdate-20000,5000,100,'10');
1 row inserted
SQL> commit;
Commit complete
SQL> update test.emp set comm=1000 where empno=50;
1 row updated
SQL> commit;
Commit complete
SQL> update test.emp set comm=1000 where empno=60;
1 row updated
SQL> 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 created
SQL> 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 1795812
EMP 06000D00E3050000 1795883
EMP 04000D00BC040000 1795877
EMP 0200150064070000 1795827
EMP 0200150064070000 1795844
EMP 0200150064070000 1795835
6 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 37
ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 70
ORA-06512: ?? "SYS.TXN_BACKOUT", line 21
ORA-06512: ?? line 1
遇到了错误信息，检查错误代码。
[oracle@NCR-Standby-Asm ~]$ oerr ora 55510
55510, 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 completed
SQL> 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 bytes
Fixed Size 2253664 bytes
Variable Size 973081760 bytes
Database Buffers 620756992 bytes
Redo Buffers 7319552 bytes
Database 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 Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 38
Current log sequence 40
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEMENTAL_LOG_DATA_MIN
-------------------------
NO
4、结论
本篇介绍了一种通过Log Miner和Flashback Transaction结合来恢复小规模事务，逆转误操作的情况。这种操作相对于Flashback Query的好处在于联动Cascade功能，可以将事务全部逆转。