Recover table on Stand by side using flash back

We had a task to flashback the stand by database  to  recover the dropped object/table. Please find the steps  carried out.

 

1)      Create a table on the primary database or use an existing table;

 

2)      Load table with some data;

 

3)      Accidently drop table or deleted certain records;

 

4)      Request table data to be restored;

 

5)      DBA team to flashback standby database, find missing data and reload into primary database – timed test.

 

 

Create a table  on Primary

create table FB_WO828610 (Name Varchar2(20));

 

Insert  Values to a table

Insert into FB_WO828610 values ('LCH1');

Insert into FB_WO828610 values ('LCH2');

Insert into FB_WO828610 values ('LCH3');

Insert into FB_WO828610 values ('LCH4');

Insert into FB_WO828610 values ('LCH5');

Insert into FB_WO828610 values ('LCH5');

Insert into FB_WO828610 values ('LCH7');

Insert into FB_WO828610 values ('LCH8');

Insert into FB_WO828610 values ('LCH9');

Insert into FB_WO828610 values ('LCH10');

Insert into FB_WO828610 values ('LCH11');

Insert into FB_WO828610 values ('LCH12');

 

Commit;

 

 

Switch logfiles;

alter system switch logfile;

 

Count the table data for a confirmation on Primary

Select count (*) from FB_WO828610;

 

Note the time stamp before you drop the table.

select systimestamp from dual;

 

SYSTIMESTAMP

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

23-FEB-18 15.30.30.866715 +00:00

 

 

On Standby side Database Open in Read only mode;

alter database open read only;

 

Count the table data for a confirmation on Stand by  

Select count (*) from FB_WO828610;

 

Then drop table on Primary

drop table FB_WO828610;

 

Check for existence of the table on both primary and standby.

select count (*) from FXC_FINANCIAL.FB_WO828610;

 

Bring Standby DB to mount stage

shutdown immediate;

startup mount;

alter database recover managed standby database cancel;

flashback database to timestamp to_date('23-FEB-2018 15.30.30','DD-MON-YYYY HH24:MI:SS');

 

Open Standby database on Read Only mode

alter database open read only;

 

Then export  and import it in to Primary side. Do not use impdp utility

 

exp  file=/nfs/oradump_dba/apex/expdp_FB_WO828610.dmp log=/nfs/oradump_dba/apex/expdp_FB_WO828610.log tables=FB_WO828610;

imp  file=/nfs/oradump_dba/apex/expdp_FB_WO828610.dmp log=/nfs/oradump_dba/apex/impdp_FB_WO828610.log tables=FB_WO828610;