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:
Just to get out from flashback mode 🙂 Hope this helps!