使用dbms_flashback工具包實現閃回查詢功能

 

Flashback Query是藉助Oracle Undo過期資料而實現的一種方便的邏輯恢復功能。在Undo Tablespace支援的情況下，我們可以查詢到過去一個特定的時間點（或者SCN點）某個資料表的時間版本。

標準的Flashback Query語句是需要藉助as of timestamp| as of scn語句在資料表後面，用於指定檢視的資料表過去時間點是什麼。這種方式從資料庫管理員的角度的確是很方便，特別是那些直接訪問後臺挽救資料的開發管理人員。

但是在兩種情況下，as of指定時間的方式存在一些問題。首先是應用程式中的語句，開發嵌入到應用程式的程式碼是不能輕易修改的，也就是說我們在procedure或者package的外面，是不能加入那些as of語句指定時間。另一方面，一個時間點資料可能是涉及多個資料表版本操作，逐個表指定是存在很多的問題。於是，使用dbms_flashback包的過去時間點上下文指定功能，就可以解決上面說的問題。

 

1、環境說明

 

筆者使用Oracle 11gR2進行測試實驗，具體版本為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

 

 

當前沒有配置補充日誌supplemental log data，同時Undo配置關鍵引數如下：

 

 

 

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

 

SUPPLEMENTAL_LOG_DATA_MIN

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

NO

 

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     9000

undo_tablespace                      string      UNDOTBS1

 

 

dbms_flashback包的描述資訊如下：

 

 

SQL> desc dbms_flashback

Element                        Type     

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

ENABLE_AT_TIME                 PROCEDURE

ENABLE_AT_SYSTEM_CHANGE_NUMBER PROCEDURE

DISABLE                        PROCEDURE

GET_SYSTEM_CHANGE_NUMBER       FUNCTION 

NOCASCADE                      CONSTANT 

NOCASCADE_FORCE                CONSTANT 

NONCONFLICT_ONLY               CONSTANT 

CASCADE                        CONSTANT 

TRANSACTION_BACKOUT            PROCEDURE

 

 

在筆者之前的文章中，經常使用dbms_flashback.get_system_change_number來獲取系統的SCN編號，並且演示過transaction_backout方法逆轉整體事務的策略。本篇集中在enable_at_time、enable_at_system_change_number和disable方法上。

 

2、dbms_flashback時間機器

 

enable_at_time和enable_at_system_change_number的作用相同，都是將當前會話的上下文逆轉到過去的一個時間點，區別僅在於制定的是時間點還是SCN編號。

正確執行兩個方法之後，所有的查詢都是基於指定的時間點進行的，類似於電影中的“時間機器”。背後使用的Flashback Query過程根本不需要我們手工指定時間點在資料表後面。

注意：同flashback query使用相同，dbms_flashback方法不允許在SYS使用者下使用，如果使用就會報錯。

 

 

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107410);

 

begin dbms_flashback.enable_at_system_change_number(query_scn => 2107410); end;

 

ORA-08185: ???§ SYS ???§??????

ORA-06512: ?? "SYS.DBMS_FLASHBACK", line 12

ORA-06512: ?? line 1

 

 

我們的演示實驗會在scott使用者下進行。首先需要給scott使用者賦予dbms_flashback包的執行許可權。

 

 

SQL> grant execute on dbms_flashback to scott;

Grant succeeded

 

 

切換到scott使用者，建立實驗資料表。

 

 

SQL> create table test as select empno, sal from emp where rownum<4;

Table created

 

SQL> select * from test;

 

EMPNO       SAL

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

 7369    800.00

 7499   1600.00

 7521   1250.00

 

 

此時系統時間和SCN編號如下：

 

 

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

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

                 2107631

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

TO_CHAR(SYSDATE,'YYYY-MM-DDHH2

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

2015-06-29 13:49:13

 

 

之後進行所謂的“誤操作”。

 

 

SQL> update test set sal=1000 where empno=7521;

1 row updated

 

SQL> commit;

Commit complete

 

SQL> select * from test where empno=7521;

EMPNO       SAL

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

 7521   1000.00

 

 

傳統的Flashback Query策略。

 

 

SQL> select * from test as of scn 2107631 where empno=7521;

 

EMPNO       SAL

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

 7521   1250.00

 

 

下面使用dbms_flashback方法，指定出一個SCN編號。

 

 

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631); --開啟了查詢；

 

PL/SQL procedure successfully completed

 

SQL> select * from test where empno=7521;

 

EMPNO       SAL

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

 7521   1250.00

 

SQL> exec dbms_flashback.disable;

 

PL/SQL procedure successfully completed

 

--disable之後，資料恢復

SQL> select * from test where empno=7521;

 

EMPNO       SAL

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

 7521   1000.00

 

 

注意：使用enable方法之後，我們以直接的方式查詢到過去的時間點方法。如果操作結束，需要使用disable方法關閉設定的上下文時間。

 

如果指定timestamp方法，效果是相同的。

 

 

SQL> exec dbms_flashback.enable_at_time(query_time => to_timestamp('2015-06-29 13:49:13','yyyy-mm-dd hh24:mi:ss'));

 

PL/SQL procedure successfully completed

 

SQL> select * from test where empno=7521;

 

EMPNO       SAL

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

 7521   1250.00

 

SQL> exec dbms_flashback.disable;

 

PL/SQL procedure successfully completed

 

 

最後，我們考慮一下，如果在過去的時間上下文中進行修改，修改相關資料和無關資料，結果是如何呢？

 

SQL> exec dbms_flashback.enable_at_system_change_number(query_scn => 2107631);

 

PL/SQL procedure successfully completed

 

SQL> select * from test where empno=7521;

 

EMPNO       SAL

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

 7521   1250.00

 

SQL> select * from test;

 

EMPNO       SAL

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

 7369    800.00

 7499   1600.00

 7521   1250.00

 

SQL> delete test where empno=7499;

 

delete test where empno=7499

 

ORA-08182: 在閃回模式下操作不受支援

 

SQL> update test set sal=1000 where empno=7369;

 

update test set sal=1000 where empno=7369

 

ORA-08182: 在閃回模式下操作不受支援

 

SQL> insert into test values (1000,1000);

 

insert into test values (1000,1000)

 

ORA-08182: 在閃回模式下操作不受支援

 

SQL> create table m as select * from test;

 

create table m as select * from test

 

ORA-08182: 在閃回模式下操作不受支援

 

 

和時間機器一樣，不能改變歷史。

 

3、結論

 

Dbms_flashback工具包提供了關於閃回技術的很多功能和有意義的場景。藉助dbms_flashback的flashback query上下文，我們可以方便的實現上下文歷史資料查詢檢索。