【備份恢復】閃回資料庫（三）基於時間戳閃回資料庫

基於時間戳閃回資料庫
1) 建立測試表，並插入測試資料

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>create table fbdb_time as select * from fbdb_scn where 1=2;

 

Table created.

SCOTT@ORA11GR2>insert into fbdb_time select 1 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

 

1 row created.

SCOTT@ORA11GR2>commit;

 

Commit complete.

SCOTT@ORA11GR2>insert into fbdb_time select 2 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

 

1 row created.

 

SCOTT@ORA11GR2>commit;

 

Commit complete.

 

SCOTT@ORA11GR2>insert into fbdb_time select 3 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

 

1 row created.

 

SCOTT@ORA11GR2>commit;

 

Commit complete.

 

SCOTT@ORA11GR2>select * from fbdb_time;

 

        ID        SCN DD

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

         1    1874151 2016-10-01 07:41:40

         2    1874195 2016-10-01 07:42:43

         3    1874285 2016-10-01 07:43:01

 

2) 刪除 scott 使用者

SCOTT@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>drop user scott cascade;

 

User dropped.

 

3) 資料庫啟動到 mount 模式（準備閃回資料庫）

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

SYS@ORA11GR2>

 

4) 執行第一次閃回操作，閃回到 5 分鐘以前（計劃閃回到 fbdb_time 表中存在 2 條記錄那一刻）

SYS@ORA11GR2>set time on

07:48:04 SYS@ORA11GR2>flashback database to timestamp sysdate-5/1440;

 

Flashback complete.

 

5) 以 read only 模式開啟資料庫，驗證是否閃回到理想的時間點  

07:48:39 SYS@ORA11GR2>alter database open read only;

 

Database altered.

 

07:48:58 SYS@ORA11GR2>select * from scott.fbdb_time;

 

        ID        SCN DD

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

         1    1874151 01-OCT-16

         2    1874195 01-OCT-16

         3    1874285 01-OCT-16

 

6) 從上面結果可以看出，顯然不是我們想要的結果，資料庫重新啟動到 mount 模式， 第二次執行閃回
資料庫操作， 這次以準確的時間去執行

07:50:17 SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

07:50:28 SYS@ORA11GR2>

07:50:29 SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

07:50:45 SYS@ORA11GR2>flashback database to timestamp to_date('2016-10-01 07:42:50','yyyy-mm-dd hh24:mi:ss');

 

Flashback complete.

 

7) 再次以 read only 模式開啟資料庫，驗證結果，閃回基本完美

07:52:16 SYS@ORA11GR2>alter database open read only;

 

Database altered.

 

07:52:33 SYS@ORA11GR2>select * from scott.fbdb_time;

 

        ID        SCN DD

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

         1    1874151 01-OCT-16

         2    1874195 01-OCT-16

 

07:52:46 SYS@ORA11GR2>conn scott/tiger;

Connected.

SCOTT@ORA11GR2>select * from scott.fbdb_time;

 

        ID        SCN DD

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

         1    1874151 2016-10-01 07:41:40

         2    1874195 2016-10-01 07:42:43

 

8) 重新啟動資料庫，以 resetlogs 方式開啟資料庫，整個恢復動作完成

SCOTT@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database open resetlogs;

 

Database altered.

 

驗證：

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>select * from fbdb_time;

 

        ID        SCN DD

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

         1    1874151 2016-10-01 07:41:40

         2    1874195 2016-10-01 07:42:43

9) 小結
基於事件的閃回一般都沒有準確的閃回時間點，那麼就需要我們做到儘量精確，把資料的損失降低到最小，那麼就需要我們多次的重複以 read only 方式開啟資料庫。就像一個形容包子餡兒小的笑話說的那樣：“吃包子，第一口沒吃到餡兒，第二口，過去了”。我們基於時間的閃回資料庫也是一樣，不要衝動的大約一個時間閃回，閃回完成後就直接 resetlogs 開啟，只要以 resetlogs 開啟後，就不可逆了，作為合格的 DBA，一定要把損失控制到最小，所以，要多次以 read only 方式開啟驗證才是上策。