【备份恢复】 闪回数据库(三) 基于时间戳闪回数据库

1.创建测试表，并插入测试数据

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

Table created.

SCOTT@PROD1>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@PROD1>commit;

Commit complete.

16:34:07 SCOTT@PROD1>insert into fbdb_time select 1 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

1 row created.

16:34:13 SCOTT@PROD1>commit;

Commit complete.

16:35:17 SCOTT@PROD1>insert into fbdb_time select 2 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

1 row created.

16:35:26 SCOTT@PROD1>commit;

Commit complete.

16:36:18 SCOTT@PROD1>insert into fbdb_time select 3 as id,dbms_flashback.get_system_change_number as scn,sysdate as dd from dual;

1 row created.

16:36:21 SCOTT@PROD1>commit;

Commit complete.

16:36:23 SCOTT@PROD1>select * from fbdb_time;

       ID        SCN DD
---------- ---------- -------------------
        1    1231450 2016-12-26 16:34:13
        2    1231577 2016-12-26 16:35:26
        3    1231617 2016-12-26 16:36:21

2.删除scott用户
SYS@PROD1>drop user scott cascade;

User dropped.

3.数据库启动到mount模式(准备闪回数据库)
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             520096848 bytes
Database Buffers          310378496 bytes
Redo Buffers                2371584 bytes
Database mounted.

4.执行第一次闪回操作，闪回到5分钟以前（计划闪回到fbdb_time表中存在2条记录那一刻）
SYS@PROD1>flashback database to timestamp sysdate-5/1440;

Flashback complete.

5.以read only模式打开数据库，验证是否闪回到理想的时间点
SYS@PROD1>alter database open read only;

Database altered.

SYS@PROD1>select * from scott.fbdb_time;

       ID        SCN DD
---------- ---------- -------------------
        1    1231450 2016-12-26 16:34:13
       
6.从上面结果可以看出，显然不是我们想要的结果，数据库重新启动到mount模式，第二次执行闪回
数据库操作，这次以准确的时间去执行。
SYS@PROD1>flashback database to timestamp to_date('2016-12-26 16:36:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.


7) 再次以 read only 模式打开数据库，验证结果，符合要求
SYS@PROD1>alter database open read only;

Database altered.

SYS@PROD1>select * from scott.fbdb_time;

       ID        SCN DD
---------- ---------- -------------------
        1    1231450 2016-12-26 16:34:13
        2    1231577 2016-12-26 16:35:26

8.重新启动数据库，以resetlogs方式打开数据库，整个恢复动作完成

SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             520096848 bytes
Database Buffers          310378496 bytes
Redo Buffers                2371584 bytes
Database mounted.
SYS@PROD1>alter database open resetlogs;

Database altered.

SYS@PROD1>select * from scott.fbdb_time;

       ID        SCN DD
---------- ---------- -------------------
        1    1231450 2016-12-26 16:34:13
        2    1231577 2016-12-26 16:35:26

9.小结
基于事件的闪回一般都没有准确的闪回时间点，那么就需要我们做到尽量精确，把数据的损失降低到最小，那么就需要我们多次的重复以
read only 方式打开数据库。就像一个形容包子馅儿小的笑话说的那样：“吃包子，第一口没吃到馅儿，第二口，过去了”。
我们基于时间的闪回数据库也是一样，不要冲动的大约一个时间闪回，闪回完成后就直接 resetlogs 打开，只要以 resetlogs 打开后，
就不可逆了，作为合格的 DBA，一定要把损失控制到最小，所以，要多次以read only方式打开验证才是上策。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2131409/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2131409/