We have lots of self-built high performance Oracle 12c database servers with two quite large instances (OLTP and data warehouse) for development purposes. Our PL/SQL developers sometimes have to redefine table structures, update millions to billions of rows, create/test new indexes and so on. At the moment everytime the update scripts run and the data is changed, we have to re-import all tablespaces, recreate replication, etc. because we need to test the updates again. This procedure takes about 3-5 hours (much of it running in the background) until the developers can access the server again. In the meantime they switch to another, unchanged server to test again, which, of course, is then unusable after the update (repeat as often as needed...).

My teams tasks include recreating the databases for these exact purposes, which is annoying, to say the least.

I'm trying to come up with some alternatives and have looked into creating RMAN backups prior to the tests, ARCHIVELOG-mode, huge UNDO tablespaces or retention time, FLASHBACK RECOVERY, etc. Nothing I've come up with is exactly what I need, I think.

Mind: I've got very little DBA-experience and all of that little experience is self-taught or learning by doing.

So what I need or try to comprehend is, what techniques could I use to reset the database (or the changed tables / data) to some point in the past (maybe even five minutes ago), without having to re-import the database? I think ARCHIVELOG mode and restoring the changed tables / data might be the best way, but I'm not remotely sure.

Could someone point me in the right direction?

2 Answers 2


If you can coordinate the activity, then using guaranteed restore points (for flashback database) is exactly what you need. I do it for all my testing (in-fact did that 4 times yesterday while testing a complex multi-step process) and even my developers/QA uses it too when necessary.

  1. You create a guaranteed restore point,
  2. run the scripts.
  3. Validate and save results
  4. flashback database to the restore point taken before the scripts began.
  5. lather. rinse. repeat.

However, you must note that flashback database affects whole database, so if more than one person is checking their independent scripts, you will need to coordinate activity.

Once you get this, it is a piece of cake really and unlike PDB (as suggested above which may require additional licenses).

  • Thank you too :). Do you have any information on how I could maybe flashback specific tables / schemas only? And how long does it take you to restore your DB? We have around 550 tables using ~1500 packages.
    – Lenniey
    Commented Feb 9, 2017 at 12:29
  • As i mentioned, it is for the whole db. The time it takes is not based on your DB size, but the amount of changes you do. We successfully flashback 4-5TB databases with change amounting to 200GB or so during our testing multiple times a year. It takes time, but it works very well. If you need more assurance, this is exactly what Oracle does under the table when you open a DR db in snapshot standby and then reinstate it. My experience on this is for 11gr2 and 12c versions both.
    – Raj
    Commented Feb 9, 2017 at 12:55
  • Alright, usually we have way smaller changes in data, let's say about ~250MB tops on two DBs with something like 400GB each. So it should work very well, I'll give it a try!
    – Lenniey
    Commented Feb 9, 2017 at 13:00
  • I tested your steps and it seems to work great, thank you very much! I'll accept this as answer.
    – Lenniey
    Commented Feb 9, 2017 at 14:54

This sounds like something where pluggable databases could be very useful. You set up a 'standardised' test PDB and create a copy for each test. Further copies can be created for tests in parallel. Once the test is successful a new 'standard' PDB is created and the cycle continues.

I haven't used the techniques as we aren't currently on 12c here but this is one of the scenarios where we are expecting to be able to improve the testing cycle.

  • Thank you for your answer! I'll look into pluggable databases then. Could you elaborate (briefly) how you test your application on your Oracle version right now?
    – Lenniey
    Commented Feb 9, 2017 at 12:24
  • @Lenniey - the nature of our application means that we don't currently have the need to test every component through a full cycle when changes are made. The components are generally discrete within a certain range. However, for testing, the benefits of PDB look like they could be useful in our case. Commented Feb 9, 2017 at 13:34
  • I checked PDB and it sounds awesome, but we have to create the databases with special flags on, so that's a thing for future tests, but thank you, too!
    – Lenniey
    Commented Feb 9, 2017 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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