Fastest way to reset oracle database for testing

Question

I'm doing automated tests for my application using Oracle Database and I want to reset/recreate the database between some steps.

Which is the best/fastest approach to reset an Oracle Database?

Answer 1

Since you said you want to reset the entire database, Flashback database is probably the tool for you. Flashback database uses archived logs (so you must first be in archive log mode), and flashback logs in order to undo everything on the database to restore the database back to either a specific point in time or to a named restore point.

Flashback log setup is that you configure the retention time (in minutes) as well as the max size of the log location.

Configure the Fast Recovery Area to store the Flashback logs:

alter system set db_recovery_file_dest='L:\Oracle\FRA' scope=both;
alter system set db_recovery_file_dest_size=100G scope=both;
alter system set db_flashback_retention_target = 1440 scope=both; --1 day

Turn on flashback database:

alter database flashback on;

Create a restore point, which is nothing more than a pointer to the SCN of when the restore point was taken:

create restore point before_changes;

You can create as many restore points as you want, and they don't take any additional space since they are merely pointers. That said, restore points will roll off if either the db_flashback_retention_target or db_recovery_file_dest_size parameters are reached (whichever comes first).

You can prevent the rolling off of these restore points by creating a flashback guarantee restore point. However, care must be taken with guarantee flashback database restore points since your database will be inaccessible if either parameter is reached (in which case, you'll have to either drop the guarantee flashback database restore point, or increase the value of these parameters.

After you have run your tests, you can flashback via:

shutdown immediate;
startup mount;
flashback database to restore point before_changes;
alter database open resetlogs;

You can read about flashback database and restore points from the Oracle 11.2 docs.