This is on a dev server for multiple different envs of one application.

e.g. a copy of production as of last night, a copy of production as of a week ago, etc etc

Does it make more sense to have 1 instance of Oracle with 8 schemas (one for each application env) or for 8 instances each with 1 schema?

We are doing backups with datapump instead of RMAN.

The key difference to this question is that in this case it is one application team with multiple instances/versions of their application and that is talk about multiple applications and also RAC.

  • OT: You really should not be using datapump as a replacement for RMAN backups. Datapump certainly has its place, but a backup for the sake of disaster recovery is not one of them. – Kris Johnston Jul 19 '17 at 18:49
  • It is just a dev env so we dump prod and load into the dev envs. We have RMAN for our T-1 env. It was my understanding that you can't use RMAN from one instance containing a single schema to another instance containing multiple schemas. Maybe a separate question! – opticyclic Jul 19 '17 at 19:41
  • There is nothing that says you can't use both: use RMAN for DR and use datapump for data loads. I just disagree with "instead of". They aren't mutually exclusive technologies. If one of your dev environments explodes or block level corruption is introduced and all you have are your nightly exports to rebuild your test environment, could your devs live with losing that days work? Many companies can't... – Kris Johnston Jul 19 '17 at 20:03
  • I'd vote for multiple instances. Multiple schemas may be efficient,. but if you have code that cross references a set of schema, then it will be nothing but trouble to resolve those references during import. datapump still (i think 12.1) cannot correctly resolve remap_schema issues for certain plsql code at times. – Raj Jul 20 '17 at 11:27
  • All the code is isolated to a single schema. There is no cross referencing. – opticyclic Jul 20 '17 at 13:38

Yes, it makes sense to have one instance with multiple schemas/users (one per application, or whatever else).

Why? Oracle licenses are extremely expensive. You're wasting money (and wasting resources, honestly) if you have one instance per application. Unless you need them separated due to some business/PCI rule, you should be okay with multiple applications on one instance.

However, you should not be mixing prod/dev/qa environments within one instance.

  • Oracle licensing allows multiple instances on one server without additional licenses. – CaM Jul 19 '17 at 18:41
  • Having multiple instances on one server should require additional cores if done right. Not increasing the cores for each instance installed is asking for fun times. – MguerraTorres Jul 19 '17 at 18:47
  • You're forgetting about virtualization (which is a whole new ball game for licensing). There isn't anything in the question that says whether this is on physical hardware or a virtual environment, so you really can't make the claim that having multiple instances will be more expensive due to licensing given what we know or don't know about the OP's environment. Also, consolidation to a single instance can work, but there could be name conflicts (schema, synonym, public objects, permissions, ACLs, directories) by doing so (one of the selling points for multitenant architecture). – Kris Johnston Jul 19 '17 at 19:00
  • It is a virtual environment – opticyclic Jul 19 '17 at 19:43
  • Solid points! The issue I'm seeing is that if you're adding more instances to a virtual, you either 1) Add more cores/memory and get charged for it, or 2) decrease performance because all instances are sharing the same cores. You can have 1000 instances on a single virtual, but they'll all be using the same cores/memory. When I last chatted with our Oracle reps, they stated they charge per core, even in Virtual Environments. Am I wrong in my thinking (honestly curious)? – MguerraTorres Jul 19 '17 at 21:00

Even if you don't have name conflicts and could easily combine all Dev environments into a single instance, I still prefer multiple instances for my Dev environments due to the following:

You really should have your Dev environments closely mimic your Live environments in terms of hardware, resources, and volume of data as much as possible. You will be in a much better position to:

1) Stress test new code before large (or even small) projects go live.

2) Your Dev teams are far more likely to catch performance issues before they go live (even if you don't stress test).

Performance issues should be caught long before you go live. The best way to do that is to test in an environment that is as close to your live environment as you can possibly make it (even before that code is uploaded to the QA environment, if you have one).

  • Good points. We do do the performance tests in a separate env that does mimic production though. Unfortunately, our dev application servers are crammed full with multiple instances which would be the bottleneck rather than the database in this case. – opticyclic Jul 23 '17 at 13:56

Each instance has it's own memory allocations. So combining everything into one instance lets you have far more efficient use of the RAM available on your Dev server.

Since you're using datapump instead of true backups via RMAN, adding a new schema or refreshing the data in one schema from your production environment should be faster this way, too.

And standing up a new schema won't require you to re-configure the other schemas to reallocate memory. Adding a new instance would require either leaving free memory available, or reducing all other instances to free up enough for the new one, triggering a restart on those existing instances.

Oracle licensing is either per user or per core. Their core licensing is number of cores = number of processors * number of threads on each processor * a multiplier (1/2 for intel CPUs). Their per user licensing is a minimum of 25 users per core. This means there's no advantage to either multiple schemas or multiple instances as far as licensing costs are concerned. Oracle charges the same for either mode.

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.