程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> oracle閃回版本和閃回事務查詢詳解

oracle閃回版本和閃回事務查詢詳解

編輯:關於Oracle數據庫

       --- 說明閃回數據庫

      --- 使用閃回表將表內容還原到過去的特定時間點

      --- 從刪除表中進行恢復

      --- 使用閃回查詢查看截止到任一時間點的數據庫內容

      --- 使用閃回版本查詢查看某一行在一段時間內的各個版本

      --- 使用閃回事務查詢查看事務處理歷史記錄或行

      優點:

      閃回技術由於只能處理更改數據,所以從根本上改變了恢復技術。使用這個技術時,從錯誤中恢復花費的時間等於制造錯誤所花費的時間。當閃回技術使用時,它與介質恢復相比,在易用性、可用性和還原時間方面有明顯的優勢。

      閃回數據庫使用閃回日志執行閃回。閃回刪除使用回收站。其他所有功能都使用還原數據。

    oracle閃回版本和閃回事務查詢詳解    三聯

      閃回時間浏覽

      閃回技術提供的功能可用於查詢方案對象的過去版本、查詢歷史記錄數據以及執行更改分析。每個事務處理在邏輯上都會生成新版本數據庫。使用閃回技術,可通過浏覽這些版本來查找錯誤以及原因。

      · 閃回查詢:查詢特定時間點的所有數據。

      · 閃回版本查詢:查看兩個時間之間行的所有版本已經更改了行的事務處理。

      · 閃回事務處理查詢:查看事務處理做的所有更改。

      使用閃回查詢功能時,可以對自特定時間起的數據庫執行查詢。通過使用select語句的 as of 子句,可指定要查看其數據的時間戳。這有助於分析數據差異。

      實驗一:閃回查詢

      實驗一:閃回查詢:as of timestamp

      SYS@ORCL>conn tyger/tyger

      Connected.

      TYGER@ORCL>create table fb_query as select * from scott.dept;

      Table created.

      TYGER@ORCL>select * from fb_query;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      TYGER@ORCL>set time on;

      09:51:36 TYGER@ORCL>delete fb_query where deptno=10;

      1 row deleted.

      09:51:53 TYGER@ORCL>commit;

      Commit complete.

      09:51:57 TYGER@ORCL>select * from fb_query;

      DEPTNO DNAME LOC

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

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      09:52:06 TYGER@ORCL>select * from fb_query as of timestamp sysdate-1/1440;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      實驗二:閃回查詢應用

      10:25:04 TYGER@ORCL>drop table fb_tyger purge;

      Table dropped.

      10:25:10 TYGER@ORCL>create table fb_tyger as select * from scott.dept;

      Table created.

      10:25:33 TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      10:25:44 TYGER@ORCL>select sysdate from dual;

      SYSDATE

      ---------

      14-MAR-14

      10:26:02 TYGER@ORCL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

      Session altered.

      10:26:30 TYGER@ORCL>select sysdate from dual;

      SYSDATE

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

      2014-03-14 10:26:38

      10:26:38 TYGER@ORCL>update fb_tyger set dname='';

      4 rows updated.

      10:26:51 TYGER@ORCL>commit;

      Commit complete.

      10:26:54 TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      10 NEW YORK

      20 DALLAS

      30 CHICAGO

      40 BOSTON

      10:27:12 TYGER@ORCL>select * from fb_tyger as of timestamp to_timestamp('2014-03-14 10:26:38','yyyy-mm-dd hh24:mi:ss');

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      此處遇到錯誤:

      ERROR at line 1:

      ORA-01466: unable to read data - table definition has changed

      參考文檔: http://blog.csdn.net/wanghui5767260/article/details/21227101

      10:29:21 TYGER@ORCL>select * from fb_tyger as of timestamp sysdate-3/1440;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      10:29:35 TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      10 NEW YORK

      20 DALLAS

      30 CHICAGO

      40 BOSTON

      10:46:22 TYGER@ORCL>set time off

      TYGER@ORCL>update fb_tyger t

      2 set dname =

      3 (select dname from fb_tyger as of timestamp

      4 to_timestamp('2014-03-14 10:26:38','yyyy-mm-dd hh24:mi:ss')

      5 where t.deptno=fb_tyger.deptno);

      4 rows updated.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      實驗三:閃回查詢 as of scn

      TYGER@ORCL>conn / as sysdba

      Connected.

      SYS@ORCL>grant execute on dbms_flashback to tyger;

      Grant succeeded.

      TYGER@ORCL>select dbms_flashback.get_system_change_number from dual;

      GET_SYSTEM_CHANGE_NUMBER

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

      1107246

      TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      TYGER@ORCL>delete fb_tyger where deptno<=30;

      3 rows deleted.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>select * from fb_tyger;

      DEPTNO DNAME LOC

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

      40 OPERATIONS BOSTON

      TYGER@ORCL>select * from fb_tyger as of scn 1107246;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      實驗四:利用PL/SQL包dbms_flashback

      語法:

      · 會話啟用閃回指定時間:

      DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);

      · 會話啟用閃回指定SCN:

      DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);

      · 關閉閃回:

      DBMS_FLASHBACK.DISABLE;

      TYGER@ORCL>conn / as sysdba

      Connected.

      SYS@ORCL>grant execute on dbms_flashback to tyger;

      Grant succeeded.

      SYS@ORCL>conn tyger/tyger

      Connected.

      TYGER@ORCL>

      TYGER@ORCL>

      TYGER@ORCL>

      TYGER@ORCL>create table fb_query1 as select * from scott.dept;

      Table created.

      TYGER@ORCL>create table fb_query2 as select * from scott.dept;

      Table created.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>select * from fb_query1;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      TYGER@ORCL>select * from fb_query2;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      TYGER@ORCL>set time on;

      11:03:38 TYGER@ORCL>update fb_query1 set loc='';

      4 rows updated.

      11:03:52 TYGER@ORCL>commit;

      Commit complete.

      11:03:54 TYGER@ORCL>update fb_query2 set dname='';

      4 rows updated.

      11:04:14 TYGER@ORCL>commit;

      Commit complete.

      11:04:15 TYGER@ORCL>

      11:04:15 TYGER@ORCL>select * from fb_query1;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING

      20 RESEARCH

      30 SALES

      40 OPERATIONS

      11:04:23 TYGER@ORCL>select * from fb_query2;

      DEPTNO DNAME LOC

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

      10 NEW YORK

      20 DALLAS

      30 CHICAGO

      40 BOSTON

      // 閃回定位到5分鐘前,此時若訪問sysdate等時間函數,那麼返回的是當前值而非5分鐘之前。

      11:04:30 TYGER@ORCL>exec dbms_flashback.enable_at_time(sysdate-5/1440);

      PL/SQL procedure successfully completed.

      11:05:09 TYGER@ORCL>select * from fb_query1;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      11:05:29 TYGER@ORCL>select * from fb_query2;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING NEW YORK

      20 RESEARCH DALLAS

      30 SALES CHICAGO

      40 OPERATIONS BOSTON

      //處於閃回會話模式時,不允許執行DML 、 DDL 操作

      11:05:45 TYGER@ORCL>update fb_query1 set dname='';

      update fb_query1 set dname=''

      *

      ERROR at line 1:

      ORA-08182: operation not supported while in Flashback mode

      11:05:59 TYGER@ORCL>exec dbms_flashback.disable;

      PL/SQL procedure successfully completed.

      11:06:18 TYGER@ORCL>select * from fb_query1;

      DEPTNO DNAME LOC

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

      10 ACCOUNTING

      20 RESEARCH

      30 SALES

      40 OPERATIONS

      11:06:30 TYGER@ORCL>select * from fb_query2;

      DEPTNO DNAME LOC

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

      10 NEW YORK

      20 DALLAS

      30 CHICAGO

      40 BOSTON

      11:06:37 TYGER@ORCL>update fb_query1 set dname='' where deptno=10;

      1 row updated.

      11:07:10 TYGER@ORCL>select * from fb_query1;

      DEPTNO DNAME LOC

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

      10

      20 RESEARCH

      30 SALES

      40 OPERATIONS

      // sys 用戶不允許使用dbms_flashback 包

      11:07:20 TYGER@ORCL>conn / as sysdba

      Connected.

      11:07:35 SYS@ORCL>set time off

      SYS@ORCL>exec dbms_flashback.enable_at_time(sysdate-5/1440);

      BEGIN dbms_flashback.enable_at_time(sysdate-5/1440); END;

      *

      ERROR at line 1:

      ORA-08185: Flashback not supported for user SYS

      ORA-06512: at "SYS.DBMS_FLASHBACK", line 3

      ORA-06512: at line 1

      實驗二:閃回版本

      ---通過閃回版本可審計表行,檢索影響行的事務處理的有關信息。然後可使用返回的事務處理標識符來執行事務處理挖掘(通過使用LogMiner)或執行閃回版本查詢。

      所謂版本(version)指的是每次事務所引起的數據行的變化情況,每次變化就是一個版本,oracle提供了閃回版本查詢,從而可以讓讓我們很清楚地看到數據行的整個變化過程,這裡的變化都是已經提交了的事務引起的變化,沒有提交的事務引起的變化不會顯示,閃回版本查詢利用的是undo表空間裡記錄的undo數據。

      使用偽列 獲取一段時間內的版本

      偽列:versions_starttime、versions_endtime、versions_startscn、versions_endscn、versions_xid、versions_operation、

      versions_startscn versions_starttime

      操作時的SCN和時間 如果為空 表示該行在查詢范圍之外創建

      versions_endscn versions_endtime

      失效時的SCN和時間 如果為空 表示該行被刪除或在查詢范圍內無改動

      versions_xid

      事務ID

      versions_operation

      該行被執行的操作 I(insert) D(delete) U(update)

      minvalue maxvalue

      版本的最大時間值和最小時間值

      注意事項:

      versions子句不能用於查詢以下特殊表:

      · 外部表

      · 臨時表

      · 固定表(x$開頭的表,也就是oracle內部的表,用於數據字典的基表),下面sql語句可查詢相關表

      select * from v$fixed_table

      不能使用versions子句查詢視圖。但是,在視圖定義中可使用versions子句。

      · versions子句不能跨DDL命令使用

      · 過濾掉段收縮操作過的行

      實驗:閃回版本查詢

      TYGER@ORCL>create table tyger as select ename,job,sal from scott.emp where rownum<5;

      Table created.

      TYGER@ORCL>select * from tyger;

      ENAME JOB SAL

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

      SMITH CLERK 857

      ALLEN SALESMAN 1656

      WARD SALESMAN 1306

      JONES MANAGER 3031

      TYGER@ORCL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

      Session altered.

      TYGER@ORCL>select sysdate from dual;

      SYSDATE

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

      2014-03-14 14:41:46

      TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';

      1 row updated.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';

      1 row updated.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>update tyger set sal=sal+100 where ename='SMITH';

      1 row updated.

      TYGER@ORCL>commit;

      Commit complete.

      TYGER@ORCL>select * from tyger;

      ENAME JOB SAL

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

      SMITH CLERK 1157

      ALLEN SALESMAN 1656

      WARD SALESMAN 1306

      JONES MANAGER 3031

      TYGER@ORCL>col starttime for a30

      TYGER@ORCL>l

      1 select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,

      2 versions_xid,ename,job,sal

      3 from tyger versions between timestamp to_date('2014-03-14 14:41:46','yyyy-mm-dd hh24:mi:ss')

      4* and sysdate where ename='SMITH'

      TYGER@ORCL>/

      STARTTIME VERSIONS_XID ENAME JOB SAL

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

      2014-03-14 14:42:32 080016000F020000 SMITH CLERK 1157

      2014-03-14 14:42:26 01002C00F1010000 SMITH CLERK 1057

      2014-03-14 14:42:17 0600180025020000 SMITH CLERK 957

      SMITH CLERK 857

      或者

      TYGER@ORCL>col versions_starttime for a22

      TYGER@ORCL>col versions_endtime for a22

      TYGER@ORCL>l

      1 select versions_starttime,versions_endtime,versions_xid,versions_operation,ename

      2* from tyger versions between timestamp to_timestamp('2014-03-14 14:41:46','yyyy-mm-dd hh24:mi:ss') and maxvalue order by 1

      TYGER@ORCL>/

      VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME

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

      14-MAR-14 02.42.17 PM 14-MAR-14 02.42.26 PM 0600180025020000 U SMITH

      14-MAR-14 02.42.26 PM 14-MAR-14 02.42.32 PM 01002C00F1010000 U SMITH

      14-MAR-14 02.42.32 PM 080016000F020000 U SMITH

      WARD

      ALLEN

      JONES

      14-MAR-14 02.42.17 PM SMITH

      7 rows selected.

      實驗三:閃回事務查詢

      flashback 的事務查詢時通過查詢flashback_transaction_query視圖來實現的

      通過查詢該視圖能夠獲得一些事務執行時的信息,甚至包括UNDO語句。

      每個事務都有事務ID以及SCN關聯關系

      閃回事務處理查詢是一中診斷工具,可以用來查看在事務處理級對數據庫所做的更改。這樣,可診斷數據庫中的問題並對事務處理執行分析和審計。

      可以使用FLASHBACK_TRANSACTION_QUERY視圖來確定所有必要的SQL語句,這些語句可用來還原特定事務處理或特定時間段內所做的修改。

      · 在數據庫中,DDL操作只是對數據字典所做的一系列空間管理操作和更改。通過執行DDL對事務處理執行閃回事務處理查詢時,會顯示對數據字典所做的更改。

      · 當閃回事務處理查詢涉及到已從數據庫中刪除的表時,就不會反映表名稱。而是使用對象編號。

      · 如果閃回了執行事務處理的用戶,則該事務處理的閃回事務處理查詢只顯示相應的用戶ID,而不是用戶名。

      TYGER@ORCL>conn / as sysdba

      Connected.

      SYS@ORCL>

      SYS@ORCL>

      SYS@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';

      UNDO_SQL

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

      update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA';

      SYS@ORCL>grant select any transaction to tyger;

      Grant succeeded.

      SYS@ORCL>conn tyger/tyger

      Connected.

      TYGER@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';

      UNDO_SQL

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

      update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA'; //undo語句

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved