Flashback Table to Timestamp 11gR2

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.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s