I have an Oracle database over which I have full control. What I am doing is performing load testing on our application, which uses Oracle as a database. To do this, I initially set the database up with some dummy data. I'm calling this "Point A".

The load testing will consume this data, and I will have to restore the data so it looks exactly like it was in Point A before I can re-run them. The hard way to do this is using Data Pump, which takes a long time and seeing how I need to backup several schemas it's rather complex.

I was wondering if it was possible just to issue a BACKUP DATABASE on RMAN after I have the data set up. Then I would run my tests, and restore it using something like RESTORE DATABASE FROM TAG <somedate>, and that would be all I need to get my tests going again. There should be no changes to the control file or anything like that.

Is this a safe way to go about doing this?


There is a feature specifically for that:

 select NAME,SCN,TIME from v$restore_point; 
 exec perform_my_test1;
 DROP RESTORE POINT before_test1 ; 

This is a database's feature, not RMAN's (commands are for sqlplus), see doc for the full setup and disclaimers.

To more precisely answer your question, RMAN can also be used for the task. The general script is:

run { 
  set until scn 123123123 ;
  restore database /* from tag ... */;
  recover database;

Don't use bare RESTORE DATABASE FROM TAG because without SET UNTIL it doesn't work as you'd expect in some scenarios.

I wouldn't say any of these two solutions are inherently safe, both have as many pitfalls as any other Oracle's advanced features, so you need to do some research upfront.

| improve this answer | |
  • There's a command exec perform_my_test1; - where did perform_my_test1 get defined? – Confluence Oct 29 '13 at 5:52
  • 1
    Just usual Oracle disclaimer - Flashback database is Enterprise Edition feature. – Mindaugas Riauba Oct 29 '13 at 6:12
  • @Confluence you state in your question that you want to perform some testing before restoring - he's just added that as a placeholder for your testing. – Philᵀᴹ Oct 29 '13 at 6:47
  • Real Application Test anyone? – Munchi Oct 29 '13 at 7:01
  • Today I learned OPEN DATABASE RESETLOGS is a dangerous operation. I had this stuff in ASM and reorganized (deleted and recreated) some datafiles, and the end result was a major mess. I will try the FLASHBACK operation soon. – Confluence Oct 30 '13 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.