Recovering A Table Using Flashback Database on Dataguard

Hi,

If you lost or truncate a table on production, then you can recover it over dataguard very easy. Flashback database must be enabled on Dataguard.

Let’s do an example scenario.

primary:
create table veridata.flashback_test (id number, tarih date default sysdate);
insert into veridata.flashback_test(id) values(1);
insert into veridata.flashback_test(id) values(2);
insert into veridata.flashback_test(id) values(3);
insert into veridata.flashback_test(id) values(4);
insert into veridata.flashback_test(id) values(5);
insert into veridata.flashback_test(id) values(6);
insert into veridata.flashback_test(id) values(7);
insert into veridata.flashback_test(id) values(8);
insert into veridata.flashback_test(id) values(9);
insert into veridata.flashback_test(id) values(10);
insert into veridata.flashback_test(id) values(11);
insert into veridata.flashback_test(id) values(12);
insert into veridata.flashback_test(id) values(13);
insert into veridata.flashback_test(id) values(14);
insert into veridata.flashback_test(id) values(15);
commit;

primary-standby:
select * from veridata.flashback_test;

primary:
truncate table veridata.flashback_test;

standby:
select * from veridata.flashback_test;

After an hour, you find actual truncate time on primary:
select * from VERIDATA.DDL_HISTORY_LOG where action_date >sysdate 10/1440

standby:
alter database recover managed standby database cancel;
shutdown immediate;
startup mount;
flashback database to timestamp TO_TIMESTAMP(‘17.10.2017 09:05:04′,’DD.MM.YYYY HH24:MI:SS’);
alter database open read only;

primary:
create database link DG connect to veridata identified by “*****” using ‘ALFADG’
alter session set global_names=false;
create table flashback_test_recovered as select * from flashback_test@DG;
select * from flashback_test_recovered;

Now, your table is ready on production.

If you have a dataguard, consider enabling flashback database before disaster 🙂