7

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?

Nielsen Martins Gonçalves
  • 342
  • 6
  • 12
  • 1
    Disagree with the person who voted this as belonging on DBA,SE. Automated test Fixtures are definitely a developer's concern. – APC Apr 25 '17 at 14:49
  • What's the scope of the testing? How much data are we talking? How often do you want to initialize (what's timescale of "between some steps")? – APC Apr 25 '17 at 14:51
  • There are large amount of data. I'm currently testing an ERP system, and there are about 150 tables already. I've already tried some alternatives: in-memory (failed), data-pump (expdp and impdp), drop the user (cascade) and reapply all scripts to create/generate data again. – Nielsen Martins Gonçalves Apr 25 '17 at 16:00
  • @NielsenMartinsGonçalves In my experience dropping the user and reapplying the scripts is the best solution. It lets you properly use version control and truly understand your schema and data. Is there a specific problem preventing you from using that approach? – Jon Heller Apr 25 '17 at 19:58
  • @JonHeller The time took to run the scripts is too long, there are several suites and doing this between the suits execution makes the time necessary to run all tests inviable – Nielsen Martins Gonçalves Apr 26 '17 at 16:36

1 Answers1

7

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.

Kris Johnston
  • 718
  • 5
  • 15
  • Thank you, I think this is what I need. I will try this and if it works I will accept your answer. – Nielsen Martins Gonçalves Apr 26 '17 at 16:39
  • Hi @Kris , could you take look at my question? I dont have flashback feature https://stackoverflow.com/questions/54274625/restore-or-reset-oracle-database-no-flashback-features – Robbi Nespu Jan 20 '19 at 08:10
  • @Kris Any hints for improving oracle startup / flashback database? See my question https://stackoverflow.com/questions/56526595/tune-oracle-database-for-faster-startup-flashback – MRalwasser Jun 11 '19 at 09:11