Oracle Flashback!

In these days I’m struggling (studying? fighting? googling?) with Oracle recovery system. I’m trying to recover a database to a precise moment in time, using “recover database until time” clause. Well, apart the fact I’m not able to make it work while two weeks passed along (!!!), today I’ve discovered an interesting functionality, available since version 9i, called Oracle Flashback. It allows you to see a consistent view of the database as it was at a point in the past: while in 10g version is quite simple to use, in 9i it’s a little bit weird :-), but it works.

Imagine you have a table called “project” created by the user “test”: someone kindly executed such command: “DELETE FROM PROJECT” (oops!). All your data is lost! (yeah, he was also in autocommit!). What can you do? No panic, simply connect as system/yourpassword and type:

EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP(‘2006-09-05 11:00:00′,’YYYY-MM-DD HH24:MI:SS’));

SELECT * FROM TEST.PROJECT;

All your stuff is there! Unfortunately, in readonly mode, but you can export and reimport data easily. Then, remember to type:

EXECUTE DBMS_FLASHBACK.DISABLE;

Just to get out from flashback mode ๐Ÿ™‚ Hope this helps!

Advertisements

One thought on “Oracle Flashback!

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