3

I'm an Oracle newb.

The systems in question are strictly for developers to test against. We fire them up and then blow them away afterwards. Problem is that this takes as long as 8 minutes per build which is costing us severely in CI. Four minutes to erase and four minutes to rebuild. Also problematic is that we can only run one set of tests at a time because we only get one schema to test against.

One thing to consider is the possibility that a developer might check in something with screwed up transaction management, like something that does real commits with no transactional rollback.

What we were thinking of as a solution was to keep a copy of the schema that is kept up to date via automated means. Then do a straight copy of the entire schema to a unique name. Do the test run against the copy and then asynchronously remove it at the end.

Generally speaking, what is a good approach for this? We are working with Oracle running on our local machines in a Docker container. What I've been experimenting with is using the expdp/impdp tools. The Docker container adds a pain in the 6 O'clock, but I've been able to get limited success with this technique. In a nutshell I do the copy but then I get errors to the effect that several items pre-exist despite copying to a new name. I expect that this has something to do with the close association between Oracle Schemas and User's. I'll post examples of the errors I'm seeing as soon as I have a new test run.

Improve this question
2
  • We are also working on an alternate track which would generate unique schema names via Jenkins build number.
    – user447607
    Commented Aug 29, 2016 at 19:38
  • What version and edition of Oracle are you using? Creating snapshot copy databases as one of the answers suggests is a good option. If you're not using 12.1 with the multitenant option, however, you might want configure the database so you can do a flashback to a guaranteed restore point which is your "gold". That may not be a 20 second solution but it is probably better than 8 minutes.
    – Justin Cave
    Commented Aug 30, 2016 at 5:48

1 Answer 1

Reset to default
3

The 12c Multitenant option is a nice tool for this.

Create a "master" pluggable database that is your starting point, and you can create other pluggable databases by cloning this PDB.

When you clone a PDB, you have the option to clone the PDB as a "snapshot copy". This does not actually copy the files, but uses storage snapshots, and that is what makes this feature great. For example, cloning a 111 GB database took 20 seconds in this example: Snapshot clone of a PDB using ACFS

When I experimented with it, smaller databases took even less time to be cloned. When you do not need the database anymore, you can just simply drop it.

Also you are not limited to 1 schema, because you can create many clones and use them at the same time concurrently, up to 252 PDBs.

Note that, the Multitenant option is an extra cost option for Enterprise Edtition databases. So you need the proper license to use it.

SNAPSHOT COPY

Improve this answer
1
  • I'll check to see if we have that license but if we don't, we'll probably end up with expdp/impdp idea. There's no point in paying extra just for test stuff.
    – user447607
    Commented Aug 30, 2016 at 18:13

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.