Oracle 11g閃回數據歸檔新功能解析

簡介

　　Oracle數據庫10g提供了五個新的閃回功能：閃回版本查詢，閃回事務查詢，閃回刪除，閃回表和閃回數據庫。Oracle數據庫11gR1提供了一個有趣的新的閃回功能：閃回數據存檔，它允許一個Or acle數據庫管理員維護一個記錄，對指定時間范圍內對所有表的的改變情況進行記錄。

　　許多年前-事實上，在上個世紀-我是一名美國大西部飲料生產廠家人力資源系統的承包商。有一天，人力資源總監問我：“如何為我們的雇員數據配置一個delta表，使我們能夠跟蹤所有對雇員數據的更改情況?”出現在我腦袋中的答案就像最近的美國總統大選期間CNN用來顯示和分析投票結果的“神奇地圖”一樣：

　　 我想創建一個名為EMPLOYEES_HISTORY的表，除了增加了一個相應的時間戳記的日期和時間的變化外，它與Employees表的列完全一樣。

　　 我希望把這個新表放在一個單獨的表空間。

　　 我想套用外鍵參照完整性約束EMPLOYEES_HISTORY表，以確保兩個表之間的數據一致。

　　 最後，我想創建一個AFTER ... FOR EACH ROW觸發器，無論何時在Employees表上發生INSERT，UPDATE或DELETE事務時，都將在EMPLOYEES_HISTORY表中插入delta記錄。

　　然後一些潛在的問題開始萦繞著我，這樣做了會發生什麼，我不停地問我自己，如果某人：

　　 禁用了EMPLOYEES_HISTORY表上的觸發器，並且忘記重新開啟了，會發生什麼事情?

　　 需要向EMPLOYEES表中增加新列，或修改數據類型，但忘記同步修改EMPLOYEES_HISTORY表上對應的列了，會發生什麼?

　　 雖然同時對EMPLOYEES和EMPLOYEES_HISTORY表做了一致的修改，但忘記修改觸發器了，會發生什麼?

　　 如果需要刪除EMPLOYEES表的列或將其標記為UNUSED時，會發生什麼?

　　 意外TRUNCATE掉EMPLOYEES_HISTORY表時會發生什麼?

　　 意外刪除了EMPLOYEES_HISTORY表時會發生什麼?甚至更糟糕的是刪除了EMPLOYEES表時會發生什麼?

　　這些問題的簡單答案是，如果真發生了這些事情，我不得不向客戶解釋為什麼弄丟了EMPLOYEES_HISTORY表中的delta數據。

　　用閃回數據歸檔實現Oracle“完全撤銷”

　　如果我擁有Oracle 11g，我會直接告訴客戶我花幾分鐘就能恢復數據，Oracle 11gR1新的閃回數據歸檔(FBDA)功能創建一個獨立的倉庫保留表中數據改變的歷史記錄。

　　FBDA信息存儲在一套獨立的對象中，跟蹤目標表的事務歷史，這些對象存儲在一個或多個表空間中，FBDA表的名稱是由系統自動產生的，可以通過視圖DBA_FlashBACK_ARCHIVE_TABLES查看，一旦在表上開啟了FBDA，所有保留的事務歷史都可以查看，這樣就不用再為關鍵事務表創建對應的歷史跟蹤表了，那樣做很麻煩，因為創建歷史跟蹤表後，還有編寫復雜的觸發器，確保歷史數據被精確跟蹤。

　　歷史信息的捕捉也是非常高效的，因為Oracle 11g專門修改了內核，使保留歷史數據的性能開銷減到最小，此外，這些歷史數據以壓縮格式存儲，減少了存儲的需要，另外，只要在表上開啟了FBDA，Oracle 11g就不會再對歷史數據做使其無效的操作(如刪除或清空目標表)或阻止捕捉(如刪除目標表的列)。

　　下面解釋一下FBDA是如何工作的：

　　 閃回數據歸檔(FBDA)是一個新的後台進程，負責跟蹤和歸檔開啟了FBDA的表的歷史數據，它通過異步處理自動收集和向指定的閃回數據歸檔寫入原始數據。

　　 當開啟FBDA的表中任何數據發生變化時，FBDA首先詢問存儲在數據庫緩沖區中的UNDO數據，如果數據仍然在那裡，FBDA就會使用它，如果UNDO數據已經從數據庫緩沖區中移除，FBDA就會嘗試從UNDO表空間中的UNDO段中獲取數據發生的變化。

　　 當FBDA捕捉到變化數據時，它會整理開啟FBDA的表中的行，然後將這些行寫入FBDA中的歷史表，這些表數據經過壓縮，就和內部分區一樣。

　　有趣的是，FBDA不包括捕獲數據的原始索引，當在FBDA中可以對歷史數據創建另一個索引。

　　自動保留策略：按照類似的保留需求，可以將FBDA對象中的歷史表數據聚集在一起，Oracle 11g也提供了自動清理FBDA中數據的方法，一旦超出了指定的保留期限就會自動執行清理工作，多個表可以共享同一個數據保留和清理策略，因為FBDA是由一個或多個表空間構成的，所以可以創建多個FBDA，每個FBDA指定不同的保留期限，這樣就可以創建多個FBDA滿足不同需求的保留策略，下面是一些常見的示例：

　　 為普通的短期歷史查詢保留90天

　　 為普通的長期歷史查詢保留1整年

　　 為國家法律需要保留20年

　配置閃回數據歸檔

　　准備一個Oracle 11g數據庫使用FBDA功能是相當簡單的，只需要經過幾個簡單的步驟即可：

　　(1)創建或指定一個或多個表空間用於FBDA保留歷史數據

　　(2)隨意指派一個FBDA作為數據庫的默認FBDA

　　(3)指派一個用戶賬戶作為FBDA管理員，授予它FlashBACK ARCHIVE ADMINISTER系統權限

　　(4)授予FBDA權限給適合的用戶賬號

　　(5)授予FlashBACK和SELECT權限給合適的FBDA表用戶

　　(6)為FBDA用戶授予DBMS_FlashBACK存儲過程EXECUTE權限

　　下面是具體的實現命令：

-- 創建FBDA管理員用戶賬號

　　DROP USER fbda_admin CASCADE;

　　CREATE USER fbda_admin IDENTIFIED BY fbda_admin;

　　GRANT FlashBACK ARCHIVE ADMINISTER TO fbda_admin;

　　-- 授予其它用戶合適的閃回權限

　　GRANT FlashBACK ANY TABLE TO hr;

　　GRANT EXECUTE ON DBMS_FlashBACK TO hr;

　　GRANT FlashBACK ANY TABLE TO oe;

　　GRANT EXECUTE ON DBMS_FlashBACK TO oe;

　　GRANT FlashBACK ANY TABLE TO sh;

　　GRANT EXECUTE ON DBMS_FlashBACK TO sh;

　　-- 為閃回數據歸檔創建表空間

　　DROP TABLESPACE fbda INCLUDING CONTENTS AND DATAFILES;

　　CREATE TABLESPACE fbda

　　DATAFILE '/u01/app/Oracle/oradata/ORCL/tsp_fdba01.dbf'

　　SIZE 24M;

　　-- 創建一個閃回數據歸檔保留5天有價值的歷史

　　DROP FlashBACK ARCHIVE fbda_1;

　　CREATE FlashBACK ARCHIVE fbda_1

　　TABLESPACE fbda

　　QUOTA 1M

　　RETENTION 5 DAY;

　　GRANT FlashBACK ARCHIVE ON fbda_1 TO hr;

　　GRANT FlashBACK ARCHIVE ON fbda_1 TO oe;

　　GRANT FlashBACK ARCHIVE ON fbda_1 TO sh;

　　-- 創建一個閃回數據歸檔保留1整年有價值的歷史

　　DROP FlashBACK ARCHIVE fbda_2;

　　CREATE FlashBACK ARCHIVE fbda_2

　　TABLESPACE fbda

　　QUOTA 4M

　　RETENTION 1 YEAR;

　　GRANT FlashBACK ARCHIVE ON fbda_2 TO hr;

　　GRANT FlashBACK ARCHIVE ON fbda_2 TO oe;

　　GRANT FlashBACK ARCHIVE ON fbda_2 TO sh;

　　-- 創建一個閃回數據歸檔保留7年有價值的歷史

　　DROP FlashBACK ARCHIVE fbda_3;

　　CREATE FlashBACK ARCHIVE fbda_3

　　TABLESPACE fbda

　　QUOTA 20M

　　RETENTION 7 YEAR;

　　GRANT FlashBACK ARCHIVE ON fbda_3 TO hr;

　　GRANT FlashBACK ARCHIVE ON fbda_3 TO oe;

　　GRANT FlashBACK ARCHIVE ON fbda_3 TO sh;

　　從上面的代碼可以看出，其實使用閃回數據歸檔真的很簡單，按照上面的代碼，我創建一個表空間FBDA，並在它裡面創建了三個閃回數據歸檔：FBDA_A，FBDA_2和FBDA_3，分別保留5天，1年，7年，我還創建了一個新用戶賬號FBDA_ADMIN，並授予它FlashBACK ARCHIVE ADMINISTER權限，最後，我們給系統中“sample”方案中的HR，OE和SH用戶賬號授予了合適的系統權限，以便它們也可以參與FBDA操作。

　　啟用和禁用一個表的歷史保留能力：使用ALTER TABLE <表名> FlashBACK ARCHIVE [FBDA名]; 命令在現有表上啟用FBDA。

　　 如果沒有指定FBDA名，將會使用默認的FBDA

　　 DBA也可以給表指定想要的FBDA

　　 如果不存在默認的FBDA就必須要明確指定FBDA

　　同樣，使用ALTER TABLE NO FlashBACK ARCHIVE; 命令可以在現有表上禁用FBDA。請看下面的代碼：　

-- 將閃回數據歸檔FBDA_2作為默認FBDA

　　ALTER FlashBACK ARCHIVE fbda_2 SET DEFAULT;

　　-- 將FBDA_1改為默認的FBDA，注意FBDA_1和FBDA_2只會簡單地交換名稱

　　ALTER FlashBACK ARCHIVE fbda_1 SET DEFAULT;

　　-- 啟用一個現有表使用默認的FBDA(fbda_1)

　　ALTER TABLE hr.applicants FlashBACK ARCHIVE;

　　-- 啟用表使用指定的FBDA

　　ALTER TABLE hr.departments FlashBACK ARCHIVE fbda_1;

　　ALTER TABLE hr.job_history FlashBACK ARCHIVE fbda_2;

　　ALTER TABLE oe.customers FlashBACK ARCHIVE fbda_3;

　　-- 在指定表上禁用閃回數據歸檔

　　ALTER TABLE hr.departments NO FlashBACK ARCHIVE;

　　上面的代碼說明了以下基本原理：

　　 可以事後修改默認的FBDA。最初我使用FBDA_2作為默認FBDA，後來我使用ALTER FlashBACK ARCHIVE SET DEFAULT; 命令將默認FBDA改為FBDA_1了。

　　 接下來，我在表HR.APPLICANTS上使用默認FBDA開啟了閃回數據歸檔跟蹤

　　 然後，我對HR.DEPARTMENTS，HR.JOB_HISTORY和OE.CUSTOMERS表開啟了閃回數據歸檔跟蹤，為它們的歷史數據選擇了不同的FBDA。

　　 最後，為了顯示如何在表上中斷FBDA跟蹤，我再次在OE.CUSTOMERS 表上使用了ALTER TABLE NO FlashBACK ARCHIVE;命令。

閃回數據歸檔：歷史數據和元數據

　　Oracle 11g在FBDA中存儲數據時沒有“重新設計車輪”，每個啟用FBDA的表使用三個簡單的表結構，每個都以<源表所有者>.SYS_FBA_<目的>_格式命名，如表1所示。這些表中的數據可以直接查詢，對於那些想一探Oracle 11g是如何管理FBDA基礎結構的人來說非使用這些表不可。

　　表1.閃回數據歸檔：歷史數據存儲

　　為了便於解說，我首先在HR.APPLICANTS表上使用DML語句對其做了一個改變，然後我清洗了數據庫緩沖區，並使用ALTER SYSTEM SET UNDO_TABLESPACE=... SCOPE=BOTH; 命令將UNDO過程切換到一個不同的表空間上，請看下面的代碼：

-- 對HR.APPLICANTS表做一些改變以便在對應的FBDA對象中自動產生"delta"記錄，對最接近1000美元的申請者申請的薪水進行四捨五入

　　UPDATE hr.applicants

　　SET salary_desired = ROUND(salary_desired, -3)

　　WHERE MOD(applicant_id,5) = 0;

　　COMMIT;

　　-- 對最接近100美元的申請者申請的薪水進行四捨五入

　　UPDATE hr.applicants

　　SET salary_desired = ROUND(salary_desired, -2)

　　WHERE MOD(applicant_id,5) <> 0

　　AND MOD(applicant_id,3) = 0;

　　COMMIT;

　　-- 現在清空數據庫緩沖區和當前UNDO表空間的內容，確保閃回版本查詢從閃回數據歸檔中返回HR.APPLICANTS表的內容

　　DROP TABLESPACE smallundo INCLUDING CONTENTS AND DATAFILES;

　　CREATE UNDO TABLESPACE smallundo

　　DATAFILE '/u01/app/Oracle/oradata/ORCL/tsp_smallundo01.dbf'

　　SIZE 2M;

　　ALTER SYSTEM CHECKPOINT;

　　ALTER SYSTEM FLUSH BUFFER_CACHE;

　　ALTER SYSTEM SET UNDO_TABLESPACE = 'SMALLUNDO' SCOPE=BOTH;

　　TTITLE 'Sample Data from Flashback Data Archives|(From SYS_FBA_HIST_73218)'

　　COL applicant_id FORMAT 99999 HEADING 'Appl|ID'

　　COL endscn FORMAT 9999999 HEADING 'Ending|SCN'

　　COL last_name FORMAT A20 HEADING 'Last Name'

　　COL gender FORMAT A2 HEADING 'G|N|D|R'

　　COL salary_desired FORMAT 999999.99 HEADING 'Salary|Desired'

　　COL job_desired FORMAT A10 HEADING 'Job|Desired'

　　SELECT

　　applicant_id

　　,endscn

　　,last_name

　　,gender

　　,salary_desired

　　,job_desired

　　FROM HR.SYS_FBA_HIST_73218

　　WHERE applicant_id < 10

　　ORDER BY applicant_id, endscn;

　　TTITLE OFF

　　TTITLE 'Sample Data from HR.APPLICANTS|(Between Time Periods)'

　　COL applicant_id FORMAT 99999 HEADING 'Appl|ID'

　　COL versions_endscn FORMAT 9999999 HEADING 'Ending|SCN'

　　COL last_name FORMAT A20 HEADING 'Last Name'

　　COL gender FORMAT A2 HEADING 'G|N|D|R'

　　COL salary_desired FORMAT 999999.99 HEADING 'Salary|Desired'

　　COL job_desired FORMAT A10 HEADING 'Job|Desired'

　　SELECT

　　applicant_id

　　,VERSIONS_ENDSCN

　　,last_name

　　,gender

　　,salary_desired

　　,job_desired

　　FROM hr.applicants

　　VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 04:28','yyyy-mm-dd hh24:mi')

　　AND TO_TIMESTAMP('2008-12-04 04:55','yyyy-mm-dd hh24:mi')

　　WHERE APPLICANT_ID < 10

　　ORDER BY applicant_id, versions_endscn;

　　TTITLE OFF

　　當UNDO表空間成功切換後，我查詢SYS_FBA_HIST_73218表獲取最近的UNDO事務，結果如下：

Sample Data from Flashback Data Archives

　　(From SYS_FBA_HIST_73218)

　　G

　　N

　　Appl Ending D Salary Job

　　ID SCN Last Name R Desired Desired

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

　　3 1203058 Brown M 70113.04 IT_CNTR2

　　6 1203058 Chandler M 55511.77 IT_CNTR1

　　9 1203058 Chestnut M 73042.53 IT_CNTR3

　　直接從表本身查詢，對比如下：

Sample Data from HR.APPLICANTS

　　(Between Time Periods)

　　G

　　N

　　Appl Ending D Salary Job

　　ID SCN Last Name R Desired Desired

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

　　1 Aniston M 88017.94 IT_CNTR2

　　2 Niven M 82553.39 IT_CNTR1

　　3 1203058 Brown M 70113.04 IT_CNTR2

　　3 Brown M 70100.00 IT_CNTR2

　　4 Murdock M 70389.16 IT_CNTR2

　　5 1202273 Bedelia M 38720.86 IT_CNTR3

　　5 Bedelia M 39000.00 IT_CNTR3

　　6 1203058 Chandler M 55511.77 IT_CNTR1

　　6 Chandler M 55500.00 IT_CNTR1

　　7 Lerner M 80587.46 IT_CNTR2

　　8 Robinson M 49516.37 IT_CNTR3

　　9 1203058 Chestnut M 73042.53 IT_CNTR3

　　9 Chestnut M 73000.00 IT_CNTR3

　　FBDA元數據：Oracle 11gR1提供了幾個關於FBDA元數據的數據字典視圖，包括哪個表空間支持可擴展的歷史數據存儲，以及FBDA中保留了哪個表：

　　表2.閃回數據歸檔：元數據

使用閃回數據歸檔：審計和數據修復

　　至此，閃回數據歸檔已經建立起來，但有什麼用途呢?下面就列舉幾個情景來說明它的用途：

　　 審計歷史事務

　　現在我們已經在HR.APPLICANTS表上建立起FBDA跟蹤了，所有發生變化的數據將會自動保留下來，這樣我們就可以向同等雇傭機會委員會(EEOC)證明我們在招聘人員時沒有歧視，因為我們最近和美國聯邦政府簽訂了這樣一份協議，以後我就可以拿事實數據進行說明了。

　　 數據粉碎

　　相信大部分對於保留歷史數據在法律上的重要性都有深刻的理解，好的記賬原則要求至少保留關鍵財務數據達7年之久，方便國家稅務機關審計。Oracle 11g將會自動刪除超出保留期限的數據，在數據粉碎期間，只針對歷史數據，而不是FBDA自身。

　　修復丟失的或錯誤修改的數據

　　以我過去30年的IT經歷來看，很多時候用戶、程序開發人員甚至DBA可能不經意錯誤地修改了關鍵數據，甚至物理地刪除了關鍵表中的行，更可怕的是，這些錯誤可能過了很久才被發現，那個時候可能最希望能夠如魔法般地重建數據，這放在過去，只能不完全恢復數據，閃回數據當然也支持不完全恢復，但它的粒度是數據庫和指定的SCN(但前提是在犯錯前已經開啟了閃回日志功能)，閃回表仍然受限於當前UNDO表空間UNDO保留的數量。下面給出一段代碼顯示如何使用閃回數據歸檔數據和閃回查詢來找回丟失的數據的：

DELETE FROM hr.applicants

　　WHERE application_date <= TO_DATE('11-10-2008','dd-mm-yyyy');

　　COMMIT;

　　INSERT INTO hr.applicants

　　SELECT *

　　FROM hr.applicants

　　VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-04 10:00','yyyy-mm-dd hh24:mi')

　　AND MAXVALUE

　　WHERE VERSIONS_OperaTION = 'D';

　　COMMIT;

　　維護閃回數據歸檔

　　雖然閃回數據歸檔自身可以完成維護，但有時還是需要手動維護，如：

　　 擴大現有FBDA的大小(新的大小不能超過授予FBDA管理用戶限額的最大表空間尺寸)。

　　 改變FBDA上歷史數據保留策略。

　　 手動清洗FBDA內比指定日期時間還舊的數據。

　　 刪除現有FBDA(注意刪除FBDA時並沒有刪除對應的表空間)。

　　請看下面的代碼：

-- 清洗現有FBDA中超過1天的數據

　　ALTER FlashBACK ARCHIVE fbda_1

　　PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

　　-- 修改現有FBDA的空間限額，減小到2M

　　ALTER FlashBACK ARCHIVE fbda_1

　　MODIFY TABLESPACE fbda QUOTA 2M;

　　-- 減少現有FBDA的保留期限為90天

　　ALTER FlashBACK ARCHIVE fbda_3

　　MODIFY RETENTION 90 DAY;

　　-- 給現有FBDA增加一個無空間限額的新表空間，這樣就允許FBDA使用新增加表空間的所有可用空間

　　DROP TABLESPACE fbda_extd INCLUDING CONTENTS AND DATAFILES;

　　CREATE TABLESPACE fbda_extd

　　DATAFILE '/u01/app/Oracle/oradata/ORCL/tsp_fdba_extd01.dbf'

　　SIZE 16M;

　　ALTER FlashBACK ARCHIVE fbda_1

　　ADD TABLESPACE fbda_extd;

　　-- 刪除一個現有FBDA，注意對應的表空間仍然存在

　　DROP FlashBACK ARCHIVE fbda_1;

　　FBDA空間管理：當一個FBDA用盡了所有可用的空間時，由這個FBDA支持的表如果發生修改操作時，其會話會接收到一個或兩個錯誤消息(下面用fbda_1來解釋這兩個錯誤)：

　　 ORA-55617: Flashback Archive fbda_1 runs out of space and tracking on fda1 is suspended

　　這個錯誤消息指出了哪個FBDA空間幾乎用完了，當FBDA達到90%或更高時就會報這個錯誤。

　　 ORA-55623: Flashback Archive fbda_1 is blocking and tracking on all tables is suspended

　　這種情況下，FBDA已經完全用完了可用空間。

　　不管出現哪個錯誤，DBA都可以手動增加FBDA的限額，或直接增加FBDA所在表空間的大小，注意這些錯誤也會記錄到Alert.log文件中。

　　閃回數據歸檔：限制和建議

　　你可能已經猜到這麼強大的功能肯定會有限制條件的，不錯，下面就列舉出這些限制條件：

　　DDL限制

　　如果在開啟FBDA的表上應用下面這些DDL命令，Oracle 11g將會產生一個異常：

　　(1)ALTER TABLE <表名>命令，如

　　刪除一列

　　重命名列

　　修改列

　　執行PARTITION 或SUBPARTITION 操作

　　將列的數據類型從LONG轉換為LOB

　　調用UPGRADE TABLE操作，不管是否指定了INCLUDING DATA選項

　　(2)DROP TABLE <表名>;

　　(3)RENAME TABLE <表明>;

　　(4)TRUNCATE TABLE <表名>;

　　 最佳實踐

　　最後，我還是列出在Oracle 11g中使用閃回數據歸檔特性時推薦的最佳實踐：

　　(1)在查詢以往數據之前執行一下COMMIT 或ROLLBACK 操作，這樣可以確保數據庫的一致性。

　　(2)閃回數據歸檔進程總是使用當前會話設置，包括NLS設置如NLS_LANGUAGE和NLS_CHARACTERSET，但實際中當歷史數據被保留時，這些變量的設置可能並不匹配。

　　(3)Oracle推薦使用INTERVAL和TIMESTAMP變量轉換函數來估算過去的時間，如指定SYSTIMESTAMP – INTERVAL ‘20’ DAYS 來獲得啟用FBDA特性的表過去的數據。

　　(4)為了更精確地查詢FBDA中的數據，Oracle推薦使用SCN，記住TIMESTAMP_TO_SCN函數可以用來直接從TIMESTAMP值中獲得一個相對准確的SCN值，但它的精確度也只能達到3秒左右。

　　小結

　　Oracle 11g新的閃回數據歸檔特性讓DBA有能力將歷史數據保留非常長的時間，只要保存歷史數據的表空間的容量足夠大，因為閃回查詢、閃回版本查詢和閃回事務查詢也受到支持，因此Oracle DBA也可以利用FBDA特性來糾正對數據的錯誤修改。FBDA安裝容易，監控簡單，自我維護，相信它會成為Oracle DBA受歡迎的工具。