oracle閃回

筆者根據播布客袁保華影片整理而成

目錄

 

 

 

閃 回

一、概述

資料庫發展的三個主題：安全性、效能更快、管理起來方便。

我們今天講的閃回功能就是為了增強資料庫的安全性的。

對於閃回，利用的回收站、回退段、日誌三種方式來閃回的。

oracle的閃回功能，是資料庫恢復技術的一種革新，也是一種進步。使資料恢復的速度更快。

傳想對於統的不完全恢復來說，傳統的恢復很慢，比如某個表被刪了，傳統的恢復是恢復的整個資料庫，需要複製整個備份的資料，再透過整個日誌和歸檔恢復一下，最後才找到刪除的表。所以說傳統的恢復的時間很長。

 

出現閃回技術後，閃回比傳統的恢復快很多。

閃回恢復可以按照行、物件、事務處理等來做恢復，也就是閃回只恢復遭到破壞的資料，不需要把無關緊要的表也恢復，所以睡會恢復就很快。

閃回記錄的是改變的行、改變的物件、和事務，所以恢復起來就更快。對於沒有遭到破壞的物件、行就不用恢復。閃回恢復的時間等於製作錯誤的時間。

 

傳統的恢復，如果使用者出現錯誤，需要用基於時間點的恢復，其步驟很多，需要當機，如果資料量又很大，那麼恢復時間會非常長，需要DBA掌握的知識比較多。

相反，如果用閃回功能，我們就用幾個閃回的命令，就可以把資料進行恢復了。

 

下面來具體看看oracle 10g以後資料庫閃回功能：

        

 

上圖中可以看出，並不是所有的閃回都是改變資料的，有的只是檢視過去某一刻的資料狀態。當然也有的閃回是改變資料。

 

  二、Flasgbackup database

閃回資料庫利用的是閃回日誌實現的。

資料庫的閃回有些是針對人為的誤操作，有些是針對所有的操作來進行資料的恢復。

資料庫的閃回把所有都閃回到了過去的某一時間點，這和不完全恢復性質差不多。利用資料庫閃回的結果就是錯誤的操作和正確的操作都回到過去的時間點了。所以資料庫閃回用於重大錯誤而產生的非孤立資料的更改。

非孤立資料的更改定義：我做的操作不是影響的一個表，而是一批表，這就是非孤立資料。如果對非孤立資料進行誤操作了，一個表一個表的改是不可能的，所以要用資料庫閃回技術來恢復。

 

使用閃回資料庫功能相當於資料庫出現錯誤時，我們按回退按鈕，使之回退到操作之前，這樣的恢復就不用備份了。

 

閃回資料庫恢復只能恢復邏輯的錯誤，比如人為的把表刪了，批處理很多張表處理錯了，就可以用閃回資料庫進行閃回。但是閃回資料庫不能恢復介質的損壞，比如表空間的損壞(但是表被drop了是可以透過資料庫閃回的，不過這樣閃回的話所有的表的資料都提前了)；閃回資料庫也不能恢復物理的損壞，如磁碟磁頭壞了，就不能用資料庫閃回恢復，而是隻能用備份檔案進行不完全恢復。

 

物理備份恢復的是方法是萬能的，它可以恢復人為和介質的損壞。

但閃回恢復不是萬能的，它只能恢復邏輯的錯誤。

 

物理恢復和資料庫閃回恢復的對比

閃回資料庫只能解決邏輯錯誤，不能解決物理錯誤；另外資料庫閃回也要先備份。

閃回資料庫利用的是閃回日誌，閃回日誌和一般的日誌不一樣，閃回日誌記錄的是事務操作反寫的變化，比如我們執行了一條insert 的sql語句，那麼一般的redo記錄的就是insert 內容，閃回日誌記錄的delete 這條內容。

 

 

閃回資料庫可以減少恢復的時間。

 

閃回日誌需要在作業系統上有儲存空間來為其儲存。

 

 

 

 

Rvwr是一個恢復寫程式，預設是沒開啟的，它的作用是把閃回日誌快取區的日誌寫到閃回日誌檔案裡面。

上述flashbackup logs也是迴圈寫的。

開啟閃回日誌，對資料庫是有開銷的，這個開銷取決於資料庫的負載量。如果資料庫是大量讀操作，開啟閃回功能對資料庫是沒有開銷的。如果資料庫有大量的寫操作，那麼開啟閃回會嚴重影響資料效能，當然我們可以遮蔽某些資料的閃回日誌。

 

 

配置閃回資料庫的三個條件

具體步驟如下：

一、開啟資料庫歸檔

1、配置資料庫歸檔的條件：下面這三個引數在10g和11g可以不用配置，因為資料庫有預設值。預設資料庫啟動時，歸檔程式會自動啟動，歸檔目錄預設在閃回區。

 

 

 

2、啟用資料庫歸檔模式

 

注：改控制檔案（是一個二進位制檔案）是由oracle 的server程式來改的，所有要啟動到mount狀態。

 

注：V$database的資訊是從控制檔案裡面讀取的

 

 

 

二、啟用資料庫的閃回功能

 

先看資料庫的閃回狀態

 

 

三、啟動閃回寫程式fvwr，使之能寫閃回日誌

 

比如下面這張圖，表示的是閃回日誌目錄在閃回區裡面

上述看到資料庫預設設定閃回的時間是一天（1440分鐘）。雖然是閃回一天的資料，但是在一天內如果資料被覆蓋了，想閃回被覆蓋的資料，就閃回不了了，這點要注意。

 

閃回時間越長，那麼就需要更多的空間來儲存閃回日誌。

 

實現資料庫閃回功能的語法

可以在rman或者sql裡兩種方式來閃回，如下：

上述看到可以透過透過timestamp，scn，恢復點三種方式。

Restore point（恢復點）：就是我們再做資料庫更改前，先做一個恢復點，如上述恢復點取得名字叫b4_load。恢復點的名字取得要有意義。然後對資料庫進行操作。假設你做錯了，就可以指定恢復點b4_load就行恢復。

 

 

實現閃回資料庫的步驟

 

上述timestamp的時間可以用to_date函式將數字進行轉化成日期。

上述第四步必須用resetlogs開啟資料庫，原因是資料庫閃回之後，資料檔案已經被閃回到以前的時刻，但是我們的控制檔案、聯機日誌檔案還是失敗時刻的，所有必須用resetlogs把聯機日誌檔案和控制檔案重置到閃回的時刻點上，這樣三大核心檔案（資料檔案、控制檔案、日誌檔案）檢查點一致了，資料庫才能開啟。

注：在上面第四步時，可以先不要急著用resetlogs方式開啟資料庫，可以先用read only方式開啟資料庫，然後去驗證是不是閃回到了所需要的時間點，如果不是，可以回去繼續執行閃回命令，只到達到所需要的時間點為止，再以resetlogs方式開啟資料庫。

 

資料庫閃回實驗

方案：

         用scott的emp表做測試。

conn scott/tiger

假設emp表裡面的員工7369的工資是800，我們準備將其工資改為1000。但是我們在做update時做錯了，將7369以及其他所有人的工資的工資都改成了1200，這時候資料就亂了。需要恢復。如果用物理備份的方法就要用基於時間點的不完全恢復，今天我們用資料庫閃回來實現。如下：

 

記錄一下7369工資為800時候的時間。

      

 

 

記錄時間後，更改7369工資為1000，但卻發出瞭如下錯誤的命令，將所有人的工資都改成1200了

 

 

 

現在用資料庫閃回方法來恢復：

 

 

 

 

以只讀方式開啟資料庫進行驗證是否是所需要的狀態，如果不是在進行閃回：

 

 

看到資料都恢復過來了。

 

現在資料庫是隻讀，只能檢視，不能dml操作。

 

下面在把資料庫改成open read write方式，如下：

 

 

再次驗證資料發現恢復成功了：

 

 

閃回資料庫的管理

下節介紹閃回資料庫的管理

1、首先介紹一下閃回資料庫的限制和因素：

當閃回資料庫後一定要先以read only檢查是否閃回正確，正確後再以resetlog開啟資料庫。另外，閃回操作和不完全恢復是不能同時使用的，只能使用其中一種方法來恢復資料。

2、使用閃回資料庫的限制：

a、資料庫檔案被刪除，不能將資料進行閃回;

b、被離線的資料檔案，不能將資料進行閃回；

c、如果資料檔案空間被reduce size了，那麼只能閃回到資料檔案空間被回收之後的資料，之前的資料閃回不了了；

d、表空間被刪除，不能進行閃回；

e、控制檔案的結構發生變化了，也不能進行資料庫閃回；

f、當恢復區空間不足時，資料庫會自動刪除閃回日誌，這樣即使你設定了閃回日誌保持時間是一天，也可能不能將資料庫閃回到一天之內的時間點。

 

3、閃回資料庫的資料字典

 

 

例子：

上圖可以看出每小時（大概是一個小時）內閃回日誌的大小。

 

 

雖然我們設定了閃回保留時間，但也不能保證在這個保留時間內閃回資料不被覆蓋，閃回空間不夠了就會自動刪除一些閃回日誌，從而使得在閃回保留時間內的資料也不能閃回。因此，我們可以用rman設定備份檔案的保留策略，也可以用crosscheck檢查過期資料，再用delete清除過期備份的資料，從而釋放閃回區的空間，以使閃回日誌不被覆蓋。

 

閃回區的空間使用情況動態效能檢視

 

 

oracle 10g r2新特性——恢復點

在oracle 10 r2之前，只有基於時間和scn的閃回，到了oracle 10g r2之後，又多了一個restore points恢復點的閃回。這個恢復點相當於是scn的別名。把我們資料庫閃回到我們恢復點對應的scn號上。

我們要保證恢復區有足夠的磁碟空間儲存閃回日誌，如果閃回日誌被刪除了，就不能用恢復點閃回了。

上述guarantee關鍵字是保證恢復區裡面的閃回日誌在保留時間內不被刪除，即使閃回區空間不夠了閃回日化也不會刪除。

另外，我們建立好一個恢復點以後，再建立另外一個恢復點後，前一個恢復點就自動刪除，所以只有一個閃回恢復點。

 

使用restore point的要求：

1、10g r2之後；

2、必須開啟閃迴歸檔日誌

3、日誌需要放到閃回恢復區裡面

 

設定閃回恢復點restore point的例子

具體實驗步驟：

1、

 

 

恢復點相當於scn的別名，這樣就不用記scn號和時間戳了

將emp表7369的工資由800改成900，但是改壞了，然後我們用恢復點restore point的方式閃回。

 

上述發現改錯了，下面用恢復點閃回;

 

啟動Mount狀態就是開啟控制檔案的過程。

下面開始閃回：

以只讀的方式開啟資料庫進行驗證

 

發現資料已經閃回了

 

然後再把資料庫以read write的方式開啟

 

 

 

總結：

1、閃回資料庫只能修復邏輯資料錯誤，不能修復物理錯誤。

2、閃回資料庫不能恢復刪除的表空間；

3、資料檔案被reduced size之前的狀態是不能被閃回的，但是reduce size之後的狀態是可以閃回的。

4、當我們重建控制檔案後，控制檔案發生了變化，也不能用閃回資料庫方法閃回資料。

 

 

幾種閃回查詢

閃回查詢：檢視過去時間點的資料，可以看到從前的版本資料長的什麼樣子，類似時間機器。實現原理是利用undo進行恢復的。

閃回版本查詢：看在選定的兩個時間點內行的所有的版本變化和兩個時間點所有事務的變化；

閃回表：可以將某個表的資料閃回到以前的時刻，這樣對整個資料庫的影響比較小；

閃回事務查詢：可以檢視過去的時間點事務執行的情況。

 

以上幾種查詢只是把某個表回到從前，其他表不受影響，這和閃回資料庫查詢相比，對資料庫的資料損失很小。閃回資料庫是把所有表都回到過去的狀態，包括沒有誤操作的表也回到過去了。

 

 

 

1、閃回查詢

實現原理是利用undo。DBA要計算分配undo空間容量，以便有足夠undo空間擴充套件。

那麼DBA怎麼估算undo空間大小呢？

DBA可以先把undo設定為自動擴充套件，然後在業務週期內透過v$undostat觀察undo空間使用情況，最後就可以給undo設定固定大小。

 

 

當前事務未結束的時刻，其他事務可以透過回退段查詢資料，但是當前事務提交了，那麼其他事務就不能從回退段檢視到資料了，如果這時想透過回退段查詢資料，需要利用閃回查詢。

另外， undo_retention是指定undo資料過期時間，如果過期時間沒有達到，即使事務結束了，而且已經提交，已經提交的事務還在回退段，可以隨時被閃回應用；如果undo_retention這個過期時間達到了，那麼新的資料，新的事務就可能覆蓋已經提交的資料，這時就不能利用閃回特稱查詢過期某一時間點的資料了。

另外，undo_retention越大，那麼就需要更大的undo空間。

 

undo_retention預設是15分鐘。假設在15分鐘內，事務提交了，且undo_retention過期時間還沒有達到，那麼新事務資料不會在undo上覆蓋未過期的資料，這樣就可以用undo閃回所需要的資料。如果undo表空間不夠大，undo表空間被用完了，即使undo_retention時間沒有達到，只要事務結束了，系統就有可能覆蓋沒有過期的回退資料，以保證新的事務正常執行。一旦undo資料被覆蓋，就不能利用閃回了。所以我們可以設定retention guarantee屬性，用來指明undo_retentin設定的時間有效。就可以保證oracle undo未過期的資料在retention指定的時間內不會被覆蓋了，就可以使用閃回恢復所需要的資料了。

 

在資料庫中，預設是no guarantee。可以透過dba_tablespaces檢視：

 

 

1、 閃回查詢

上述可以用timestamp或者scn號兩種方法來閃回查詢，

閃回查詢就可以檢視過去時間資料的狀態，但並沒有修改這個資料。

閃回查詢是利用多版本一致性來實現閃回查詢，其多版本資訊是在undo表空間上的。

 

利用閃回查詢恢復資料的例子：

實驗步驟：

emp原始狀態

記錄此時（t1時刻）的scn號：

 

 

修改資料

 

再次記錄此時（t2時刻）的scn號：

 

 

識別錯誤，檢視t1時刻的資料：

 

恢復成t1時刻的資料

 

驗證資料改回來了

 

2、閃回版本查詢

可以查詢一行資料在某個時間段內版本的變化，利用這個功能可以對相應行進行審計。閃回版本查詢實現原理也是透過檢視undo上的資料。和閃回查詢的區別就是閃回版本查詢檢視的是一個時間段內資料版本的變化，而閃回查詢是檢視過去一個時間點的資料。

 

 

上述versions_xid是偽列，他是個事務號，versions_xid是以提交的事務來記錄每次修改資料的版本，每個成功提交的事務都有一個事務號versions_xid。

另外，上圖中version between 後面可以用timestamp和scn來閃回版本查詢。

 

閃回版本查詢+閃回查詢組合：

1、  定位錯誤

利用oracle閃回版本查詢定位這個錯誤是在哪個版本出現的；

2、  恢復到需要的版本

 

閃回版本查詢例子

  實驗方案

        

 

具體實驗步驟：

emp 表原始狀態：

 

檢視當前時間：

下面對資料進行多次修改：

 

 

上述事務經歷了三個版本。

 

下面用閃回版本查詢來看經歷的這些事務版本：

minvale是最早的時間

上述圖中的versions_xid,versions_starttime,versions_endtime都是emp表中的偽列。

 

 

 

然後我們就可以用閃回查詢+DML來進行恢復：

 

 

 

 

使用閃回版本查詢需要注意的事項：

1、  如果是外部表，不能用versions 子查詢進行閃回版本；

2、  對臨時表不能使用versions子句檢視過去某時間的資料版本；

3、  對動態效能檢視也不能使用versions子句查詢

4、  versions子句也不能使用在檢視上

5、  使用versions子句不能跨越DDL操作 。比如我在做ddm後又做了ddl操作，那麼ddl操作之前的不能用versions子句查詢過去事務版本了，但是可以用versions查詢ddl之後的事務版本。

6、  如果我們先對錶做insert 操作，後來發現錯了，又做了delete操作，那麼段中就有就有空洞了，可以用shrink命令回收這些空洞空間。

當我們用shrink命令後，也可以用versions看到過去事務的版本號，但是會看到有些詭異的地方，這就是由於資料庫為了使資料塊中的資料和原來一樣，使資料塊中的資料弄得有點詭異。

 

實驗1：不能在檢視上使用versions子句的例子：

實驗方案：

 

 

 

 

 

記錄一下此時的時間：

 

做修改操作：

 

 

 

 

這時候，事務有了三個版本。

 

下面透過閃回版本查詢：

 

看到閃回版本查詢不能用在檢視上。

 

實驗2：閃回版本查詢不能跨越ddl操作的實驗

實驗方案

 

dml操作

 

ddl（建個約束）操作

 

 

 

 

再做dml操作：

‘

下面做閃回版本查詢：

 

因為我們用ddl語句改變了表的結構了，就不能用閃回事務查詢了。

 

下面我們把閃回版本時間改到ddl操作之後，這樣就能使用閃回版本查詢了：

 

3、 閃回表（flash back table）

功能：

1、  可以把一個表恢復到以前的時刻

2、  和閃回查詢以及閃回版本查詢的區別：如果想恢復一個表的子集的資料，用閃回表恢復很方便。而閃回查詢和閃回版本查詢只對針對一個表的某幾行做恢復，而對一個表的子集做恢復就不好實現了

3、  閃回表只對錯誤的表恢復到以前的時刻，其他表不受影響；

4、  物理的不完全恢復是需要停資料庫的，而閃回表恢復不需要起停資料庫。

5、  基於時間點的物理不完全恢復、閃回資料庫都是把資料庫所有的表都回到以前的狀態了。而閃回表只針對單個表，其他表不受影響

6、  閃回版本查詢和閃回查詢是定位時刻點狀態的，然後結合dml語句做恢復；而閃回表是直接做閃回操作了。

7、  閃回表實現的原理也是使用undo的資料進行閃回。

8、   使用閃回表功能在閃回一個表的同時也會自動維護索引、觸發器、約束，即索引觸發器、約束也跟著表一起閃回。一句話，閃回表會自動維護表上的物件（索引、觸發器、約束）。

9、  一個使用者要有對錶的flashback table、select、insert、delete、alter的許可權，才能對該表使用閃回表的功能

10、  要啟用行移動，才能使用閃回表的功能。因為閃回表會牽涉到塊裡面行的移動。

 

 

啟用行移動的方法：

閃回表的命令：

 

閃回表的使用方法：

 我們可以先用閃回版本查詢，檢視在某個時間段內經歷了哪些事務，然後在用閃回查詢，看某個時刻是否是我們需要閃回需要的資料，如果這個時間點正是我們想要的資料，那就可以在該時間段使用閃回表功能閃回資料了。

 

 

flashback table（閃回表）的限制：

1、  閃回表不能用在系統表上，即不能用在系統sys使用者下或者系統表空間的表。

所以我們再設計業務資料的時候不要把表放在系統使用者下或者系統表空間中。

 

剛才說sys 使用者下的資料是不能用閃回表功能的，但是我們在普通使用者下，即使在普通使用者下把表建立到系統表空間上了，也可以利用閃回表的功能對資料進行閃回，一句話，普通使用者下建立的所有表都可以利用閃回表功能的。

2、  閃回表操作不能跨越DDL，不能閃回到DDL（如alter、truncate）操作之前，只能閃回到DDL之後的操作；

有兩個例外，如果對一個表做了create index的ddl操作，是可以透過閃回表的功能閃回到create index 操作之前的狀態的，並且閃回後新的索引還存在。

同樣，如果在一個表上建立了觸發器，那麼可以透過閃回表功能閃回到建立觸發器（ddl操作）之前的狀態，並且閃回後該觸發器還生效可用。

 

 

使用系統表不能恢復的例子

1、不能使用系統表做閃回的例子

 

conn / as sysdba

create table emp

as select * from scott.emp;

 

SQL> select empno,sal from emp where empno=7369;

 

     EMPNO        SAL

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

      7369        900

 

記錄一下時間：

 

 

然後我們對sys使用者emp 7369的工資改成1000,但是改成了1900

.

 

定位錯誤：可以用閃回版本查詢定位錯誤（這裡略）：

 

然後利用閃回表的功能做恢復：

 

上述可以看到sys使用者下的表是不能用閃回表功能閃回的。

 

下面我們在sys使用者下，在普通表空間上建立一張表，測試是否可以閃回：

 

可見，在sys使用者下的表，無論是建立在系統表空間或者其他表空間，都是不支援閃回表功能的。

 

 

 

下面我們在普通使用者下，把表建立在系統表空間裡面：

 

構造錯誤：

 

利用閃回版本查詢定位錯誤：

minvalue最早的時間，maxvalue最後的時間。

 

用閃回表功能閃回：

 

 

 

 

結論：sys 使用者下的資料是不能用閃回表功能的，但是我們用普通使用者下建立表，即使在普通使用者下把表建立到系統表空間上了，也可以利用閃回表的功能對資料進行閃回。

 

 

2、 閃回表不能跨DDL的例子

 

 

 

 

做個ddl操作

 

在做DML操作

 

 

 

下面閃回到ddl操作之前的狀態，發現閃回不了：

下面測試閃回到ddl操作之後的狀態，發現可以閃回了

 

3         ddl操作是trigger時對閃回表功能的影響—沒有影響

 

看觸發器的資訊：

 

 

 

閃回成功了

 

閃回完成後，觸發器還存在並且可用

 

4、閃回事務查詢

閃回事務查詢功能是定位錯誤的，類似於閃回版本查詢。

今天，我們介紹的閃回事務查詢，可以看到資料過去某一時間段資料的事務的變化，也就是說能看這個事務影響的所有行和表的資料變遷。

閃回事務查詢+dml操作來做恢復。

 

閃回事務查詢和其他的區別：

閃回查詢是查過去一個時間點事務狀態的，閃回版本查詢是檢視過去一個時間段內事務變化的。

閃回表是做恢復的，閃回表可以把一張表上資料恢復到以前狀態，但是和這張表關聯的其他表的資料狀態不能恢復到以前狀態；

閃回版本查詢，能找到一張表裡某些行上資料的變化，但是不能看到和這行資料相關的其他行或表的變化。而閃回事務查詢可以看和這行資料相關的其他行或表的變化

 

 

 

閃回事務查詢需要檢視資料字典flashback_transaction_query。普通使用者需要授權才能檢視該字典，如下：

select * from session_privs;

 

 

 

 

desc flashback_transaction_query;

 

該字典會自動生產undo sql語句進行恢復

 

 

 

閃回事務查詢恢復的實驗：

 

 我們首先建立兩張表，然後對這兩張表修改資料，最後用閃回事務查詢來恢復兩張表關聯的資料。

 

 

修改資料：

 

上面commit後，這個是第一個事務

不要commit

 

上面是第二個事務。

 

現在準備恢復，先用閃回版本查詢看一下這行資料的變遷：

記錄上述的xid，此xid會在閃回事務查詢裡面引用。

再用閃回事務查詢一下：

透過上述語句看到了xid=’’的事務裡面對應的SQL語句

用上述這個事務裡面的相反的SQL語句進行恢復操作：

 

commit

 

驗證恢復到以前的時刻：

50號部門資料也沒有了，說明恢復到了以前的時刻：

 

 

閃回事務查詢的限制：

只能看DML的語句，ddl語句是查不到的；

 

############################

5、閃回事務

在oracle 11g中，對閃回事務查詢增加了新特性，可以把獨立的事務（獨立的事務是指這個事務和其他事務沒有關聯）撤銷掉，就相當於這個事務沒發生過一樣，但閃回事務查詢對有關聯的事務撤銷就很麻煩。不過，閃回事務就很好的解決了這個問題。

 

閃回事務功能：可以撤銷獨立的事務和相互關聯的事務（比如我把dept 表20部門刪除，那麼emp表20部門資料就會受影響，這樣就是相關聯的事務）。

 

閃回事務的要求：

1、  撤銷事務的使用者需要擁有如下許可權：

2、  要開啟附加日誌的功能，這樣才能在日誌中記錄額外的資訊（比如某個表的主鍵資訊的變化，這就是額外資訊）。

3、  閃回事務也會利用undo表空間

透過上述看出，閃回事務很類似oracle的日誌挖掘。

4、  閃回事務呼叫dbms_flashback.transaction_backout這個包裡面的過程來進行事務撤銷，注意如果加nocasecade選項，表示撤銷的是一個獨立的事務，和其他事務沒有關聯，這個事務的撤銷對別的事務的資料沒有影響。

5、  授予普通使用者使用dbms_flashback的許可權：

6、  資料庫必須是11g才能用閃回事務

 

閃回事務的步驟：

1、  透過閃回事務查詢來看這個時間段內經歷了幾個事務

2、  選擇有問題的事務

3、  對有問題的獨立事務進行撤銷

4、  如果是對有關聯的事務進行撤銷，需要加一下引數來實現

5、可以透過dba_flashback_txn_state 和dba_flashback_txn_report這兩個檢視看事務之間的依賴關係。

 

閃回事務實驗

實驗方案：

 

開啟附加日誌功能，為了使附加日誌記錄的內容最少，我們可以只開啟附加日誌記錄主鍵的資訊，如下：

 

授予普通使用者對dbms_flashback包有執行許可權：

 

grant select any transaction to scott;

 

 

 

下面構建兩個事務

 

 

上面是第一個事務

 

 

上面是第二個事務

 

上面的事務1和事務2是沒有關聯的。

 

下面透過閃回事務查詢，看剛才做了哪些事務：

 

 

 

上面透過閃回事務查詢定位到了兩個事務。其實我們可以直接透過上述undo_sql語句進行回退，但是如果語句比較多，就比較麻煩。

 

下面我們透過閃回事務功能回退第二個事務，其實現原理利用的包是dbms_flashback；

 

下面的pl/sql必須在sys使用者下執行，否則會報錯。

 

驗證，看到事務2被回撤了。

 

如果回退的不對，可以用rollback取消剛才的回退，回退正確了再提交commt。

記得commit；

下面回撤事務1

 

 

上面我們做了一個獨立事務的回退

下面我們再做一個透過閃回事務的方法，回退有關聯的事務的一個實驗。

透過閃回事務回退有關聯的事務

實驗方案

 

 

建立事務1：

 

我們知道，dept是主鍵表，emp表是外來鍵表：

建立事務2：

 

 

 

透過閃回事務查詢看在這個時間段內有多少個事務：

 

 

因為事務2（有外來鍵）依賴事務1（有主鍵），那麼我們現在撤銷事務1，會成功嗎：

 

 

 

看到我們撤銷事務1時出錯了，原因是事務2依賴於事務1.

下面我們加個級聯的選項來撤銷，本應該能成功，但是卻失敗了，這個可能是oracle的bug：

 

下面我們再加個選項，只撤銷沒有衝突的，結果也沒有成功：

 

下面我們在加強制執行回退操作，也沒有成功：

 

看來，我們想一起撤銷兩個關聯的事務，無論加什麼引數都是做不到的。

那麼怎麼辦呢？

透過下面的方法來實現。

下面我們先撤銷事務2，再撤銷事務1就能成功，這樣事務2 就不再依賴事務1了：

先撤銷事務2：

記得commit：

 

再撤銷事務1，執行成功了：

 

驗證：

 

commit提交

結論：如果兩個事務有主外來鍵關係，那麼可以先撤銷事務2，再撤銷事務1，一步一步來撤銷。

 

 

 

 

 

 

 

6使用閃回資料歸檔檢視以前對資料做的操作（11g新特性）

閃回資料歸檔功能：可以檢視歷史資料，可以對過去的資料進行查詢。比如我們資料庫有一個重要的表emp，我們想把對emp每次做的修改操作記錄都跟蹤下來，比如跟蹤每個值是什麼時候改的，改的是什麼等。這個需求我們可以用應用程式的方式來實現，我們可以建立一張歷史表，這個歷史表和emp表結構一樣，不過需要對這個歷史表再加個時間的欄位，當我對emp表更新記錄時，我們透過觸發器將emp表的更新變化記錄到歷史表。如果emp表增加了欄位，那麼我們還需要在歷史表再加個欄位，這樣維護很麻煩。另外，歷史表刪除了，那麼觸發器再執行的時候就會出錯。

 

今天我們講的閃回資料歸檔，實現起來就很簡單了。

 

我們以前講的閃回查詢，閃回版本查詢，閃回事務查詢，他們都是依賴於回退段，所以閃回的時間不會太久。

 

而閃回資料歸檔是存放在資料庫的表空間中，只有歸檔不刪除，這樣就可以閃回很久以前的資料了。

 

閃回資料歸檔（11g新特性）：資料庫會自動跟蹤資料庫的變化，並寫入閃迴歸檔區域裡面，這樣我們就可以把資料閃回到任何時刻了（不受時間限制）。

 

如果我們把所有表的變化都放在閃回資料歸檔區，那麼需要的空間是很大的，但資料庫允許我們在表的級別設定，只記錄哪些表的資料歸檔。預設的是資料庫沒有啟用閃回資料歸檔區。

 

我們可以設定閃回資料歸檔區裡的資料保留時間，以釋放空間。

 

如果我們想對原表加一列，那麼閃迴歸檔區為了維護致性，會阻止對原表加入列。

利用閃回資料歸檔功能，可以對資料進行恢復和審計。

使用閃回資料歸檔的方法：

 

 

閃回資料歸檔的程式：

程式名：閃回資料歸檔後臺程式

閃回資料歸檔後臺程式的作用，是把資料庫資料變化寫到閃回資料歸檔區FDA中，這種寫入方式是非同步的，預設是每5分鐘寫一次。這個程式是把表上資料的變化以壓縮的方式寫入閃回資料歸檔區。

另外閃回資料歸檔區是以天進行分割槽的，這樣讓寫入I/O儘量低。

此外，當閃回資料歸檔後臺程式空間不夠時，資料庫是以1M的方式給閃回資料歸檔區來增加空間的。

 

上圖中，當閃回資料歸檔後臺程式發生將表資料dml操作的變化寫到閃回資料歸檔區的時候，該程式會先把data buffer cache裡面的資料變化寫入資料閃回區。當髒資料不在buffer cache，而是寫資料檔案中時，閃回資料歸檔後臺程式就會把undo 裡的data寫入到閃回資料歸檔區。

 

閃回資料歸檔後臺程式也可以監控閃回資料歸檔區的空間，如果閃迴歸檔區裡面有檔案過期了，就會刪除。

 

傳統的歸檔檔案是存放在操作中的，而閃回資料歸檔是存放在普通表空間中的

 

使用閃回資料歸檔的步驟

其實就兩大步:

         第一步：建立閃迴歸檔區的空間

第二步：授權

1、使用者許可權：

grant select any transection to scott；

grant execute on dbms_flashback to scott；

把使用者的錶的歷史資料放在閃回資料歸檔區裡面：flashback archive

alter table emp flashback archive fda1;

2         系統許可權：

flashback archive administrator，這個許可權是建立和關聯閃回資料歸檔區的許可權。

 

閃回資料歸檔維護命令

建立閃迴歸檔區

在一個資料庫中是可以有多個閃回資料歸檔區的。下圖中的default表示的是，當我們在給一個表沒指明閃迴歸檔區時，預設就是default指定的fla1閃回資料歸檔區

 

 

開啟表的閃迴歸檔功能：

下面沒有指名閃回資料歸檔區的名字，就用上面指定預設的fla1。

 

透過閃回資料歸檔檢視歷史資料

也可以使用閃回資料歸檔來看閃回資料版本查詢

 

閃回資料歸檔的實驗

1、  建立存放資料歸檔區的表空間

2、  建立資料閃迴歸檔管理員並授權

下面授予該使用者歸檔管理許可權，該許可權表示可以建立閃回資料歸檔區

 

再給connnet和resource的角色

 

檢視自己有哪些許可權：

connfbda_admin/fbda_admin

 

 

3、  建立一個普通使用者，做為資料歸檔區的使用者

4、  建立閃回資料歸檔區

 

 建立幾個都行

 

 

5、  授權普通使用者可以使用哪個閃回資料區

 

6、  使用：

 

產生事務1：

產生事務2：

 

7、  使用閃回版本查詢，其資料來自undo

 

如果undo被覆蓋，那麼執行上述語句就會報錯。下面我們就一起來測試一下：

 

8、  建立一個undotbs2，,並刪除原來的undotbs1---注意，以下幾步純是測試用的，生產中不要用。

9、 

10、               

 

 

 

 

看到出現了01555錯誤，因為閃回版本查詢的資料來自undo，我們把以前的undotbs1刪除了，所以就報了01555的錯誤。

 

11、              利用閃回資料歸檔來檢視歷史資料

11.1 啟用表emp的閃回資料歸檔功能

啟用該功能，就會把修改emp錶的歷史資料全都放在閃回資料歸檔區裡面。

 

11.2 模擬資料丟失

 

 

事務1：

事務2：

 

11.3再用閃回版本查詢的資料就不是來自undo，而是來自閃回資料歸檔區了。

 

11.4 測試：再次刪除undotbs2，目的是用來確認閃回版本查詢的資料來自閃回資料歸檔區，而不是unotbs2

 

 

 

 

再執行閃回版本查詢，發現資料被查出來了，說明資料來源於閃回資料歸檔區：

 

小知識：

閃回資料歸檔後臺程式會把data buffer cache和undo裡面的資料寫到閃回資料歸檔區裡面。

 

 

閃回資料歸檔的維護

建立閃回資料歸檔區

 

啟用表的閃回資料歸檔功能

上述沒有寫閃回資料歸檔區的名稱，表示用的是預設的閃回資料歸檔區。

 

使用閃回資料歸檔區

閃回查詢

或者閃回版本查詢使用閃回資料歸檔區

對閃回資料歸檔區新增空間

修改閃回資料歸檔保留期限

 

手工刪除閃回資料歸檔區的資料

 

當然清除閃迴歸檔區的資料也可以自動刪除，即根據設定的保留期限來自動閃回。

 

刪除閃回資料歸檔區

檢視閃回資料歸檔區的資料字典

用閃回資料歸檔的管理員登陸：

 

flashback_archive# 表示的是閃回資料歸檔區名字的編號。

 

 

檢視閃回資料歸檔的表空間的資訊

 

conn test/test

上述增加的表就是歷史表。

 

 

emp和 sys_fba_hist_74981表結構幾乎一樣，sys_fba_hist_74981是emp錶的歷史表。當emp表發生變化時，其資料變化就會自動寫入到歷史表sys_fba_hist_74981

 

 

下面這個表user_flashback_archive_tables列出哪些表啟用了閃回資料歸檔功能，以及其對應的歷史表是什麼，這些歷史表都在閃回資料歸檔區裡面：

 

 

 

閃回資料歸檔功能可以跨越DDL操作

我們前面講的基於undo的閃回版本查詢是不能跨DDL操作的，但是我們現在要講的閃回資料歸檔功能是可以跨DDL操作的。

 

 

下面我們就做個實驗，來驗證閃回資料歸檔可以跨越ddl。

對emp表做個DDL操作：

 

跨越ddl操作後，基於閃回資料歸檔的閃回版本查詢還可以用（以前基於undo的閃回版本查詢如果跨越ddl操作會報錯）：

 

在oracle 11g r2中，使用閃回資料歸檔功能，如果對原表增加列，刪除列，列重新命名，列的長度更改了，或者表的一個分割槽truncate了，再或者整個表truncate或改名了，那麼閃回資料歸檔的歷史表也會做相應的操作，從而使原表和歷史表保持一致。但是在11 r2之前，不支援上述功能。

閃回資料歸檔功能也有不支援的ddl操作：drop

這時候可以用dbms_flashback_archive包，來使原表和歷史表取消關聯，這樣就可以對原表做類似drop等的ddl操作。然後在用dbms_flashback_archive把原表和歷史表做關聯。

 

 

下面這張圖很好的詮釋了在一張表刪除一列資料和增加一列資料，那麼歷史表也做相應的操作。

 

 

實驗：

 

啟用表的閃回資料歸檔功能

 

對錶執行一個ddl操作：

執行這個操作後，t錶的歷史表也會自動刪除object_id列。

 

 

desc  sys_fba_hist_75030 ;   --可以看到t錶的歷史表sys_fba_hist_75030也刪除相應的列。

 

下面我們在測試drop 命令

看到t表不能刪除，原因是閃回資料歸檔不支援drop命令

發現歷史表也不能刪除：

 

如果想把一個表的資料分出來，或者把兩個分割槽的資料做一個交換，這時候可以用dbms_flashback_archive包，這個包裡面有些過程，來使原表和歷史表取消關聯，這樣就可以對原表做類似drop等的ddl操作。然後在用dbms_flashback_archive把原表和歷史表做關聯。使用這個表的前提是：基表（原始表如emp）和歷史表必須在同一個使用者下。如下圖：

 

 

 

我們建立一張和歷史表一樣的表t2，用來備份這個歷史表：

 

下面我們解除基表t和歷史表的關聯

 

然後我們再用ddl命令drop來刪除歷史表，這時候就可以刪除了：

看到現在可以刪除了，因為我們解除了基表t和歷史表的關聯了，所以就可以刪除了。

 

現在我們再用dbms_flashback_archive包將基表t和它的歷史表來做關聯，我們發現現在關聯不上了，原因是我們把t錶的歷史表給刪除了。但是我們留了一手，我們在上步給歷史表做了個備份t2。

 

 

我們把歷史表的備份表t2改名成原來歷史表的名字：

 

再次建立基表和歷史表的關聯，看到成功了：

 

現在我們再把歷史表刪除，看到又是不允許的，因為基表和歷史表已經做了關聯，恢復到以前的關聯關係了：

 

 

 

上述過程可以用下圖表示：

 

 

利用閃回資料歸檔，可以利用oracle閃回查詢（查的是過去的時間點）和閃回版本查詢（過去時間段的查詢）來查過去的資料；同時，不管是閃回查詢還是閃回版面查詢，都可以用scn或者時間戳來查詢。

 

有時我們查詢過去的資料時，發現在一秒鐘和兩秒中的資料是一樣的，原因是oracle是以事務為單位進行查詢的，所以時間段內資料是一樣的。

 

閃回資料歸檔功能不是在資料庫級別設定的，而是在表級別設定的。

 

flashback drop and the recycle bin

 

如果不小心把一張表刪除了，可以透過oracle回收站的功能，把刪除的表閃回。

 

 

回收站的功能是否開啟，透過recyclebin=on設定。

oracle的資料字典記錄了回收站的資訊。當我們刪除了一張表，其實並沒有真的開闢一塊空間當做回收站，來把刪除的表放到裡面，而是刪除的表佔用的還是原來的空間。只是把刪除表的資訊記錄在了資料字典(dba_free_space)中，同時把刪除的表名在回收站中重新命名了。

 

回收站空間釋放方法：

1、  自動釋放  當我們把表刪除後，在回收站中會把該表重新命名。但資料字典dba_free_space會把重新命名的當成空閒(free)空間。

oracle會優先使用沒有用過的嶄新的空閒空間，當這樣的嶄新的空閒空間用完後，oracle才會使用回收站中所標記物件的空閒空間的,當回收站的空間也用完了，如果表空間是自動擴充套件的，那麼這個表空間就會autoextend 來擴充套件空間。

2、  手工釋放

用命令purge user_recyclebin(只清除當前使用者自己回收站的資料)或者purge dba_recyclebin（把資料庫中所有的回收站的資訊清除掉），將空間也被釋放。

用如下命令清除回收站中某個表：

用如下命令清除回收站中表空間裡的某個使用者的回收站的資訊：

 

 

用如下命令表示再刪表時，把該表回收站的資訊也刪除，這回表的空間釋放了：

 

在刪除表空間時，加including contents表示把表空間中物件的回收站資訊也清除：

 

刪除使用者時，加cascade，表示把使用者物件回收站的資訊也清除掉：

 

 

下圖表示再做drop操作時，不會放在回收站裡面：

 

 

*.當我們在drop表的時候，表上的索引也同時被drop掉了。

 

flash drop的實驗：

        

 

 

 

 

 

上圖看到，表並沒有真正的刪除，只是rename了一下。

 

可以透過show recyclebin看回收站的內容，也可以透過字典user_recyclebin（用這個能看刪除的索引資訊）來看：

 

 

當我們用閃回技術閃回到drop前的狀態，那麼閃回回來的表上索引的名字，約束的名字、觸發器的名字還會保留回收站中的名字。這時候我們可以透過rename命令進行重新命名。

 

 

 

回收站裡面有多個同名的表——後進先出的原則

假如我們把emp表刪除後放進回收站了，但我們又刪除了一個也叫emp的表，那麼我們在從回收站恢復emp表時，閃回的是哪個表呢？其實利用後進先出的原則來恢復回收站的表的。下面我們做實驗來驗證後進先出的原則

 

實驗：

 

 

此時員工7369工資是800

 

我們再建立一張emp1的表

 

此時7369的工資是900

 

看到回收站中emp1後兩個版本。

 

我們再建立一個emp1的表：

 

 

此時7369的工資是1000

再刪除emp1

這時候我們看到回收站裡面emp1有三個版本，我們再恢復的時候，是按照後進先出的原則來恢復。

我們的emp1第一個版本員工7369工資是800，第二個版本7369工資是900，第三個版本是1000；

 

 

 

看到恢復的資料版本是第三個版本，7369的工資是1000時的emp1表。從而證明了後進先出的原則。

 

 

清除回收站，其表佔據的空間也被清空了。

 

如果是dba，可以purge dba_recyclebin；這樣清空的是資料庫所有使用者的的回收站。

清空後，看到select * from tab顯示的乾淨了，不再有

  這樣的表了。

 

 

從回收站閃回的表名和scott使用者下表名衝突了，怎麼辦----rename

我們可以這樣做個試驗：在回收站中的emp1表的7369工資是1000，而scott使用者下的emp1表emp1 7369工資是800，在這種情況下,從回收站進行閃回，會怎麼樣呢？

上面閃回看到報錯了。

 

可以透過rename來解決:

檢視回收站中的資訊

 

完。