Restore table data with Flashback Query in Oracle

Restore table data with Flashback Query in Oracle

Here is the example of table which is deleted by mistake and you can recovered this table from FLASBBACK Query.
Example will explain the Flashback Query concept and use. Flashback Query dependent on UNDO_RETENTION parameter.
So do recovery as quick as you can.

1. Check the no of rows in table

SQL> select count(*) from employee_bkp;

COUNT(*)
———-
101436

2. Get the current SCN Number and timestamp

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate, dbms_flashback.get_system_change_number() scn from dual;

DDATE SCN
——————– ———-
05-09-2018 12:46:21 2011955

3. Delete the table for shown in example (informed by application team accidentally deleted at what time).

SQL> delete from employee_bkp;
101436 rows deleted.

SQL> commit;
Commit complete.

4. Check the actual table data present after delete command.

SQL> select count(*) from employee_bkp;

COUNT(*)
———-
0

5. Different way to check the table data with flashback query:

--Check the table data with flashback query using Timestamp parameter.
SQL> select count(*) from employee_bkp as of timestamp to_timestamp('05-09-2018 12:45:00','dd-mm-yyyy hh24:mi:ss');
COUNT(*)
----------
101436

–Check the table data with flashback query using SCN number.
SQL> select count(*) from employee_bkp as of scn 2011955;
COUNT(*)
———-
101436

–Check the data present if it happens 90 min before. you can specify your time as you wanted.
select count(*) from employee_bkp as of timestamp (systimestamp -interval ’90’ minute);
COUNT(*)
———-
101436

Restore of table depends upon two cases:
1. Complete table restore
2. Some data of table restore

Case 1. If complete table is deleted then you can simple restored complete table as follows:

-- Insert into same table as follows:
insert into employee_bkp as select * from employee_bkp as of scn 2011955;

Case 2. If some rows need to recovered then first create a backup of table then you can use find deleted rows and insert into it by using minus/not in operator
— create new table with traditional method CTAS

SQL> create table emp_bkp as select * from employee_bkp as of timestamp to_timestamp('05-09-2018 12
:45:00','dd-mm-yyyy hh24:mi:ss');

SQL> select count(*) from emp_bkp;

COUNT(*)
———-
101436

SQL> select * from emp_bkp;
minus
select * from employee_bkp;
Note: Minus will give you detail of only distinct rows, so you need to find the count of rows how many time it need to insert into production table. If table having any primary or unique id then it will give you exact count.

Note:
Convert time stamp to SCN and SCN to Timestamp

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.