Recover data of table from FLASHBACK

In this post , we are going to explain how can you recover the data of a table from the past timestamp with FLASHBACK on



High Level Steps:


1.   Check the flashback is ON
2.   Check the database is in ARCHIVELOG mode.
3.   Get the time when wrong update (update of column without where clause and committed ) was  
      performed.
4.   Select data from table at the timestamp from history.
5.   Make sure that new transactions are not coming  and all transactions on this table are suppressed.
6.   Create a temporary table from the history timestamp.
7.   Validate the data in current table (which has data from the past timestamp) and compare.
8.   If additional rows have been inserted after the time of wrong DML statement, capture the details
      of rows additional column values. store this additional data in another TEMP tables.
9.   Validate the data in current and TEMP tables.
10. Backup the original table (current time).
11. truncate the original table (current time).
12. Insert the data from table which is created with data in history time.
13. Insert the data in the table with additional column values.

Assumptions: 
No other updated have been performed on table and only new data have been inserted.

All Steps :

sql > select inst_id , name, flashback_on from gv$database;
SQL > inst_id | name |flashback_on
SQL> _______ |________|______________
1 |neerajdb|ON select inst_id, sql_id , sql_fulltext from gv$sqltext where upper(sql_fulltext) like '% sql > select inst_id , name, flashback_on from gv$database;
SQL > inst_id | name   |flashback_on
SQL>  _______ |________|______________
     1       |neerajdb|ON

select inst_id, sql_id  , sql_fulltext from gv$sqltext where upper(sql_fulltext) like '%<MENTION SQL STATEMENT SEARCH KEYWORDS IN CAPITAL>%';

With above command you can find the sql_id of a DML statement now find the timestamp of the SQL statement - 

select sql_id, to_char(first_load_time,'DD/MON/YYYY HH24:MI:SS'), to_char(last_load_time,'DD/MON/YYYY HH24:MI:SS'), parsing_schema_name from v$active_session_history where sql_id='&SQLID';

select sql_id, to_char(first_load_time,'DD/MON/YYYY HH24:MI:SS'), to_char(last_load_time,'DD/MON/YYYY HH24:MI:SS'), parsing_schema_name from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='&SQLID';

Now you have the time when the wrong command was executed. Stop the current update in the table. Check current count in table at present and at the time of issue was reported. 

SQL> select count(*) from  neerajdb.emp_order_payment;

SQL> select count(*) from  neerajdb.emp_order_payment as of timestamp  timestamp  '2016-12-08 09:17:21';

SQL> create table neerajdb.order_id_oldtimestamp as select * from neerajdb.emp_order_payment as of timestamp  timestamp  '2016-12-08 09:17:21';

SQL> select count(*) from neerajdb.order_id_oldtimestamp;

SQL> select * from neerajdb.order_id_oldtimestamp;

SQL> select distinct order_id from neerajdb.emp_order_payment;

SQL> select order_id, count(*) from neerajdb.emp_order_payment group by order_id;
-- Support we have got additional 2 order came after the timestamp (when wrong data was updated)  - For Example - '8872334','8872335'

SQL> select count(*) from neerajdb.emp_order_payment where order_id in ('8872334','8872335');

SQL> create table neerajdb.temp2 as select * from neerajdb.emp_order_payment where order_id in ('8872334','8872335');

SQL> select count(*) from neerajdb.temp2;

SQL> create table neerajdb.emp_order_payment_TEMP as select * from neerajdb.emp_order_payment where 1=0;

SQL> select count(*) from  neerajdb.emp_order_payment_TEMP;

SQL> select count(*) from  neerajdb.temp2;

SQL> insert into neerajdb.emp_order_payment_TEMP   select * from neerajdb.order_id_oldtimestamp;

SQL> insert into neerajdb.emp_order_payment_TEMP  select * from neerajdb.temp2;

SQL> commit;

SQL> select count(*) from neerajdb.emp_order_payment_TEMP;  

SQL> select count(*) from  neerajdb.emp_order_payment;

SQL> select order_id, count(*) from  neerajdb.emp_order_payment_TEMP group by order_id;

SQL> select order_id, count(*) from  neerajdb.emp_order_payment group  by order_id;

SQL> select * from neerajdb.emp_order_payment_TEMP;

SQL> select * from  neerajdb.emp_order_payment;

SQL> create table neerajdb.emp_order_payment_CORPTDATA as select * from neerajdb.emp_order_payment;

SQL> select count(*) from neerajdb.emp_order_payment_CORPTDATA;

SQL> -- export the table neerajdb.emp_order_payment  

SQL> Truncate the table neerajdb.emp_order_payment

SQL> insert into neerajdb.emp_order_payment as select * from neerajdb.emp_order_payment_TEMP;

SQL> commit;