記一次在非歸檔模式下的Oracle資料庫用閃回操作恢復表和DML語句

Summer的小西瓜發表於2017-09-07
模式Oracle資料庫

最近看DBA相關的書，其中有一句話說的很好：一切的備份都是為了恢復
於是自己拿測試環境試了試，模擬一下恢復操作

具體測試場景：Flashback閃回table和DML語句
oracle版本：11gR2
小版本號：11.2.0.4.0
是否歸檔：非存檔模式

首先書裡寫了，只要查詢show parameter bin，recyclebin 的值為on，即可用flashback恢復drop的表，但是上網一查，貌似閃回操作還得開啟歸檔模式，書中沒寫是否要必須開啟歸檔模式，我們來試驗一下看看：

首先建立一張表testlijian，裡面有一個欄位叫name，insert三條資料為lijian3,wangsiqi,helloworld，具體操作由PLSQL圖形化介面完成，我就不貼建表語句了。

之後我們執行show parameter bin，得到結果：recyclebin 的值為on

SQL> show parameter bin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
recyclebin                           string      on

之後我們drop這個表，執行drop table testlijian;
ok,這張表被drop掉了。
我們現在執行select * from testlijian;
得到資料庫中沒有這張表：

SQL> select * from testlijian;
select * from testlijian
ORA-00942: 表或檢視不存在

我們現在執行flashback table testlijian to before drop;
之後再次執行select * from testlijian;，會發現表已經回來了，而且表裡的記錄也沒有丟失。
完整的執行過程如下：

SQL> select * from testlijian;
select * from testlijian
ORA-00942: 表或檢視不存在

SQL> flashback table testlijian to before drop;
Done

SQL> select * from testlijian;
NAME
--------------------------------------------------
lijian3
wangsiqi
helloworld

SQL>

從這裡可以驗證出來，flashback閃回被drop掉的table，是不需要開啟歸檔模式的，只跟是否開啟回收站功能有關。

之後我們來驗證一下DML語句是否也可以不用開啟歸檔模式，用閃回來恢復。
我們執行一條命令：
update testlijian set name =`lijian4` where name = `lijian3`;
commit;
之後用如下命令來確定xid號：

select versions_xid,name
from testlijian
versions between scn minvalue and maxvalue;

得到剛剛提交的update操作的xid號為:0900090018740000

我們再執行如下命令：

select operation , undo_sql
from flashback_transaction_query
where xid = hextoraw(`0900090018740000`)

我們把完整的操作記錄複製出來看一下：

SQL> update testlijian set name =`lijian4` where name = `lijian3`;
1 row updated

SQL> 
SQL> select versions_xid,name
  2  from testlijian
  3  versions between scn minvalue and maxvalue;
VERSIONS_XID     NAME
---------------- --------------------------------------------------
                 lijian3
                 wangsiqi
                 helloworld

SQL> commit;
Commit complete

SQL> 
SQL> select versions_xid,name
  2  from testlijian
  3  versions between scn minvalue and maxvalue;
VERSIONS_XID     NAME
---------------- --------------------------------------------------
0900090018740000 lijian4
                 lijian3
                 wangsiqi
                 helloworld

SQL> 
SQL> select operation , undo_sql
  2  from flashback_transaction_query
  3  where xid = hextoraw(`0900090018740000`)
  4  ;
OPERATION                        UNDO_SQL
-------------------------------- --------------------------------------------------------------------------------
UNKNOWN                          
BEGIN                            

SQL>

我們可以看到，在最後一個SQL執行完了之後，undo_sql怎麼是空的呢？然後operation也是unknown，明明我執行了一個update語句呀？

關於這裡，我上網查詢了一下：原來在11gR2裡，有一個功能預設是不開啟的
oracle11gR2預設禁止supplemental logging
需要把它開啟，需要執行alter database add supplemental log data;
如果要把它關閉，就是執行alter database drop supplemental log data;

我們來實驗一下:
執行alter database add supplemental log data;
再把我們們之前的update語句改一改：
update testlijian set name =`lijian5` where name = `lijian4`;
之後再重複一下我們之前做過的操作，完整記錄貼出來：

SQL> alter database add supplemental log data;
Database altered

SQL> update testlijian set name =`lijian5` where name = `lijian4`;
1 row updated

SQL> commit;
Commit complete

SQL> 
SQL> select versions_xid,name
  2  from testlijian
  3  versions between scn minvalue and maxvalue;
VERSIONS_XID     NAME
---------------- --------------------------------------------------
060015002A710000 lijian5
0900090018740000 lijian4
                 lijian3
                 wangsiqi
                 helloworld

SQL> 
SQL> select operation , undo_sql
  2  from flashback_transaction_query
  3  where xid = hextoraw(`060015002A710000`);
OPERATION                        UNDO_SQL
-------------------------------- --------------------------------------------------------------------------------
UPDATE                           update "JLLT_DM"."TESTLIJIAN" set "NAME" = `lijian4` where ROWID = `AAAW08AAFAAK
BEGIN                            

SQL>

ok，這回我們看見了undo_SQL的內容，正是我們執行的Update語句。

我們執行：

select operation,start_scn
from flashback_transaction_query
where xid = hextoraw(`060015002A710000`);

得到SCN號為：1559951294，後來發現這個數不對，因為沒設定行顯示長度，看的不全，導致後面有一個錯誤出來。

我們先開啟行遷移模式:
alter table testlijian enable row movement;
執行flashback table testlijian to SCN 1559951294;
完整操作如下：

SQL> select operation,start_scn
  2  from flashback_transaction_query
  3  where xid = hextoraw(`060015002A710000`);
OPERATION                         START_SCN
-------------------------------- ----------
UPDATE                           1559951294
BEGIN                            1559951294

SQL> alter table testlijian enable row movement;
Table altered

SQL> flashback table testlijian to SCN 1559951294;
flashback table testlijian to SCN 1559951294
ORA-08181: 指定的編號不是有效的系統更改號

SQL> flashback table testlijian to SCN 15599512948179;
Done

SQL> select * from testlijian;
NAME
--------------------------------------------------
lijian4
wangsiqi
helloworld

SQL> alter table testlijian disable row movement;
Table altered

SQL>

我們驚喜的看到，lijian5又變回了lijian4，這說明我們不開啟歸檔模式，我們也能夠通過閃回找回誤操作後的資料了！
但是這個操作，有一個限制條件，那就是這個找回，是在回滾段裡找回的，那就跟一個引數的設定有關，即：undo_retention
我們看一下庫裡這個引數的設定：

SQL> select name,value
  2  from v$parameter
  3  where name like `%undo%`;
NAME                                                                             VALUE
-----------------------------------------------------------------
undo_management                                                         AUTO
undo_tablespace                                                          UNDOTBS1
undo_retention                                                                   900

可以看到，這裡是900秒，也就是說，我們的誤操作，第一要滿足undo的空間足夠大，能夠裝得下這部分資料，另外要滿足15分鐘這個條件，才能通過非歸檔模式下的flashback找回成功。
從這裡我們展開一下聯想，很多資料庫都開了歸檔模式，但是並沒有配置Rman來對這些archivelog利用最大化，這樣的話還不如不開，因為開了歸檔模式資料庫的壓力會變高，第二管理歸檔日誌會帶來一定的維護成本。當然具體如何抉擇，還是要依靠大家的實際情況。

相關文章