第08章Oracle閃回_回收站管理.ppt

1、數(shù)據(jù)庫(kù)操作與管理語(yǔ)言O(shè)racle SQL 閃回,目標(biāo),閃回的基本概念 閃回表 閃回刪除 閃回版本查詢 閃回事務(wù)查詢,閃回的基本概念,閃回表 錯(cuò)誤的執(zhí)行了insert,update,delete且執(zhí)行了commit語(yǔ)句。 閃回刪除 錯(cuò)誤的執(zhí)行了drop語(yǔ)句。 主要是操作oracle的回收站recyclebin。 閃回版本查詢 獲取表上所有已提交過(guò)的事務(wù)歷史記錄。 閃回事務(wù)查詢 撤消事務(wù)。 其他閃回 閃回?cái)?shù)據(jù)庫(kù)。-要求數(shù)據(jù)庫(kù)為歸檔模式。默認(rèn)為非歸檔模式。 必須要停止數(shù)據(jù)庫(kù)。 閃回歸檔日志。,閃回(Flashback),在Oracle的操作工程中，會(huì)不可避免地出現(xiàn)操作失誤或者用戶失誤，例如不小心刪除

2、了一個(gè)表等，這些失誤和錯(cuò)誤可能會(huì)造成重要數(shù)據(jù)的丟失，最終導(dǎo)致Oracle數(shù)據(jù)庫(kù)停止 在傳統(tǒng)意義上，當(dāng)發(fā)生數(shù)據(jù)丟失、數(shù)據(jù)錯(cuò)誤問(wèn)題時(shí)，解決的主要辦法是數(shù)據(jù)的導(dǎo)入導(dǎo)出、備份恢復(fù)技術(shù)，這些方法都需要在發(fā)生錯(cuò)誤前，有一個(gè)正確的備份才能進(jìn)行恢復(fù)。 為了減少這方面的損失，Oracle提供了閃回技術(shù)。有了閃回技術(shù)，就可以實(shí)現(xiàn)數(shù)據(jù)的快速恢復(fù)，而且不需要數(shù)據(jù)備份。,閃回的益處,恢復(fù)中，閃回技術(shù)是革命性的進(jìn)步 傳統(tǒng)的恢復(fù)技術(shù)緩慢: 它是整個(gè)數(shù)據(jù)庫(kù)或者一個(gè)文件恢復(fù)，不只恢復(fù)損壞的數(shù)據(jù) 在數(shù)據(jù)庫(kù)日志中每個(gè)修改都必須被檢查 閃回速度快: 通過(guò)行和事務(wù)把改變編入索引 僅僅改變了的數(shù)據(jù)會(huì)被恢復(fù) 閃回命令很容易: 沒(méi)有復(fù)雜棘手

3、的多步程序,閃回的類型,閃回表(flashback table) 閃回刪除(flashback drop) 閃回版本查詢(flashback version query) 閃回事務(wù)查詢(flashback transaction query),閃回表,閃回表，實(shí)際上是將表中的數(shù)據(jù)快速恢復(fù)到過(guò)去的一個(gè)是焦點(diǎn)或者系統(tǒng)改變號(hào)SCN(System Change Number)上。實(shí)現(xiàn)表的閃回，需要使用到與撤銷表空間相關(guān)的undo信息，通過(guò)show parameter undo命令可以了解這些信息。 SCN：系統(tǒng)改變號(hào)。 與時(shí)間形成一一對(duì)應(yīng)的關(guān)系。 通過(guò)timestemp_to_scn(sysdate)

4、獲取某一個(gè)時(shí)間的SCN號(hào)。 閃回表是指將表中的數(shù)據(jù)，恢復(fù)到之前的一個(gè)時(shí)間點(diǎn)上。 用戶對(duì)表數(shù)據(jù)的修改操作，都記錄在撤銷表空間中，這為表的閃回提供了數(shù)據(jù)恢復(fù)的基礎(chǔ)。例如，某個(gè)修改操作在提交后被記錄在撤銷表空間中，保留時(shí)間為900秒，用戶可以在這900秒的時(shí)間內(nèi)對(duì)表進(jìn)行閃回操作，從而將表中的數(shù)據(jù)恢復(fù)到修改之前的狀態(tài)。 執(zhí)行表的閃回，需要有flashback any table的權(quán)限 需要使用oracle EE 10g版本或以上的Oracle數(shù)據(jù)庫(kù)。,閃回的時(shí)間參數(shù)：,默認(rèn)情況下，在900秒以內(nèi)的數(shù)據(jù)可以無(wú)條件閃回 可以通過(guò)show parameters undo;查詢時(shí)間參數(shù)。 只有管理員，才可以查

5、看時(shí)間參數(shù)。,修改閃回的時(shí)間參數(shù)：,可以通過(guò)修改閃回的時(shí)間限制，修改時(shí)間限制。但性有可能會(huì)因此下降。 修改閃回的時(shí)間參數(shù)為20分鐘=20*60=1200秒 alter system set undo_retention=1200 scope=both; scope是指作用的范圍，取值：memory(當(dāng)前實(shí)例有效),spfile(修改配置文件，后面的實(shí)例有效) both(兩個(gè)同時(shí)有效) 查詢閃回時(shí)間 show parameters undo;,給用戶授權(quán)閃回操作：,默認(rèn)情況下，scott沒(méi)有閃回的權(quán)限。 Grant flashback any table to scott;,注意：閃回表需要將表

6、的行移動(dòng)功能打開 alter table enable row movement;,閃回表語(yǔ)法：,FLASHBACK TABLE schema. TO BEFORE DROP RENAME TO table SCN|TIMESTAMPexpr ENABLE|DISABLETRIGGERS schema：模式名，一般為用戶名。 TO TIMESTAMP：系統(tǒng)郵戳，包含年、月、日、時(shí)、分、秒。 TO SCN：系統(tǒng)更改號(hào)， ENABLE TRIGGERS：表示觸發(fā)器恢復(fù)以后為enable狀態(tài)，而默認(rèn)為disable狀態(tài)。 TO BEFORE DROP：表示恢復(fù)到刪除（drop）之前。 RENAME

7、TO table：表示更換表名。,閃回表舉例：,通過(guò)timestamp閃回表 flashback table flash_test to timestamp to_date(2011-03-23 18:27:40,yyyy-mm-dd hh24:mi:ss); 通過(guò)SCN號(hào)閃回表 即可以閃回刪除的數(shù)據(jù)，也可以通過(guò)閃回刪除插入的新的數(shù)據(jù)。即 flashback table flash_test to scn 868741; 必須要使用Oracle EE版本。 以下是刪除表時(shí)用的閃回,即drop table flashback table tab_test to before drop;,示例:,

8、根據(jù)時(shí)間戳閃回： flashback table b to timestamp to_timestamp(2013-03-26 10:42:01,yyyy-mm-dd hh24:mi:ss); 根據(jù)SCN號(hào)閃回： Select timestamp_to_scn(sysdate) from dua; flashback table b to scn 998723;,閃回表: 需要考慮的事情,FLASHBACK TABLE命令作為單一的事務(wù)執(zhí)行，會(huì)得到一個(gè)單一的DML鎖 表的統(tǒng)計(jì)數(shù)據(jù)不會(huì)被閃回 當(dāng)前的索引和從屬的對(duì)象會(huì)被維持 閃回表操作: 系統(tǒng)表不能被閃回 不能跨越DDL操作 根據(jù)版本不同，有所變

9、化，11g已經(jīng)提升此功能。 會(huì)被寫入警告日志 產(chǎn)生撤銷和重做的數(shù)據(jù),閃回表: 需要考慮的事情,閃回表數(shù)據(jù),閃回刪除,閃回刪除，實(shí)際上從系統(tǒng)的回收站中將已刪除的對(duì)象，恢復(fù)到刪除之前的狀態(tài)。 系統(tǒng)的回收站只對(duì)普通用戶有作用。 管理員只做管理。 建議不使用管理員做普通操作。 應(yīng)該創(chuàng)建一個(gè)新的用戶進(jìn)行業(yè)務(wù)等的操作。,閃回刪除：回收站（recyclebin）簡(jiǎn)介,回收站是所有被刪除對(duì)象及其相依對(duì)象的邏輯存儲(chǔ)容器，例如當(dāng)一個(gè)表被刪除(drop)時(shí)，該表及其相依對(duì)象并不會(huì)馬上被數(shù)據(jù)庫(kù)徹底刪除，而是被保存到回收站中。 Show recyclebin或是select * from recyclebin; use

10、r_recyclebin 回收站將用戶執(zhí)行的drop操作記錄在一個(gè)系統(tǒng)表中，也就是將被刪除的對(duì)象寫到一個(gè)數(shù)據(jù)字典中。如果確定不再需要該對(duì)象，可以使用purge命令對(duì)回收站進(jìn)行清空。 SQL purge recyclebin; 被刪除的對(duì)象的名字可能是相同的，例如用戶創(chuàng)建了一個(gè)test表，使用drop命令刪除該表后，又創(chuàng)建了一個(gè)test表，這時(shí)，如果再次刪除該表就會(huì)導(dǎo)致向回收站中添加了兩個(gè)相同的表。,閃回刪除：回收站中對(duì)象的命名規(guī)則,為了確保添加到回收站中的對(duì)象的名稱都是唯一的，系統(tǒng)會(huì)對(duì)這些保存到回收站中的對(duì)象進(jìn)行重命名，重命名的格式如下： BIN$globalUID$version 其中： B

11、IN表示RECYCLEBIN；globalUID是一個(gè)全局唯一的、24個(gè)字長(zhǎng)的對(duì)象，該標(biāo)識(shí)與原對(duì)象名沒(méi)有任何關(guān)系；version指數(shù)據(jù)庫(kù)分配的版本號(hào)。,閃回刪除舉例：,閃回使用drop命令刪除的表 使用表名閃回 使用recyclebin name閃回 閃回b表 flashback table b to before drop; 閃回最近一個(gè)被刪除的表,如果存在同名 flashback table c to before drop; 使用雙引號(hào),通過(guò)recyclebin name閃回 flashback table BIN$nf03/ZogReKWpCqRbYS2OA=$0 to before

12、drop rename to cc; 閃回重名的表 To rename newTable;,閃回 刪除: 需要考慮的事情,閃回刪除對(duì)下列表無(wú)效: 在SYSTEM 表空間內(nèi)的表 用精細(xì)審計(jì)的數(shù)據(jù)庫(kù)或 虛擬的私人數(shù)據(jù)庫(kù) 屬于字典管理的表空間 由于空間不足已經(jīng)被手動(dòng)或自動(dòng)刪除的表 以下依賴不被保護(hù): 位圖索引 表之前刪掉的索引,閃回版本查詢,閃回版本查詢，提供了一個(gè)審計(jì)行改變的查詢功能，通過(guò)它可以查找到所有已經(jīng)提交了的行記錄。其語(yǔ)法格式如下： select column_name,column_name,. from table_name versions between SCN|TIMESTAMP

13、 expr|MINVALUE and epxr|MAXVALUE as of SCN|TIMESTAMP expr; 其中：column_name列名；table_name表名；between.and時(shí)間段；SCN系統(tǒng)改變號(hào)；TIMESTAMP時(shí)間戳；AS OF表示恢復(fù)單個(gè)版本;MAXVALUE最大值；MINVALUE最小值；expr指定一個(gè)值或者表達(dá)式。,Versions between版本查詢,功能更加強(qiáng)大，通過(guò)versions between能夠查看指定時(shí)間段內(nèi)undo表空間中記錄的不同版本（注意，只包括被提交的記錄）。 版本查詢過(guò)程中提供了多個(gè)偽列如下： VERSIONS_STARTS

14、CN，VERSIONS_STARTTIME該記錄操作時(shí)的scn或時(shí)間，如果為空，表示該行記錄是在查詢范圍外創(chuàng)建的 VERSIONS_ENDSCN，VERSIONS_ENDTIME該記錄失效時(shí)的scn或時(shí)間，如果為空，說(shuō)明記錄當(dāng)前時(shí)間在當(dāng)前表內(nèi)存在，或者已經(jīng)被刪除了，可以配合著VERSIONS_OPERATION列來(lái)看，如果VERSIONS_OPERATION列值為D，說(shuō)明該列已被刪除，如果該列為空，則說(shuō)明記錄在這段時(shí)間無(wú)操作。 VERSIONS_XID該操作的事務(wù)ID VERSIONS_OPERATION對(duì)該行執(zhí)行的操作：I表示insert，D表示delete，U表示update。提示：對(duì)于索

15、引鍵的update操作，版本查詢可能會(huì)將其識(shí)別成兩個(gè)操作：DELETE和INSERT。,閃回版本查詢舉例,創(chuàng)建一張新表： create table version_table(vid int, content varchar(20); 執(zhí)行如下操作： insert into version_table values(1,aaa); commit; insert into version_table values(2,bbb); commit; update version_table set content=bbb1 where vid=2; commit; insert into versi

16、on_table values(3,ccc); commit; 執(zhí)行閃回版本查詢。,查詢commit時(shí)間版本：,select id,name, versions_starttime,versions_endtime, versions_startscn,versions_endscn, versions_operation, versions_xid from stud versions between timestamp minvalue and maxvalue;,閃回版本查詢：,查詢這個(gè)時(shí)間點(diǎn)前的數(shù)據(jù)：,As of 表示查詢單個(gè)版本。,閃回事務(wù)查詢,閃回事務(wù)查詢實(shí)際上閃回版本查詢的一個(gè)擴(kuò)

17、充，通過(guò)它可以審計(jì)某個(gè)事務(wù)甚至撤銷一個(gè)已經(jīng)提交的事務(wù)。 實(shí)現(xiàn)閃回事務(wù)查詢，需要先了解flashback_transaction_query視圖，從該視圖中可以獲取事務(wù)的歷史操作記錄以及撤銷語(yǔ)句(UNDO_SQL)。 使用閃回事務(wù)查詢，可以了解某個(gè)表的歷史操作記錄，這個(gè)操作記錄對(duì)應(yīng)一個(gè)撤銷SQL語(yǔ)句，如果想要撤銷這個(gè)操作，就可以執(zhí)行這個(gè)SQL語(yǔ)句。,Flashback_transaction_query,名稱 類型 說(shuō)明 - XID RAW(8) 事務(wù)編號(hào) START_SCN NUMBER 事務(wù)的開始的系統(tǒng)改變號(hào) START_TIMESTAMP DATE 事務(wù)的開始時(shí)間 COMMIT_SCN N

18、UMBER 事務(wù)提交時(shí)的系統(tǒng)改變號(hào) COMMIT_TIMESTAMP DATE 事務(wù)提交時(shí)的時(shí)間 LOGON_USER VARCHAR2(30) 對(duì)應(yīng)的用戶 UNDO_CHANGE# NUMBER 撤銷操作對(duì)應(yīng)的編號(hào) OPERATION VARCHAR2(32) 操作 TABLE_NAME VARCHAR2(256) 表 TABLE_OWNER VARCHAR2(32) 所有者 ROW_ID VARCHAR2(19) 行號(hào) UNDO_SQL VARCHAR2(4000) 撤銷事務(wù)的SQL語(yǔ)句,閃回事務(wù)查詢,注意，要查詢flashback_transaction_query視圖的信息，需要有select any transaction的權(quán)限。,閃回事務(wù)查詢舉例,從閃回版本查詢中，獲取xid號(hào)。 基于xid號(hào)，在flashback_transaction_query視圖中獲取相應(yīng)事務(wù)的信息。 利用UNDO_SQL撤銷事務(wù)操作。,用PLSQL塊閃回事務(wù)查詢：,set serveroutput on; declare v_sql varchar(50