oracle时间戳找回数据库,【备份恢复】闪回数据库（三）基于时间戳闪回数据库...

基于时间戳闪回数据库1)创建测试表，并插入测试数据

SYS@ORA11GR2>conn scott/tiger

Connected.

SCOTT@ORA11GR2>create tablefbdb_timeas select * fromfbdb_scnwhere 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;

IDSCN DD

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

11874151 2016-10-01 07:41:40

21874195 2016-10-0107:42:43

31874285 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 Area730714112 bytes

Fixed Size2256832 bytes

Variable Size452984896 bytes

Database Buffers272629760 bytes

Redo Buffers2842624 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;

IDSCN DD

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

11874151 01-OCT-16

21874195 01-OCT-16

31874285 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 Area730714112 bytes

Fixed Size2256832 bytes

Variable Size452984896 bytes

Database Buffers272629760 bytes

Redo Buffers2842624 bytes

Database mounted.

07:50:45 SYS@ORA11GR2>flashback databaseto timestamp to_date('2016-10-0107: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;

IDSCN DD

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

11874151 01-OCT-16

21874195 01-OCT-16

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

Connected.

SCOTT@ORA11GR2>select * from

scott.fbdb_time;

IDSCN DD

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

11874151 2016-10-01 07:41:40

21874195 2016-10-0107: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 Area730714112 bytes

Fixed Size2256832 bytes

Variable Size452984896 bytes

Database Buffers272629760 bytes

Redo Buffers2842624 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;

IDSCN DD

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

11874151 2016-10-01 07:41:40

21874195 2016-10-01 07:42:43

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

only方式打开验证才是上策。