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;
