基于时间戳闪回数据库
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 方式打开验证才是上策。