Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. I will demonstrate below how to do flashback a table to a point in time. But first you have to enable flashback.
From 11gR2 onwards you don’t have to shutdown the database to enable Flashback. You can do it online. To enable flashback in 11gR2 do the below steps.
1. alter system set db_recovery_file_dest=’+FRA’ scope=both sid=’*’;
2. alter system set db_recovery_file_dest_size=’400G’ scope=both sid=’*’;
3. select flashback_on from v$database;
FLASHBACK_ON
——————
NO
4. alter database flashback on;
select flashback_on from v$database;
FLASHBACK_ON
——————
YES
_______________________________________________________
— For a table to be flash-backed you need to have row movement enabled on it. We have a table here called Employees in Schema HR
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
— Check current SCN and Database Time
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;
4784136875 2012-04-30 8:30:00
— Check the current total no. of records in the table
SELECT COUNT(*) FROM HR.EMPLOYEES
Count(*)
126
— Check the current total no. of records in the table as it existed half an hour back at 8:00 AM
SELECT COUNT(*) FROM HR.EMPLOYEES AS OF TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
Count(*)
127
This means there is one record was deleted. We need to flashback the table as it existsed at 8:00AM
FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP TO_TIMESTAMP(‘2012-04-30 8:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);
SELECT COUNT(*) FROM HR.EMPLOYEES
Count(*)
127
Now we can see that the record are displaying as 127. Which means table is back to the state before the record was deleted.
