Using 10gR2 Flashback Database Restore Point for testing

Oracle 10g R2 introduces a new feature to the already almighty flashback database - Restore points. The concept is the same as for SQL transaction savepoints.

So what is the benefits of this - many I would say, one is for testing. In todays world of SOA testing your integrated systems is harder then ever... first you spend days on setting your data up for your testcases, then you back them up. Run your 5 minute test case, and then you are back to recovering your backups for your 25 systems or so. So here comes Oracle 10g R2 Flashback database to the rescue.

In 10g R1 you needed to flashback to a certain SCN or timestamp, which is easy enough. With savepoints you create a restore point after you have setup your test case. For example:

SQL>create restore point testcase_a_rp guarantee flashback database;

And when you want to flashback to this particular database state, you issue:

SQL> flashback database to restore point testcase_a_rp;

A testcase setup would then be the following setup for each database:

1. Setup the database schema to the state needed prior to test run
2. Create a restore point
3. Run tests
4. Evaluate tests
5. Flashback database to restore point
6. Go to 3 to run test again
   

If you are confused like me, you might forget your savepoint names. That is allright as long as you remember the view name v$restore_point. This view will tell you all about your restore points.

To do the above flashback needs to be turned on. See the documentation for your version of the database for instructions on how to do this or look at this article.

Sphere: Related Content