Thanks for the question, Paul.
Asked: February 02, 2018 - 2:36 pm UTC
Last updated: February 05, 2018 - 2:25 am UTC
Version: 11g
Viewed 1000+ times
You Asked
He have a bunch of junit tests that interact with the DB. As we all know when you write tests that interact with a db you always have the problem of random DB state that can make these types of DB tests brittle. Our DB is very big and has multi schemas and many stored procs. I say this because simply running a db clean script before every test is not a feasible option for us. Looking for a better solution then this.
Our Deployment:
- We have 3 oracle schemas running on the same db
Issues:
- Need a fast way to snap our schemas back to a set state.
- Right now 1 server hosts our oracle DB with our 3 schemas. Would like a way to have many instances of these 3 schemas on a given box. Maybe multi oracle DB's or maybe there is a better way to do this within a given Oracle DB.
Goal:
- Have many instances of our 3 oracle schemas running on a given box. The more the better as our tests will mostly go pretty easy in terms of load on oracle.
- Have a way to quickly bring back our db schemas to a given state.
and Connor said...
The easiest way to "rewind" a database is with Flashback database.
In 12c, you could put those schemas into their own pluggable database, and just flashback at this level, but since you're on 11g, the best you can do is the entire database.
But all you need to do is:
Before tests start:
- create guaranteed restore point ABC;
Run your tests, gather data:
After tests:
- Bring database to mount mode (typically I'l shutdown and restart to ensure I've booted off any connections)
- flashback database to ABC;
- open database resetlogs
You can size an instance quite small (~300MB of RAM) so you should be able to accommodate many instances on a box. And you are then nicely setup for pluggable database features in 12c.
Is this answer out of date? If it is, please let us know via a Comment