当我们对表进行了truncate操作之后是无法使用闪回删除来进行恢复的，此时我们才会去考虑使用闪回数据库。
但是当我们使用闪回数据库将数据库回退到某个时间点的时候也是要付出一些代价的。比如在下午两点的时候你不小心truncate掉一个很重要的表，而你到两点半的时候才发现这个误操作，在两点到两点半这段时间也已经进行很多其他的数据库操作，那么这时候，如果我们使用闪回数据库来将数据库回退到下午两点时，两点到两点半这段时间的数据就会丢失，那么面对这种问题应该如何解决呢？下面我们通过实验来演示：
1.新建一个用户hh，并赋予dba权限，在hh模式下建立一张测试表flash_t:
HH@orcl 2014-10-12 19:32:02>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 19:34:45>select * from flash_T;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Table created.
HH@orcl 2014-10-12 19:34:45>select * from flash_T;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2. 记录此时的系统时间：
HH@orcl 2014-10-12 19:37:11>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:37:41
SYSDATE
-------------------
2014-10-12 19:37:41
3.把表flash_t truncate掉：
4.创建一张表flash_t_1:
HH@orcl 2014-10-12 19:38:59>create table flash_t_1 as select * from dba_users;
Table created.
HH@orcl 2014-10-12 19:39:11>select count(1) from flash_t_1;
COUNT(1)
----------
44
Table created.
HH@orcl 2014-10-12 19:39:11>select count(1) from flash_t_1;
COUNT(1)
----------
44
现在假设我们发现了误truncate掉表flash_t了，这里就存在两种做法。
第一种是直接将数据库闪回到2014-10-12 19:37:41时间点，而在这个时间点到发现错误的时间点之间的数据不要了，然后直接打开数据库，那么误truncate掉的flash_t就可以恢复了。
第二种是在我们将数据库闪回到2014-10-12 19:37:41时间点之后，希望这个时间点之后更新的数据可以保留下来。
首先我们看看第一种做法：
把数据库闪回到2014-10-12 19:37:41时间点：
在这之前我们验证下如果表被truncate之后是无法通过闪回删除来恢复的：
HH@orcl 2014-10-12 19:44:31>alter table flash_t enable row movement;
Table altered.
HH@orcl 2014-10-12 19:44:51>flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
Table altered.
HH@orcl 2014-10-12 19:44:51>flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
（错误显示表的定义已经被改变了，所以无法进行闪回删除）
使用闪回数据库的功能将数据库闪回到特定时间点（注意使用闪回数据库功能进行闪回一定要将数据库处于mount）：
HH@orcl 2014-10-12 19:44:58>conn /as sysdba
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
使用resetlogs打开数据库：
SYS@orcl 12-OCT-14>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
查询表flash_t的数据库是否恢复：
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以看到，在时间点2014-10-12 19:37:41之后进行的操作已经失效：
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
select count(1) from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
select count(1) from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
下面我们进行如下的演示看看结果：
（前四个步骤是一样的）
记录系统时间：
HH@orcl 12-OCT-14>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
HH@orcl 2014-10-12 19:54:23>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:54:27
Session altered.
HH@orcl 2014-10-12 19:54:23>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:54:27
把表flash_t truncate掉：
将数据库闪回到2014-10-12 19:54:27 ：
HH@orcl 2014-10-12 19:56:42>conn /as sysdba
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:54:27','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
应用归档将数据库进行恢复：
SYS@orcl 12-OCT-14>recover database;
Media recovery complete.
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:54:27','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
应用归档将数据库进行恢复：
SYS@orcl 12-OCT-14>recover database;
Media recovery complete.
查看数据是否都恢复了：
HH@orcl 12-OCT-14>select * from flash_T;
no rows selected
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
COUNT(1)
----------
44
no rows selected
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
COUNT(1)
----------
44
可以看到，这个结果和没有进行闪回之前是一样的，因为我们进行了应用归档日志进行恢复的操作，那么oracle就会根据归档中的记录将数据库恢复到最新的状态，所以结果和没有闪回是一样的。因此这是一次失败的恢复。
接下来进入第二种方式恢复数据：
当我们发现有误操作了之后，我们可以把之后进行的插入操作的表导出来，将数据库闪回到特定时间点之后再将表进行导入操作：
当我们发现有误操作了之后，我们可以把之后进行的插入操作的表导出来，将数据库闪回到特定时间点之后再将表进行导入操作：
创建表flash_t:
HH@orcl 2014-10-12 20:20:51>select * from tab;
no rows selected
HH@orcl 2014-10-12 20:20:54>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 20:21:26>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
no rows selected
HH@orcl 2014-10-12 20:20:54>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 20:21:26>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
记录当前系统时间：
HH@orcl 2014-10-12 20:21:33>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 21:15:51
SYSDATE
-------------------
2014-10-12 21:15:51
truncate表flash_t:
创建表flash_t_1:
HH@orcl 2014-10-12 20:23:30>create table flash_t_1 as select * from dba_users;
Table created.
HH@orcl 2014-10-12 20:24:05>select count(1) from flash_t_1;
COUNT(1)
----------
44
Table created.
HH@orcl 2014-10-12 20:24:05>select count(1) from flash_t_1;
COUNT(1)
----------
44
此时，你发现了truncate掉flash_t这张表的误操作，于是想通过闪回数据库把数据库闪回到truncate操作之前，但是在这之前，要先把表flash_t_1导出：
[oracle@localhost app]$ exp hh/hh file=/u01/app/expimp/flash_t_1.dmp log=/u01/app/expimp/flash_t_1_exp.log tables=flash_t_1;
Export: Release 11.2.0.1.0 - Production on Sun Oct 12 21:19:26 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table FLASH_T_1 44 rows exported
Export terminated successfully without warnings.
Export: Release 11.2.0.1.0 - Production on Sun Oct 12 21:19:26 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table FLASH_T_1 44 rows exported
Export terminated successfully without warnings.
[oracle@localhost expimp]$ ls
flash_t_1.dmp flash_t_1_exp.log
flash_t_1.dmp flash_t_1_exp.log
将数据库闪回到2014-10-12 21:15:51：
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 21:15:51','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SYS@orcl 12-OCT-14>alter database open resetlogs;
Database altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 21:15:51','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SYS@orcl 12-OCT-14>alter database open resetlogs;
Database altered.
查询flash_t的数据是否恢复：
SYS@orcl 12-OCT-14>conn hh/hh
Connected.
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Connected.
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
HH@orcl 12-OCT-14>select * from flash_t_1;
select * from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
select * from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
下面应用Imp把flash_t_1导入到数据库中：
[oracle@localhost expimp]$ imp hh/hh file=/u01/app/expimp/flash_t_1.dmp log=/u01/app/expimp/flash_t_1_imp.log full=y;
Import: Release 11.2.0.1.0 - Production on Sun Oct 12 21:27:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing HH's objects into HH
. importing HH's objects into HH
. . importing table "FLASH_T_1" 44 rows imported
Import terminated successfully without warnings.
Import: Release 11.2.0.1.0 - Production on Sun Oct 12 21:27:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing HH's objects into HH
. importing HH's objects into HH
. . importing table "FLASH_T_1" 44 rows imported
Import terminated successfully without warnings.
可以看到表flash_t_1已经成功导入到hh模式中了。