前提：

- Standby Database Must be in Flashback database mode. 
 - Time at which Drop/Truncate/Delete Table happened should be within the db_flashback_retention_target and all the flashback and archive logs should be available

?

?

在dataguard备库上找回在主库上被错误的Drop/Truncate/Delete 掉的Table

参考文章：
How To Recover From A Drop/Truncate/Delete Table Done On Primary Using Flashback On A Standby Database (文档 ID 958557.1)

主库：

 
[oracle@hosta ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Jul 31 22:08:19 2015

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


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

SQL> select sysdate from dual;

SYSDATE
---------
31-JUL-15

SQL> show parameter format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
nls_date_format                      string
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
star_transformation_enabled          string      TRUE
SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2015-07-31 22:10:00

SQL> select count(*) from scott.test_tab_1 ;

  COUNT(*)
----------
      2566

SQL> truncate scott.test_tab_1 ;
truncate scott.test_tab_1
              *
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword


SQL> truncate table scott.test_tab_1 ;

Table truncated.

SQL> 

 






备库：

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440  ------->默认的设置，1440分钟，也就是一天。
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 356M
memory_target                        big integer 356M
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate; 
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  372449280 bytes
Fixed Size                  1313484 bytes
Variable Size             322962740 bytes
Database Buffers           41943040 bytes
Redo Buffers                6230016 bytes
Database mounted.
SQL> flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database  cancel;

Database altered.

SQL> flashback database to timestamp to_date('2015-07-31 22:10:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from scott.test_tab_1 ;

  COUNT(*)
----------
      2566

SQL> exit

 

[oracle@hostb SBDB1]$ export NLS_LANG=american_america.AL32UTF8
[oracle@hostb SBDB1]$ exp system/oracle file=/home/oracle/test_tab_exp_0730.dmp tables=scott.test_tab_1

Export: Release 11.1.0.7.0 - Production on Fri Jul 31 22:20:03 2015

Copyright (c) 1982, 2007, Oracle.  All rights reser