[基礎(chǔ)]9i新特性之五閃回查詢

txfy

9i新特性之五閃回查詢
                                               -----txfy
1:基礎(chǔ)理論：
  這是9i中比較有用的一個(gè)新特性,可以不需要用熱備份就能夠恢復(fù)人為誤操作導(dǎo)致的數(shù)據(jù)丟失.前半部分是基礎(chǔ)知識和原理性介紹,后半部分借鑒了gototop老大ncn上的一篇文章,和brucelau在csdn上的一篇文章(在此表示感謝),寫了兩個(gè)實(shí)例,和一些注意事項(xiàng)以及一些探討,希望大家有空也研究研究!有錯誤請指正.
這一新特性允許用戶查看過去某一時(shí)間點(diǎn)數(shù)據(jù)庫的映像視圖。
用戶可以利用系統(tǒng)時(shí)間或者system change number(scn)號來指定這個(gè)只讀視圖。只有在那個(gè)時(shí)間點(diǎn)已經(jīng)提交的事務(wù)才能夠被查到。
這個(gè)特性的可能應(yīng)用有：
      ..自我維護(hù)過程中的修復(fù)
      ..恢復(fù)類似于email的應(yīng)用程序包
      ..用于趨勢分析的決策支持系統(tǒng)。
概述：
     數(shù)據(jù)庫系統(tǒng)保證用戶對數(shù)據(jù)庫提交的所有查詢將得到用戶指定時(shí)間（服務(wù)器端系統(tǒng)時(shí)間）的一致的數(shù)據(jù)庫映像，所有的在這個(gè)時(shí)間點(diǎn)之前提交的事務(wù)是可見的，之后提交的不可見。
     在這個(gè)某一時(shí)間點(diǎn)的數(shù)據(jù)庫映像上可以執(zhí)行應(yīng)用。例如一個(gè)windows的消費(fèi)者應(yīng)用可以增加一個(gè)按鈕允許用戶返回先前的時(shí)間點(diǎn)顯示當(dāng)時(shí)的賬目平衡狀況。以前，應(yīng)用必須存取很多信息。而這個(gè)特性允許訪問過去的信息，甚至當(dāng)這些信息并沒有顯式的指定出來。
     有幾個(gè)原因?qū)е掠脩艨赡苄枰�查詢過去的數(shù)據(jù)。一個(gè)重要的應(yīng)用是自我維護(hù)修理，當(dāng)一些重要的行被意外的從一個(gè)表中被刪除了，用戶希望恢復(fù)它。做這樣一個(gè)修理，用戶可以向后移動到一個(gè)時(shí)間點(diǎn)，查看丟失的行并把他們重新插入現(xiàn)在的表內(nèi)。但是需要注意不要帶來邏輯矛盾。
     另一個(gè)潛在的好處是在處理類似email和voice mail的應(yīng)用包體上。當(dāng)用戶因?yàn)榘村e鍵意外的刪除了一封mail或者聲音信息時(shí)，他們可以通過移回到固定時(shí)間點(diǎn)來恢復(fù)刪除的mail或者信息并將他們重新插入現(xiàn)在的信息箱內(nèi)。
    oracle flashback
      ..oracle flashback 的實(shí)質(zhì)是自動管理undo information。
    ..undo information在系統(tǒng)級的在一個(gè)特定的保持力時(shí)間間隔內(nèi)被保存。
    ..確保在保持力時(shí)間間隔內(nèi)的undo信息對重構(gòu)映像是足夠的。
    ..oracle flashback是在session級生效的。
    ..pl/sql游標(biāo)將在flashback被關(guān)閉前打開，以用來執(zhí)行dml語句。
    ..smon通過表smon_scn_time保證了系統(tǒng)時(shí)間和scn號的對應(yīng)關(guān)系.
oracle flashback overview
   另外一些好處比如決策支持系統(tǒng)，聯(lián)機(jī)分析等不得不執(zhí)行一個(gè)長時(shí)間運(yùn)行的事務(wù)。有時(shí)需要執(zhí)行對過去數(shù)據(jù)的分析和建模，例如遮光劑隨季節(jié)變化需求的變化。
   包dbms_flashback提供了閃回到指定的系統(tǒng)時(shí)間或者scn的功能。一旦flashback在session級被enable,用戶可以執(zhí)行任意查詢或者pl/sql包。所有的對于數(shù)據(jù)庫的查詢都是運(yùn)行在指定時(shí)間或者scn的數(shù)據(jù)庫中。因此，用戶的會話紀(jì)錄了基于指定時(shí)間的數(shù)據(jù)庫映像，當(dāng)一個(gè)會話斷開或者另一個(gè)會話連接上來，閃回則自動關(guān)閉。用戶也可以利用包的disable顯式的關(guān)閉閃回。這個(gè)包可以用在登陸觸發(fā)器里自動使閃回升效，而不需要更改程序代碼。
   在flashback狀態(tài)下打開的pl/sql游標(biāo)可以記錄下當(dāng)游標(biāo)打開時(shí)系統(tǒng)的基于scn或者時(shí)間的閃回狀態(tài)的數(shù)據(jù)。不同的并發(fā)會話或者連接可以將數(shù)據(jù)庫閃回到不同的時(shí)間點(diǎn)或者scn,dml,ddl操作和分布式操作在會話運(yùn)行于flashback狀態(tài)時(shí),將被禁止運(yùn)行，在flashback disable之前打開的pl/sql游標(biāo)能夠被用來執(zhí)行dml.
    當(dāng)我們用基于時(shí)間點(diǎn)的enabling flashback時(shí)，數(shù)據(jù)庫自動選擇一個(gè)在這個(gè)指定時(shí)間之前的5分鐘內(nèi)產(chǎn)生的一個(gè)scn,作為閃回的基點(diǎn)。對于細(xì)粒度閃回用戶可以用scn來enable flashback.
    oracle flashback的相關(guān)操作。
   
   ..DBA必須設(shè)定UNDO保持力足夠大以能夠重構(gòu)需要閃回的數(shù)據(jù)。
   ALTER SYSTEM SET UNDO_RETENTION=<SECONDS>;;
   值為UNDO數(shù)據(jù)保持的秒數(shù)。
   ..包DBMS_FLASHBACK提供了需求接口。
   下面的例子介紹了如何應(yīng)用這個(gè)包。
   call dbms_flashback.enable_at_time('9-nov-01:11:00:00');
   select * from employess;
   call dbms_flashback.disable();
  flashback overview
  flashback view是由undo retention interval來限制的，這個(gè)參數(shù)由數(shù)據(jù)庫管理員指定，沒有最大值限制。
  dbms_flashback提供了如下接口：
    ..enable_at_time:會話級的enable flashback.映像時(shí)間被設(shè)定為最接近指定時(shí)間戳的scn.
      ..enable_at_system_change_number:將數(shù)據(jù)庫閃回到指定的scn號。
    ..get_system_change_number:返回當(dāng)前的scn,利用這個(gè)接口，用戶能夠得到當(dāng)前scn并儲存起來為以后使用。
    ..disable:這個(gè)存儲過程允許我們在整個(gè)會話內(nèi)停止flashback并將你帶回當(dāng)前時(shí)間的數(shù)據(jù)狀態(tài)。
  注意：dbms_flashback.enable存儲過程不可以在有活動事務(wù)的時(shí)候執(zhí)行，并且，這個(gè)包不能用sys身份執(zhí)行。
   下面我們來看一個(gè)應(yīng)用閃回的過程。
     ..在2：10用戶可以從表中讀出某些數(shù)據(jù)。
     ..在2：13某些行被意外刪除。
     ..在2：15用戶啟用閃回，并創(chuàng)建視圖來訪問2：10的時(shí)候的數(shù)據(jù)。smon進(jìn)程保存了一個(gè)轉(zhuǎn)化表，并將這個(gè)指定時(shí)間轉(zhuǎn)換為相應(yīng)的scn.一個(gè)pl/sql游標(biāo)被創(chuàng)建，它可以連接當(dāng)前表和閃回的視圖，找出丟失的行。
     ..在2：17，用戶使用disable關(guān)閉閃回，然后利用游標(biāo)內(nèi)的數(shù)據(jù)重新將丟失數(shù)據(jù)插回表內(nèi)。
  注意：閃回技術(shù)只能是閃回dml操作，而不能閃回ddl操作，例如drop,truncate都不能被閃回，這是因?yàn)閞ollback segments不能存儲足夠的信息來重建以前的object.另外閃回也不能應(yīng)用于pl/sql包，存儲過程，或者函數(shù)。

2、使用閃回查詢查詢誤刪除數(shù)據(jù)。

SQL>; create user flashback identified by flashback;

用戶已創(chuàng)建

SQL>; grant connect, resource to flashback;

授權(quán)成功。

SQL>; grant execute on dbms_flashback to flashback;

授權(quán)成功。

SQL>; conn
請輸入用戶名:  flashback/flashback
已連接。
SQL>; set echo on
SQL>; create table t(a date);

表已創(chuàng)建。

15:40:18 SQL>; select * from t;

未選定行

15:40:22 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

15:40:35 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

15:40:42 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

15:40:46 SQL>; commit;

提交完成。

15:40:59 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 03:40:35
2004-03-22 03:40:42
2004-03-22 03:40:46

15:41:06 SQL>;
15:42:06 SQL>;
15:45:37 SQL>;
15:54:18 SQL>; delete from t where rownum<2;

已刪除 1 行。

15:54:41 SQL>; commit;

提交完成。

15:54:42 SQL>; select * from t;

A
----------
22-3月 -04
22-3月 -04


15:55:55 SQL>; execute DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 15:50:00
','yyyy-mm-dd hh24:mi:ss'));

PL/SQL 過程已成功完成。

15:56:32 SQL>; select * from t;

A
----------
22-3月 -04
22-3月 -04
22-3月 -04

15:56:38 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 03:40:35
2004-03-22 03:40:42
2004-03-22 03:40:46

15:56:45 SQL>;
3、使用閃回查詢恢復(fù)誤刪數(shù)據(jù)

16:18:57 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

16:19:05 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

16:19:08 SQL>; commit;

提交完成。

16:19:10 SQL>;
16:20:58 SQL>;
16:24:01 SQL>;
16:25:39 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

16:25:44 SQL>; insert into t select sysdate from dual;

已創(chuàng)建 1 行。

16:25:45 SQL>; commit;

提交完成。

16:25:47 SQL>; select to_char(a,'yyyy-mm-dd hh:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 04:25:45
2004-03-22 04:19:05
2004-03-22 04:19:08
2004-03-22 04:25:44

16:26:05 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 16:25:45
2004-03-22 16:19:05
2004-03-22 16:19:08
2004-03-22 16:25:44

16:26:15 SQL>; delete from t where rownum<4;

已刪除3行。

16:26:38 SQL>; commit;

提交完成。

16:26:40 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 16:25:44

16:26:42 SQL>; declare
16:27:36   2  cursor flash_recover is
16:27:36   3  select * from t;
16:27:36   4  t_recode t%rowtype;
16:27:36   5  begin
16:27:36   6  DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 16:26:05','yyyy
mm-dd hh24:mi:ss'));
16:27:36   7  open FLASH_RECOVER;
16:27:36   8  DBMS_FLASHBACK.DISABLE;
16:27:36   9  loop
16:27:36  10  FETCH FLASH_RECOVER INTO t_recode;
16:27:36  11  EXIT WHEN FLASH_RECOVER%NOTFOUND;
16:27:36  12  insert into t values (t_recode.a);
16:27:36  13  end loop;
16:27:36  14  CLOSE FLASH_RECOVER;
16:27:36  15  commit;
16:27:36  16  end;
16:27:36  17  /

PL/SQL 過程已成功完成。

16:27:37 SQL>; select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t;

TO_CHAR(A,'YYYY-MM-
-------------------
2004-03-22 16:19:05
2004-03-22 16:19:08
2004-03-22 16:25:44

16:27:48 SQL>;

我們可以看到利用游標(biāo)我們可以將丟失的數(shù)據(jù)找回來，但是我們同時(shí)也發(fā)現(xiàn)，在找回?cái)?shù)據(jù)的時(shí)候我們丟失了一條數(shù)據(jù)，這個(gè)就是我們前面介紹的在應(yīng)用基于時(shí)間點(diǎn)的閃回的時(shí)候，oracle數(shù)據(jù)庫自動選擇一個(gè)在這個(gè)指定時(shí)間之前的5分鐘內(nèi)產(chǎn)生的一個(gè)scn,作為閃回的基點(diǎn)。在我上面操作的例子中，由于我在第二次提交后兩條數(shù)據(jù)和刪除之間的時(shí)間間隔太短，而這里面的scn和系統(tǒng)時(shí)間對照表5分鐘刷新一次，距離我閃回的時(shí)間最近的scn不是最后一次提交的時(shí)間，而是插入前兩條記錄后提交的時(shí)間，導(dǎo)致后面插入的所有數(shù)據(jù)無法閃回，所以如果發(fā)生插入紀(jì)錄和刪除記錄時(shí)間間隔太短，中間恰好沒有scn記錄時(shí)，這些數(shù)據(jù)將無法恢復(fù)。

那么下面我們來看看記錄scn和time對應(yīng)關(guān)系的標(biāo)smon_scn_time
這是一個(gè)sys用戶的表。
16:46:37 SQL>; select count(*) from sys.smon_scn_time;

  COUNT(*)
----------
      1440
可以看出表內(nèi)共1440條記錄。
16:47:17 SQL>; select to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_
time where rownum<10;

TO_CHAR(TIME_DP,'YY
-------------------
2004-03-19 16:24:51
2004-03-19 16:29:58
2004-03-19 16:35:05
2004-03-19 16:40:12
2004-03-19 16:45:20
2004-03-19 16:50:27
2004-03-19 16:55:39
2004-03-19 17:00:47
2004-03-19 17:05:54
從這里我們看出確實(shí)是每5分鐘記錄一次對照，當(dāng)我們指定時(shí)間后，smon利用這個(gè)表查找前面的最近的scn號。所以精確恢復(fù)紀(jì)錄將比較困難，不過寫到這我忽然想到，如果查詢兩個(gè)時(shí)間點(diǎn)的scn然后取中間的莫個(gè)值，多試幾次，應(yīng)該可以做到精確恢復(fù)所有記錄，呵呵推翻了上面的不可以精確恢復(fù)的論斷，由于時(shí)間關(guān)系，這個(gè)測試暫時(shí)不做，大家有興趣可以試試。那么這個(gè)表是怎么保存記錄的呢？經(jīng)過研究，這個(gè)表每過5分鐘自動刪除最舊紀(jì)錄，然后加入新紀(jì)錄，記錄總數(shù)一直
保持1440，也就是5天時(shí)間，所以如果我們通過ENABLE_AT_TIME是無法恢復(fù)5天前的數(shù)據(jù)的，這個(gè)時(shí)候就必須用scn.但是我們平時(shí)是不會紀(jì)錄scn號的那么碰到這種情況怎么辦呢，我以前寫過一篇關(guān)于logminer的文章，利用logmnr包，我們可以做到這一點(diǎn)。
幾點(diǎn)需要注意的：
1：在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必須設(shè)定你的NLS_DATE_FORMAT的精確程度，Oracle默認(rèn)的是精確到天，如果你不設(shè)定，像上面的例子你不會得到預(yù)期結(jié)果。
2：看下面的例子，當(dāng)我們flashback時(shí)我們發(fā)現(xiàn)sysdate函數(shù)返回的時(shí)間并沒有閃回
16:56:05 SQL>; exec DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2004-03-22 16:26:05','
yyyy-mm-dd hh24:mi:ss'));

PL/SQL 過程已成功完成
3：閃回查詢無法恢復(fù)到表結(jié)構(gòu)改變之前，因?yàn)殚W回查詢使用的當(dāng)前的數(shù)據(jù)字典。
4：你只能在事務(wù)開始時(shí)進(jìn)入閃回查詢模式，如果之前有DML操作，則必須COMMIT。