利用闪回数据库恢复truncate后的表

1.验证是否启用了flashback database
SQL> select flashback_on,force_logging from v$database; 

FLASHBACK_ON       FOR
------------------ ---
NO           NO
2.开启闪回功能和force logging
SQL> shutdown immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
Database mounted.

3.将数据库开启到归档模式
SQL> alter database archivelog; 

Database altered.
-------如果不开启归档模式无法开启闪回功能

4.开启闪回功能
SQL>  alter database flashback on; 

Database altered.

SQL> alter database open; 

Database altered.

5.开启force logging，
SQL> alter database force logging; 

Database altered.

6.验证是否成功开启
SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES           YES



7.连接到scott用户，并新建表t
SQL> conn scott/scott
Connected.
SQL> create table t as select * from dept;

Table created.

8.开启时间
SQL> set time on
22:15:32 SQL> select * from t
22:15:45   2  ;

   DEPTNO DNAME      LOC
---------- -------------- -------------
   10 ACCOUNTING      NEW YORK
   20 RESEARCH      DALLAS
   30 SALES      CHICAGO
   40 OPERATIONS      BOSTON

9.模拟误删除表t
22:16:55 SQL> truncate table t;

Table truncated.

22:17:34 SQL> select * from t; 

no rows selected

10.关闭数据库并启动到mount阶段
22:18:22 SQL> conn / as sysdba
Connected.
22:18:33 SQL> shutdown immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
22:18:43 SQL> startup mount exclusive; 
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
Database mounted.

11.闪回数据库到被删除之前的时间点
22:18:57 SQL>  Flashback Database to timestamp(to_date('2016-01-09 22:15:32','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.

12.只读方式打开数据库
22:21:36 SQL> alter database open read only; 

Database altered.

13.连接scott用户查询表是否回来
22:21:46 SQL> conn scott/scott
Connected.
22:21:55 SQL> select * from t;

   DEPTNO DNAME      LOC
---------- -------------- -------------
   10 ACCOUNTING      NEW YORK
   20 RESEARCH      DALLAS
   30 SALES      CHICAGO
   40 OPERATIONS      BOSTON

14.到处相对应的表
[oracle@enmoedu ~]$ exp scott/scott tables=t file=/u01/app/oracle/t.dmp;

Export: Release 11.2.0.4.0 - Production on Sat Jan 9 22:47:36 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T          4 rows exported
Export terminated successfully without warnings.

15.将数据库再次闪回到truncate后的时间或者scn点，并查看是否存在该表
SQL>   Flashback Database to timestamp(to_date('2016-01-09 22:17:34','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.

SQL> alter database open RESETLOGS;

Database altered.

SQL> select * from scott.t;

no rows selected

16.删除表t，重新导入t表，再次查完成实验
sqlplus scott/scott
SQL> drop table t;

Table dropped.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@enmoedu ~]$ imp scott/scott file=/u01/app/oracle/t.dmp

Import: Release 11.2.0.4.0 - Production on Sun Jan 10 00:15:00 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"          4 rows imported
Import terminated successfully without warnings.
[oracle@enmoedu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 00:15:07 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from scott.t;

   DEPTNO DNAME      LOC
---------- -------------- -------------
   10 ACCOUNTING      NEW YORK
   20 RESEARCH      DALLAS
   30 SALES      CHICAGO
   40 OPERATIONS      BOSTON

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

转载于:http://blog.itpub.net/30604784/viewspace-1974375/