Restore Table to specific Point in Time or SCN using FlashBack Table

Hi all,

This week i decided to share some of Admin related tip. We all came across  the situations when accidentally execution of wrong DELETE or UPDATE statement got committed in the database.

Like Developer thinks that he is working on Test Server but accidentally executed following command to update all rows in Production environment.

Update Emp
set Sal = nvl(Sal,0) + ((nvl(Sal,0) * 15) / 100); 

commit; 

This query was to check the affect of increment in salaries by 15% in test environment only for Sales department but it updates all rows and statement is committed into database.

Now what to do in this situation? With Oracle10g and above you don't have to worry about it using Flashback Table we can restore our Table to specific Time or SCN while database is still online.

See What Oracle Docs says about Flashback Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Any ways let get into the example

Prerequisites:  From Oracle Documentation


The prerequisites for using the Flashback Table feature on a table are as follows:

• Row movement must be enabled on the table. You can enable row movement with the following SQL statement:
  ALTER TABLE table ENABLE ROW MOVEMENT;
• You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
  
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
  
• Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
  

Example:

- Create sample table EMP_FLASH_TEST as copy of EMP table in SCOTT Schema

create table scott.Emp_Flash_test 
as 
select * from scott.emp;

- Execute this command to delete all rows from the table (Also Note the Time of the operation)

delete from scott.emp_flash_test
;

commit; 

- Run this query as DBA and note down the SCN number at the time of the operation (This query will provide information about the operations executed on database This feature called Flasback Transaction Query)

select xid, operation, table_name, start_scn
from flashback_transaction_query
where table_name = 'EMP_FLASH_TEST'
and operation = 'DELETE';

Result would be similar to this
Xid      /                        Operation / TableName /                  START_SCN 
0400200052040000    DELETE    EMP_FLASH_TEST    2664690
0400200052040000    DELETE    EMP_FLASH_TEST    2664690
0400200052040000    DELETE    EMP_FLASH_TEST    2664690

- Enable row movement of the table

alter table scott.emp_flash_test enable row movement;

- Execute Flashback Table command to specific timestamp or SCN 

flashback table scott.emp_flash_test to scn 2664690;

-Verify the result now all rows are restored back to the position before the DELETE statement.

select * from scott.emp_flash_test;

Hope it helps

More info about Flashback Table check here

You can follow me on Twitter as well

Have a nice day,
Baig