Flashback in 1,2,3

No need to say what flashback means as we all seen atleast one movie that either takes us to our previous memories or the movie actors itself will go back in their memories!!!

Well, the same concept applies for Oracle Database also.

In simple terms, you just feel like to revert all your database changes to certain point of time before you either you messed up the data (well, by doing some kinda testing) or altering some structure of the database.

Or, can be used to prepare your database to restore to the point where you wanted to be after allowing other departments to work on the database.

OK, enough theory over here. Lets get to practical as to how easy it is:

Pre-Req:

1. Database needs to be running in Archive Log Mode (no need to be in archive log from the day one. But only when you want to start FLASHBACK till the time you are done with your FLASHBACK).

2. Some diskspace to record/log all the objects (that are only being modified) old images/copy while the database is in FLASHBACK mode.

Steps:

If the Database is already in ARCHIVELOG then move on to step #4:

1. shutdown immediate;

2. startup mount;

3. alter database archivelog;

4. alter system set db_recovery_file_dest_size=2G;

Note: 2G is to store all the modified object image copies before the modification is done.

5. alter system set db_recovery_file_dest='/location_where_you_want_the_FlashBack_Log_should be created';

6. alter database flashback on;

7. alter database open;

8. create restore point started_messing_it_up;

Note: remember the name that you are giving here. In this case it's "started_messing_it_up"

Let the users start using the database for a while now.

Once you are ready to Flashback/Re-Wind to the point where you wanted to be:

1. shutdown immediate;

2. startup mount;

3. flashback database to restore point started_messing_it_up;

Note: the name is the one that you have given at the time of creating the restore point.

4. alter database open resetlogs;

Now, your database is just like how it was before creating the restore point.