Retrieve the wrong Drop/truncate/delete table from the main library on the Dataguard repository

Premise:

-Standby database must is in Flashback database mode.  -time at which Drop/truncate/delete Table happened should is within the db_flashback_retention_target and all the Flashba CK and archive logs should be available

Retrieve the wrong drop/truncate/delete from the main library on the Dataguard Repository table reference article: How to Recover from A drop/truncate/delete table do on Primary  Using Flashback on A Standby database (document ID 958557.1) Main library: [[email protected] ~]$ sqlplus/as sysdbasql*plus:release  11.1.0.7.0-production on Fri Jul 22:08:19 2015Copyright (c) 1982, D, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.1.0.7.0-productionwith The partitioning, OLAP, Data Minin G and Real Application testing optionssql> select Sysdate from dual; Sysdate---------31-jul-15sql> Show parameter FormatName TYPE VALUE-------------- ---------------------------------------------------------------Log_archive_format string%t_%s_%r                   . Dbfnls_date_format Stringnls_time_format Stringnls_time_tz_format              Stringnls_timestamp_format Stringnls_timestamp_tz_formatStringstar_transformation_enabled string truesql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:s S '; Session altered. Sql> select Sysdate from dual;  Sysdate-------------------2015-07-31 22:10:00sql> Select COUNT (*) from scott.test_tab_1; COUNT (*)----------2566sql> truncate scott.test_tab_1; truncate scott.test_tab_1 *error at line 1:ora -03290:invalid truncate command-missing CLUSTER or table keywordsql> truncate TABLE scott.test_tab_1; Table truncated. sql>:sql> Show parameter TargetNAME TYPE VALUE--------------------------- --------------------------------------------------archive_lag_target integer 0db_flashback_retenti On_target integer 1440-------> Default settings, 1440 minutes, that is, one day.                    Fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target           Big integer 356mmemory_target             Big integer 356mpga_aggregate_target Big integer 0sga_target big in Teger 0sql> Recover managed standby database cancel; Media recovery complete. sql> shutdown immediate; Ora-01109:database not OpenDatabase dismounted. ORACLE instance shut down. Sql> Startup Mount;oracle instance started. Total System Global area 372449280 bytesfixed size 1313484 bytesvariable size 322962740 byte Sdatabase buffers 41943040 Bytesredo buffers 6230016 bytesdatabase 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 activesql> 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 (*)----------2566sql> exit [[email protected] sbdb1]$ export Nls_lang=american_america. Al32utf8[[email protected] sbdb1]$ exp system/oracle file=/home/oracle/test_tab_exp_0730.dmp tables=scott.test  _tab_1export:release 11.1.0.7.0-production on Fri Jul 22:20:03 2015Copyright (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.1.0.7.0-productionwith The partitioning, OLAP, Data Mini NG and Real application testing optionsexport done in Al32utf8 character set and Al16utf16 NCHAR character Setabout to exp ORT specified tables via conventional Path ... Current user changed to SCOTT. . Exporting table test_tab_1 2566 rows Exportedexport terminated successfully without warnings.  [[email protected] sbdb1]$

Standby Library:

[[email protected] sbdb1]$ sqlplus/as sysdbasql*plus:release 11.1.0.7.0-production on Fri Jul 22:26:46 2015Co  Pyright (c) 1982, D, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.1.0.7.0-productionwith The partitioning, OLAP, Data Minin G and Real application testing optionssql> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. Sql> Startup Mount ORACLE instance started. Total System Global area 372449280 bytesfixed size 1313484 bytesvariable size 322962740 byte Sdatabase buffers 41943040 Bytesredo buffers 6230016 bytesdatabase mounted. sql> recover standby Database;ora-00279:change 1156098 generated at 07/31/2015 22:10:03 needed for thread 1ora-00289: Suggestion:/home/oracle/archive/sbdb1/1_100_884907736.dbfora-00280:change 1156098 for thread 1 are in sequence #100Spec Ify log: {<ret>=suggested | AUTO | Cancel}autoorA-00279:change 1156325 generated at 07/31/2015 22:14:44 needed for thread 1ora-00289:suggestion:/home/oracle/archive/s Bdb1/1_101_884907736.dbfora-00280:change 1156325 for thread 1 are in sequence #101ORA -00278:log file '/home/oracle/archiv E/SBDB1/1_100_884907736.DBF ' no longerneeded for the Recoveryora-00279:change 1156336 generated at 07/31/2015 22:15:03 N eeded for thread 1ora-00289:suggestion:/home/oracle/archive/sbdb1/1_102_884907736.dbfora-00280:change 1156336 for Thread 1 is in sequence #102ORA -00278:log file '/home/oracle/archive/sbdb1/1_101_884907736.dbf ' no longerneeded for this Recoveryora-00279:change 1156346 generated at 07/31/2015 22:15:13 needed for thread 1ora-00289:suggestion:/home/oracle /archive/sbdb1/1_103_884907736.dbfora-00280:change 1156346 for thread 1 are in sequence #103ORA -00278:log file '/home/ora CLE/ARCHIVE/SBDB1/1_102_884907736.DBF ' No longerneeded for this recoveryora-00308:cannot open archived log '/home/ oracle/archive/sbdb1/1_103_884907736. dbf ' ora-27037:unable to obtain file Statuslinux error:2: No such file or directoryadditional information:3sql> alte R database recover managed standby database disconnect from session;alter database recover managed Standby database Discon Nect from Session*error in line 1:ora-01153:an incompatible media recovery is activesql> ALTER DATABASE recover manage d standby Database Cancel;database altered. sql> ALTER DATABASE recover managed standby database using current logfile disconnect from Session;database altered. Sql>

Retrieve the wrong Drop/truncate/delete table from the main library on the Dataguard repository