進行精細粒度的資料誤操作還原，是我們在實際工作中經常遇到的場景。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功能，可以將事務全部逆轉。

使用Flashback Transaction方法來恢復資料表資料

相關文章