【備份恢復】 閃回技術之閃迴歸檔

不一樣的天空w發表於2016-10-17

閃迴歸檔
UNDO表空間記錄的回滾資訊雖然可以提供回閃查詢,但時間久了,這些資訊會被覆蓋掉,其實只要事務一提交,他們就變成可覆蓋的物件了,所以經常在做回閃查詢時,我們會因為找不到undo block而收到1555錯誤,11G裡面引入了Flashback Data Archive ,他用於儲存資料的所有改變,時間由你自己設定,消耗的是更多的磁碟空間
閃回資料歸檔可以只為特定的表服務

1) 建立測試表空間及使用者,分配較為基本的角色

SYS@ORA11GR2>create tablespace ts_users datafile '/u01/app/oracle/oradata/ORA11GR2/ts_users.dbf' size 10m;

 

Tablespace created.

 

SYS@ORA11GR2>create user xx identified by oracle default tablespace ts_users;

 

User created.

 

SYS@ORA11GR2>grant connect,resource to xx;

 

Grant succeeded.

 

2) 建立 flashback archive並設定保留策略

SYS@ORA11GR2>create flashback archive fbad_ts_users tablespace ts_users retention 1 day;

 

Flashback archive created.

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

(建立閃迴歸檔前提,1.開啟快速恢復區,即設定快速恢復區的大小及路徑。2.設定為歸檔模式)
1: 建立、刪除需要有“flashback archive administer” 系統許可權

2:閃回資料歸檔語法
CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive
TABLESPACE tablespace_name
[QUOTA integer {K| M| G| T| P| E}]
RETENTION integer {YEAR | MONTH | DAY};

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

 

3) 建立表 t1啟用閃迴歸檔資料這個動作需要有“flashback archive” 許可權)

SYS@ORA11GR2>conn xx/oracle

Connected.

XX@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;

create table t1(x int) flashback archive fbad_ts_users

*

ERROR at line 1:

ORA-55620: No privilege to use Flashback Archive

 

XX@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>

SYS@ORA11GR2>grant flashback archive on fbad_ts_users to xx;

 

Grant succeeded.

 

SYS@ORA11GR2>conn xx/oracle

Connected.


——對新建的表啟動閃迴歸檔

XX@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;

 

Table created.

 

1)  已存在的表啟動閃迴歸檔資料,如下

XX@ORA11GR2>create table t2(x int);

 

Table created.

 

XX@ORA11GR2>alter table t2 flashback archive fbad_ts_users;

 

Table altered.

 

2)  檢視已啟用閃迴歸檔資料的表(其中: ARCHIVE_TABLE_NAME 為記錄閃回資料的表,我們無法檢視它

SYS@ORA11GR2>select * from dba_flashback_archive_tables;

 

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS

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

T1         XX         FBAD_TS_USERS        SYS_FBA_HIST_89859   ENABLED

T2         XX         FBAD_TS_USERS        SYS_FBA_HIST_89860   ENABLED

 

SYS@ORA11GR2>conn xx/oracle

Connected.


XX@ORA11GR2>select * from dba_flashback_archive_tables;

 

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS

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

T1         XX         FBAD_TS_USERS        SYS_FBA_HIST_89859   ENABLED

T2         XX         FBAD_TS_USERS        SYS_FBA_HIST_89860   ENABLED

 

3)  禁用閃迴歸檔資料

使用no flashback archive子句可以 disable flashback archive
(注:需要有
“flashback archive administer”系統許可權)

 

XX@ORA11GR2>alter table t2 no flashback archive;

alter table t2 no flashback archive

*

ERROR at line 1:

ORA-55620: No privilege to use Flashback Archive

或者回到sys下授權,或者直接在sys下操作;

 

XX@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>

SYS@ORA11GR2>alter table xx.t2 no flashback archive;

 

Table altered.

 

驗證:使用者xx下的t2表已經禁用了閃迴歸檔:

SYS@ORA11GR2>select * from dba_flashback_archive_tables;

 

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS

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

T1         XX         FBAD_TS_USERS        SYS_FBA_HIST_89859   ENABLED

 

4)  插入資料:

XX@ORA11GR2>insert into t1 values (1);

 

1 row created.

 

XX@ORA11GR2>insert into t1 values (2);

 

1 row created.

 

XX@ORA11GR2>insert into t1 values (3);

 

1 row created.

 

XX@ORA11GR2>commit;

 

Commit complete.


XX@ORA11GR2>select * from t1;

 

         X

----------

         1

         2

         3


——查詢當前scn:

XX@ORA11GR2>select dbms_flashback.get_system_change_number as scn from dual;

select dbms_flashback.get_system_change_number as scn from dual

       *

ERROR at line 1:

ORA-00904: : invalid identifier

xx使用者沒有查詢scn的許可權)

 

轉到sys使用者查詢:

XX@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>

SYS@ORA11GR2>select dbms_flashback.get_system_change_number as scn from dual;

 

       SCN

----------

   1786408

 

——刪除xx使用者下的t1表的資料:

SYS@ORA11GR2>delete xx.t1 where x<=2;

 

2 rows deleted.

 

SYS@ORA11GR2>commit;

 

Commit complete.

 

SYS@ORA11GR2>select * from xx.t1;

 

         X

----------

         3

 

——利用閃迴歸檔進行閃回查詢:(閃迴歸檔也只是查詢先前的資料,而不能直接修復)

SYS@ORA11GR2>select * from xx.t1 as of scn 1786408;

 

         X

----------

         1

         2

         3

 

5)  刪除當前 undo 表空間(為了驗證前面操作的閃回資訊是從閃迴歸檔裡來的,而不是從undo表空間

SYS@ORA11GR2>show parameter undo_tablespace

 

NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS2


SYS@ORA11GR2>create undo tablespace undo1 datafile '/u01/app/oracle/oradata/ORA11GR2/undo1.dbf'  size 100m autoextend on next 10m;

 

Tablespace created.

 

SYS@ORA11GR2>alter system set undo_tablespace=undo1;

 

System altered.

 

SYS@ORA11GR2>show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     7200

undo_tablespace                      string      UNDO1


SYS@ORA11GR2>drop tablespace undotbs2 including contents and datafiles;

 

Tablespace dropped.

 

6)  再次透過閃回查詢檢視刪除的資料,此次查詢出來的資料就是從閃迴歸檔中查詢得出:

SYS@ORA11GR2>select * from xx.t1 as of scn 1786408;

 

         X

----------

         1

         2

         3

 

7)  清除歸檔 FBAD_TS_USERS 5 分鐘之前的資料(閃迴歸檔中的資料時可以刪除的),再次利用剛才的閃回查詢的 SCN,此時我們發現, 結果與表中實際情況相同。

 

SYS@ORA11GR2>alter flashback archive fbad_ts_users purge before timestamp (sysdate-5/1440);

 

Flashback archive altered.

 

SYS@ORA11GR2>

S

SYS@ORA11GR2>select * from xx.t1 as of scn 1786408;

 

         X

----------

         1

         2

         3

 

SYS@ORA11GR2>

結果與實際情況不相同,則需先清空buffer cache裡的記憶體(alter system flush buffer cache

同理:alter system flush shared pool

11) 清除閃迴歸檔 FBAD_TS_USERS 中所有資料

SYS@ORA11GR2>alter flashback archive fbad_ts_users purge all;

 

Flashback archive altered.

 

SYS@ORA11GR2>

 

12) 再次執行閃回查詢,因為可以透過 undo 得到指定 SCN 的資料,測試時, undo 資料並未被覆蓋
SYS@ORA11GR2>select * from xx.t1 as of scn 1786408;

 

         X

----------

         3

(因為閃迴歸檔裡資料清空了,所以不能再對錶進行閃回查詢了,所以查到的資料只是DML操作commit之後儲存的資料;)

 

小結:
- 啟用了閃迴歸檔功能的表不允許 drop
- 啟用了閃迴歸檔功能的表允許 truncate
- 允許新增、刪除列
- 不允許刪除表所在使用者
- 將表歸檔資料禁用後則可以正常刪除

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2126604/,如需轉載,請註明出處,否則將追究法律責任。

相關文章