[基礎(chǔ)]9i新特性之五閃回查詢
2024-07-21 02:33:11
供稿：網(wǎng)友
9i新特性之五閃回查詢
-----txfy
這是9i中比較有用的一個(gè)新特性,可以不需要用熱備份就能夠恢復(fù)人為誤操作導(dǎo)致的數(shù)據(jù)丟失.前半部分是基礎(chǔ)知識和原理性介紹,后半部分借鑒了gototop老大ncn上的一篇文章,和brUCelau在csdn上的一篇文章(在此表示感謝),寫了兩個(gè)實(shí)例,和一些注重事項(xiàng)以及一些探討,希望大家有空也研究研究!有錯(cuò)誤請指正.
這一新特性答應(yī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è)按鈕答應(yīng)用戶返回先前的時(shí)間點(diǎn)顯示當(dāng)時(shí)的賬目平衡狀況。以前，應(yīng)用必須存取很多信息。而這個(gè)特性答應(yīng)訪問過去的信息，甚至當(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(cuò)鍵意外的刪除了一封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è)存儲過程答應(yīng)我們在整個(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。
