11g閃回資料歸檔

11g閃回資料歸檔

 

=================

 

閃回資料歸檔是用來儲存一個或多個錶的歷史資料的新資料庫物件、以及該資料的儲存保留和清除策略
歸檔保留資料庫中表的變化
並將這些變化儲存在表空間中
這些變化資料是壓縮儲存的，並且只保留過去的變化
所以插入操作時不會記錄在變化中的

 

為了實現閃回資料歸檔，oracle使用後臺程式FBDA（flashback data archive proces）來定期將undo中捕獲的變化寫入閃回資料歸檔中
FBDA程式預設5分鐘捕獲一次表的歸檔變化資料
不過如果系統特別繁忙，這個頻率也會加快

 

資料庫預設的堆表並不開啟閃回資料歸檔
所以要使用這個功能必須在建立表的時候新增 falshback data archive關鍵字或者使用alter table命令啟用表的歸檔

資料庫中可以對多個表使用閃迴歸檔
這些歸檔資料可以在同一個表空間也可以在不同的表空間
同一個資料庫中可以有多個閃回資料歸檔
對於不同的閃回資料歸檔採用不同的策略，如一個資料自動保留5年，一個資料自動保留2年
對於開啟閃回資料歸檔的表不能直接刪除，要先關閉閃回資料歸檔後才能刪除

 

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

 

--建立閃回資料歸檔需要的許可權

FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER

 

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

--測試

 

--測試環境版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

 

--建立存放閃回資料歸檔的表空間flash_data1和flash_data2

SQL> create tablespace flash_data1 datafile '/oracle/oradata/db11/db11/flash001.dbf' size 10m autoextend on maxsize 4096m;

Tablespace created.

SQL>  create tablespace flash_data2 datafile '/oracle/oradata/db11/db11/flash0021.dbf' size 10m autoextend on maxsize 4096m;

Tablespace created.

 

--建立閃迴歸檔flash_1y
--分配預設表空間flash_data1
--預設表空間中分配4000m的空間許可權
--閃迴歸檔資料儲存1年

 

SQL> create flashback archive flash_1y
  2  tablespace flash_data1
  3  quota 4000m
  4  retention 1 year;

Flashback archive created.

--對閃回資料歸檔的操作

--設定系統預設歸檔
SQL> alter flashback archive flash_1y set default;

Flashback archive altered.

 

 

--為閃回資料歸檔新增表空間
SQL> alter flashback archive flash_1y add tablespace flash_data2;

Flashback archive altered.

 

 

--調整閃迴歸檔資料對應表空間的使用空間數
SQL> alter flashback archive flash_1y modify tablespace flash_data2 quota 10m;

Flashback archive altered.

 

 

--調整閃迴歸檔資料保留的時間，到時間自動刪除過期資料
SQL> alter flashback archive flash_1y modify retention 2 year;

Flashback archive altered.

SQL> alter flashback archive flash_1y modify retention 1 year;

Flashback archive altered.

 

 

--刪除閃迴歸檔資料與對應表空間的聯絡
--刪除後表空間仍然存在
SQL> alter flashback archive flash_1y remove tablespace flash_data2;

Flashback archive altered.

 

 

--清除閃回資料歸檔中所有的變化資料
SQL> alter flashback archive flash_1y purge all;

Flashback archive altered.

 

 

--清除特定時間前的資料
SQL> alter flashback archive flash_1y purge before timestamp(systimestamp-interval '2' day);
Flashback archive altered.

 

 

--時間還可以使用如下值：
systimestamp - interval '2' second
systimestamp - interval '2' day
systimestamp - interval '2' month

 

--清除特定scn之前的資料
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  905328

SQL> alter flashback archive flash_1y purge before scn 905328;

Flashback archive altered.

 

 

--建立測試使用者
SQL> create user an identified by an;

User created.

 

 

--授予許可權
SQL> grant connect,resource to an;

Grant succeeded.

SQL> grant flashback any table to an;

Grant succeeded.

SQL> grant flashback archive administer to an;

Grant succeeded.

 

 

--建立閃回資料歸檔的表
--建表時帶有關鍵字 flashback archive
--並且指定使用的閃回資料歸檔

SQL> conn an/an
Connected.
SQL> create table atest(id int,
  2  name varchar2(10),
  3  address varchar2(20))
  4  flashback archive flash_1y;

Table created.

 

--關閉表的閃回資料歸檔特性
SQL> alter table atest no flashback archive;

Table altered.

 

 

--重新開啟表的閃回資料歸檔
SQL> alter table atest flashback archive;

Table altered.

 

--建立測試資料
SQL> insert into atest select 1,'an','home1' from dual;

1 row created.

SQL> insert into atest select 2,'xiangxiang','home1' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from atest;

        ID NAME       ADDRESS
---------- ---------- --------------------
         1 an         home1
         2 xiangxiang home1

 

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

 

 

--和閃回資料歸檔相關的字典表
--其中比較常用的是前三個
--分別對應系統中的閃回資料歸檔、已經開啟閃回資料歸檔的表以及閃迴歸檔資料存放的表空間

DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
DBA_FLASHBACK_TXN_REPORT
DBA_FLASHBACK_TXN_STATE
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES
USER_FLASHBACK_TXN_REPORT
USER_FLASHBACK_TXN_STATE

 

--這個字典表描述閃回資料歸檔對應的表名、owner、使用的閃回資料歸檔名、記錄表變化情況的日誌表名以及狀態

SQL> desc user_flashback_archive_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 OWNER_NAME                                NOT NULL VARCHAR2(30)
 FLASHBACK_ARCHIVE_NAME                    NOT NULL VARCHAR2(255)
 ARCHIVE_TABLE_NAME                                 VARCHAR2(53)
 STATUS                                             VARCHAR2(8)

SQL> col FLASHBACK_ARCHIVE_NAME for a15
SQL> col ARCHIVE_TABLE_NAME for a20
SQL> col table_name for a15
SQL> col owner_name for a15
SQL> set linesize 300
SQL> set wrap off

 

 

--如下查詢限制表an.atest使用了flash_1y這個閃回資料歸檔
--目前表閃回資料歸檔的狀態為開啟
--記錄表變化情況的日誌表為SYS_FBA_HIST_73424
--程式FBDA不是實時，所以修改完表後過幾分鐘日誌表的資料才會更新

 

 

SQL> select * from user_flashback_archive_tables;

TABLE_NAME      OWNER_NAME      FLASHBACK_ARCHI ARCHIVE_TABLE_NAME   STATUS
--------------- --------------- --------------- -------------------- --------
ATEST           AN              FLASH_1Y        SYS_FBA_HIST_73424   ENABLED

 

 

SQL> conn / as sysdba
Connected.
SQL> select * from USER_FLASHBACK_ARCHIVE;

no rows selected

SQL> select * from dba_flashback_archive;

OWNER_NAME      FLASHBACK_ARCHI FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                           LAST_PURGE_TIME                     STATUS
--------------- --------------- ------------------ ----------------- ----------------------------------------- ------------------------------------------
SYS             FLASH_1Y                         1               365 14-NOV-11 03.22.21.000000000 PM       14-NOV-11 03.33.21.000000000 PM     DEFAULT

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHI FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
--------------- ------------------ ------------------------------ ----------------------------------------
FLASH_1Y                         1 FLASH_DATA1                    4000

 

 

 

--對錶中資料進行更改
Connected.
SQL> update atest set id=id+1;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from atest;                                                                    
                                                                                             
        ID NAME       ADDRESS                                                                
---------- ---------- --------------------                                                   
         2 an         home1                                                                  
         3 xiangxiang home1                                                                  
                                                                                             
SQL> update atest set id=1 where id=3;                                                       
                                                                                             
1 row updated.                                                                               
                                                                                             
SQL> commit;                                                                                 

SQL> select * from atest;                                                                     
                                                                                             
        ID NAME       ADDRESS                                                                
---------- ---------- --------------------                                                   
         2 an         home1                                                                  
         1 xiangxiang home1                                                                  

 

 

--更改後記錄更改的表中並不是馬上新增資料
--因為fbda程式滯後
--要過一段時間時候資料才能進行查詢
--查詢時包括更改資料時的scn、運算元據的型別以及資料的值都會有詳細記錄

 

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
14-NOV-11 03.42.44.061854 PM +08:00

SQL> select * from SYS_FBA_HIST_73424;

no rows selected
                                                                                          
                                                                                             
                                                                                             
SQL> select current_timestamp from dual;                                                     
                                                                                             
CURRENT_TIMESTAMP                                                                            
---------------------------------------------------------------------------                  
14-NOV-11 04.00.41.818424 PM +08:00                                                          
                                                                                             

 

SQL> select * from SYS_FBA_HIST_73424;                                                       
                                                                                             
RID          STARTSCN     ENDSCN XID              O         ID NAME       ADDRESS            
---------- ---------- ---------- ---------------- - ---------- ---------- --------------------
AAAR7QAAEA     905464     905799 0200010082020000 I          1 an         home1              
AAAR7QAAEA     905464     905799 0200010082020000 I          2 xiangxiang home1              
AAAR7QAAEA     905799     906194 04000100FD010000 U          3 xiangxiang home1   

 

 

--查詢

--基於時間

SQL> select * from atest as of timestamp to_timestamp('2011-11-14 15:40:00','yyyy-mm-dd hh24:mi:ss');

        ID NAME       ADDRESS
---------- ---------- --------------------
         1 an         home1
         2 xiangxiang home1

 

SQL>  select * from atest as of timestamp to_timestamp('2011-11-14 16:01:00','yyyy-mm-dd hh24:mi:ss');

        ID NAME       ADDRESS
---------- ---------- --------------------
         1 xiangxiang home1
         2 an         home1

 

 

--基於scn

SQL> select * from atest as of scn 905464;

        ID NAME       ADDRESS
---------- ---------- --------------------
         1 an         home1
         2 xiangxiang home1