教你如何成為Oracle 10g OCP - 第十四章 閃回

 

閃回 (flashback) 

1.  閃回主要是針對邏輯錯誤(比如使用者誤刪除了資料，表，管理員誤刪除了使用者等)，而像物理資料塊損壞，online redo損壞等物理錯誤，閃回是不能提供幫助的。

 

2.  需要進行閃回的幾種可能情況：
A. 管理員錯誤刪除了使用者
B. 使用者或管理員Drop Table
C. 使用者Truncate Table
D. 使用者執行了錯誤的指令碼導致多個表資料混亂
E. 使用者誤刪除了表的資料

 

3. 閃回的分類
A. 閃回資料庫(flashback database):  適用於刪除使用者，truncate table, 執行錯誤指令碼導致多表資料混亂(無法透過閃回表方式恢復)
B. 閃回刪除(flashback drop):  如果錯誤drop了table, 我們可以透過閃回刪除來恢復 。
C. 閃回表(flashback table):  如果誤刪除了table中的某些資料，可以透過flashback table回退到歷史上的某個時間點。
D. 閃回版本查詢(flashback version query): 每次事務引起的資料行的變化，每次變化就是一個版本。透過閃回版本查詢可以看到資料行的整個變化過程。
E. 閃回事務查詢(flashback_transaction_query):  利用此檢視可得到哪些事務引起資料變化，其中的undo_sql是撤銷語句。
F. 閃回查詢(flashback query):  顯示歷史的某個時間點上Table中有哪些資料 。

 

 

 

4.  先看看 Oracle9i (flashback query ) 

Oracle9i flashback query :

用Oracle 多版本讀一致的特性透過undo 來提供所需的前映象中的資料。透過這個功能，可以看到歷史資料，甚至用歷史資料來修復誤操作引起的錯誤。可以透過指定時間或者SCN 來檢索需要的資料。

資料庫最好處於Automatic Undo Management 狀態（也有文章說必須，我查詢了oracle文件應用了這個詞Prerequisite，同時在AskTom上我也查詢到相應的解釋，指明最好是自動Undo管理，手動的Undo管理也是可以的，並且有例子）最大可以閃回查詢的時間段由UNDO_RETENTION 初始化引數（單位為秒）指定，參看下面執行命令

SQL> show parameter undo

NAME                              TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

可以透過ALTER SYSTEM SET UNDO_RETENTION =;  來修改引數值
設定了相對大的UNDO_RETENTION， 就必須設定足夠大的UNDO ROLLBACK SEGMENTS

儘管timestamp可以精確到毫秒，可是由於 oracle 每隔5分鐘會將產生的 SCN 對應一個 TIME 做記錄 ，也就是說通常只記錄了SCN,但是每5分鐘會記錄 SCN and TIME ，當採用 timestamp 來做flashback 的時候就有可能產生偏差，5分鐘的來由是在於SYS.SMON_SCN_TIME，所以基於SCN的Flashback Query是最準確的， 為了避免恢復失敗，我們可以先等5分鐘，然後再進行恢復。 還有在Oracle 9i中SCN和對應時間的對映資訊只會保留5天，因此我們無法恢復5天前的資料。如果你想使用閃回查詢來恢復5天前的資料，你必須自己來確定需要恢復的SCN (SCN的確定可以使用Logminer) 。 

eg:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';      --- 定義時間格式，精確到秒

select * from sfis1.LOGIN_LOG_TRACE
as of timestamp to_timestamp('2008-4-17 15:36:00','YYYY-MM-DD HH24:MI:SS')
where  id in ('18479513','18479514','18479515');

SQL> select sal from emp  AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)  where empno=7369;

AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)

 

----------

 

當前SCN: SQL> select dbms_flashback.get_system_change_number fscn from dual; 

SQL> col fscn for 9999999999999999999
SQL> col nscn for 9999999999999999999
SQL> select name, FIRST_CHANGE# fscn, NEXT_CHANGE# nscn, FIRST_TIME from v$archived_log;
...................
NAME                                           FSCN                 NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/arch/oracle/1_52413.dbf              12929941968          12929942881 2005-06-22 14:38:28
/arch/oracle/1_52414.dbf              12929942881          12929943706 2005-06-22 14:38:32
/arch/oracle/1_52415.dbf              12929943706          12929944623 2005-06-22 14:38:35
/arch/oracle/1_52416.dbf              12929944623          12929945392 2005-06-22 14:38:38
/arch/oracle/1_52417.dbf              12929945392          12929945888 2005-06-22 14:38:41
/arch/oracle/1_52418.dbf              12929945888          12929945965 2005-06-22 14:38:44

create table sfis1.LOGIN_LOG_TRACE_recov as select * from sfis1.LOGIN_LOG_TRACE where 1=0;

insert into sfis1.LOGIN_LOG_TRACE_recov  select * from sfis1.LOGIN_LOG_TRACE
as of scn  12929942881  where  id in ('18479513','18479514','18479515');  

 

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

 

SQL> exec dbms_flashback.enable_at_time(to_date('20080110131400','yyyymmddhh24miss')) ， 實現資料庫的回閃。

閃回得到的結果並不是把被刪除的資料直接找了回來，而是在回滾段中找到了被刪除表的一個鏡相，所以表還是被刪除了，但因為鏡相已經找到，可以利用他來進行資料恢復。

 

 

 

5.  Oracle10g 閃回資料庫 

原理： Oracle是透過讀取閃回日誌來進行資料回退動作的。閃回日誌類似於redo log一樣在flashback buffer 記錄資料行改變前的所有列數值(注意redo log是記錄前後的值)，然後再由RVWR程式寫入閃回日誌檔案中(flash_recovery_area目錄下的檔案)，讀取閃回日誌的時候和寫入順序相反(因為閃回需要逐漸恢復到更以前的時間點，而redo log的寫入和應用順序是一樣的)，

條件： 
A. 資料庫要求歸檔模式 ；
B. 配置閃回恢復區(flash recovery area，閃回日誌存放處) . SQL>show parameter db_recovery_file
C. 配置閃回保留時間，預設為1440分鐘，一天。  SQL> show parameter db_flashback_retention_target
D. 啟動或關閉閃回資料庫。 SQL> alter database moutn ;   SQL> alter database flashback on ;   SQL> alter database flashback off ;
   開啟後Oracle會在flash recovery area區域自動建立閃回日誌。 關閉閃回資料庫後，則這個區域的所有閃回日誌都被自動刪除。

操作：
SQL> flashback database to timestamp to_date('2008-10-12 12:26:23','yyyy-mm-dd hh24:mi:ss') ; 
或者 SQL> flashback database to scn 3562415 ; 
閃回結束後，一般應該先以read only開啟庫，確認閃回資料是否正確，如果不正確，可以關閉然後mout,繼續閃回。如果正確了，則需要關閉資料，然後
以resetlog方式開啟資料。

備註：
我們也可以使用備庫進行閃回，read only讀出正確資料後，再將備庫恢復到最新狀態。

注意點：
閃回資料受到時間db_flashback_retention_target 以及空間 flash_recovery_area 的影響, 達到空間大小後Oracle會自動刪除閃回日誌。當前到閃回的時間點

之間如果重建controlfile或 刪除了某個表空間或收縮了某個資料檔案，那麼閃回到那個點會失敗 。

相關檢視： v$flashback_database_log  

 

 

 

5.  Oracle10g 閃回刪除(flashback drop)

Oracle10g之前刪除(drop)一個表，那麼該表就會從資料字典中刪除，10g或之後drop table後預設只是在資料字典裡對被刪除的表進行重新命名，並沒有真的將表

從資料字典中刪除，維護刪除前後對應關係的資料字典表，我們稱為回收站(recycle bin), 表上相關物件，trigger,index也會一併進入回收站。

檢視：  SQL> show recyclebin ;  或檢視  dba_recyclebin , user_recyclebin ; 

刪除後的表在回收站中名稱變為類似  BIN$PHI0e0pQESPCL1Aao3EB==$0  的名稱，同樣index也被改名。 他們原來所佔用的空間在物理上並沒有被釋放。
可以透過user_segments 檢視 segment_name = 'BIN$PHI0e0pQESPCL1Aao3EB==$0' ，的確物理上佔用空間，但是邏輯上已經釋放了，併入dba_free_space中了。

操作：
SQL> flashback table A_TEST to before drop ;  表被恢復後，相關index,trigger等需要重建。

SQL> flashback table A_TEST to before drop rename to A_TEST_NEW ;  (如果需要恢復的表名稱已經存在了)

SQL> flashback table "BIN$PHI0e0pQESPCL1Aao3EB==$0"  to before drop ;  (如果同樣的名稱表被多次drop, show recyclebin多個同名表)

 

 

 

6.  10g中drop table 如何回收物理空間

A. 自動回收： 當tablespace存在空間壓力，首先使用非回收站可用空間，然後才是釋放回收站最老的那些物件佔用的空間，知道釋放完回收站所有的空間。
B. 手工回收： purge命令釋放回收站裡面物件佔用的空間
    SQL> purge table A_TEST ; 
    SQL> purge index index_A_TEST;   清除回收站中某個index佔用的空間 
    SQL> purge tablespace users ;    清除回收站中屬於users表空間的物件所佔用的空間
    SQL> purge user_recyclebin ;     清除回收站中屬於當前使用者的所有物件所佔用的空間
    SQL> purge user_recyclebin ;     清除回收站中所有物件佔用的空間    

C. 我們刪除表的時候可以直接見回收站物理空間清理。 SQL> drop table A_TEST purge ;  這樣的刪除，table將沒有辦法透過閃回刪除進行恢復了。

D. drop user tolywang cascade ;    drop tablespace users including contents ;   這些命令物理上刪除，也不會放入回收站中。

 

 

 

7.  10g中閃回表(flashback table) 

閃回表就是將表中的資料回退到歷史上的某個時間點，閃回表利用的是undo表空間中記錄的資料改變前的值。這些undo中的資料保留的時間取決於
undo_retention 的值，如果要回退的資料被其他事務覆蓋的話，就不能恢復到指定的時間了，除非在undo上設定retention guarantee選項。

備註：  由於閃回表操作會修改表中的資料，可能引起資料行的遷移(閃回前後分別在A,B資料塊中)，所以閃回表之前，需要啟動資料行的轉移特性。
SQL> alter table A_TEST enable row movement ; 

閃回表可以從時間上向前，向後任意閃回，只要這些時間段的資料在UNDO中存在。比如：
SQL> flashback table A_TEST to timestamp to_date('2007-10-12 20:35:12','yyyy-mm-dd hh24:mi:ss');  閃回到2007-10-12 20:35:12
SQL> flashback table A_TEST to timestamp to_date('2007-10-12 20:23:42','yyyy-mm-dd hh24:mi:ss');  然後閃回到2007-10-12 20:23:42
SQL> flashback table A_TEST to timestamp to_date('2007-10-12 20:35:12','yyyy-mm-dd hh24:mi:ss');  再次閃回到2007-10-12 20:35:12

注意： 在閃回的兩個時間段A,B之間如果存在對table的DDL操作(Alter)，閃回操作會失敗。SYS下的表不能閃回操作。

 

 

 

8.  10g中閃回版本查詢(flashback version query)

閃回版本查詢不是閃回操作，是檢視引起資料行變化的每次操作(變化情況)。閃回版本查詢也是利用的undo表空間裡面記錄的undo資料。

SQL查詢語法：

select [Pseundocolumns] ...  from ... versions between
{scn | timestamp {expr | minvalue } and {expr | maxvalue} }
[as of {scn | timestamp expr}]
where [Preundocolumns...]....

Pseundocolumns 偽列
A.  versions_starttime 事務開始時的時間
B.  versions_startscn  事務開始時的scn號
C.  versions_endtime   事務結束時的時間
D.  versions_endscn    事務結束時scn號 
E.  versions_xid       事務的ID號
F.  versions_operation   事務進行的操作型別，包括插入(I),刪除(D)和更新(U) .

例子：

select versions_starttime, versions_startscn, versions_endtime, versions_endscn,
versions_xid,  versions_operation from A_TEST  versions between scn minvalues
and maxvalues ;

注意： 閃回版本查詢不支援外部表，臨時表，X$表，檢視。

 

 

 

9.  10g中閃回事務查詢(flashback transaction query)

10g中閃回事務查詢指一個檢視： flashback_transaction_query ,同時也是一個診斷工具。透過查詢可以顯示哪些事務引起了資料的變化。
併為此提供撤銷的SQL語句。 利用的是undo表空間中的undo資料。

SQL> select operation, undo_sql  from flashback_transaction_query where table_name='A_TEST'
and table_owner='TOLYWANG'  order by start_timestamp desc ; 

 

 

 

10.  10g中閃回查詢(flashback query)
同樣，閃回查詢利用的是undo表空間中的undo資料。

語法如下：
SQL> select ... from   as  of  timestamp ...   where .... 
SQL> select ... from   as  of  scn ...   where ....  
以上顯示是在歷史某個時間點上這個table有哪些資料 。

例子：
SQL > create table A_TEST_TMP select * from as of timestamp to_date('2007-10-12 20:23:42','yyyy-mm-dd hh24:mi:ss') ;

 

 

Oracle9i (flashback query )