Should we follow our Oracle DBA directive and merge our 5 instances into 1? That's the question!!??
A little background:
Our Oracle EPM consultant installed our Oracle OBIEE and EPM apps (e.g. Hyperion Planning, HFM, DRM, FDMEE etc) using 5 different instances of Oracle databases.
Our Oracle DBA wants us to combine the 5 instances running on Oracle version 11i into 1 instance on a new Oracle version 12c with 5 different schemas. Will that complicate our upgrade from 11i to 12c ? For example, will the tables in these OBIEE and EPM repositories be different if we had 5 instances vs 1 instance? Those are our secondary and equally important questions.
Here’s what our EPM consultant, wrote:
Traditionally you would separate the OBIEE, Planning and HFM applications in separate DB’s but this has changed over time. Having a separate database effectively means that you have a separate environment for Planning, separate for HFM, etc. increasing the maintenance on each of the environments.
We have one Production environment at a client site, with one Oracle Database, hosting 9 Planning Applications ( in 9 Schema’s) and 5 HFM Applications (in 5 Schemas), Also one FDMEE schema and an ODI schema as well.
We have “High Availability” and replication (DR) running constantly and do not experience any performance issues, unless caused by badly designed / programmed customer scripts in EPM application.
So should we merge. If your answer is to keep OBIEE and EPM apps in different separate instances, I would like to know why so that I can present your answers to our DBA.
Do you agree with his statements above? I may not not in agreement for the following reasons:
1) It may require additional steps to migrate from 11i to 12c??? That's my secondary question.
Even though you can combine 5 instances into 1 instance, I don't believe it is best practice. That's why that "Big 3" consulting company and others install HFM into its own Instance of Oracle DB.
2) Performance tuning is at Instance level. Tuning instance for HFM will affect tuning for other EPM apps if they share the same Instance.
3) Oracle Instance performance will be adversely affected by multiple schemas that are heavily used. For example, HFM schema performance will be affected negatively if other schemas in that instance are heavily used.
4) I think that If there are multiple schema’s a restart of an Instance will take longer time to recover. Let’s say that I need to restart Hyperion schema due to corruption. I have to restart instance which will take longer as it has to restart a large schema called HFM. I’m not sure though.
(5) patches to EPM apps, backups and restores of schemas are more complicated if they are all in one schema? )
An obvious candidate here would be the multitenant option
http://www.oracle.com/technetwork/database/multitenant/overview/index.html which allows consolidation of multiple databases into a single instance, whilst still retaining the strict separations and resource controls that individual instances would have.
To look at your points
1) It may require additional steps to migrate from 11i to 12c??? That's my secondary question.
Even though you can combine 5 instances into 1 instance, I don't believe it is best practice. That's why that "Big 3" consulting company and others install HFM into its own Instance of Oracle DB. No matter what you do, it will be small effort compared the base effort on testing your applications under a new database version. That will be the bulk of the workload. How you configure the instance(s) wont make much difference to that.
2) Performance tuning is at Instance level. Tuning instance for HFM will affect tuning for other EPM apps if they share the same Instance.Instance level tuning is rare, and typically helps all applications.
3) Oracle Instance performance will be adversely affected by multiple schemas that are heavily used. For example, HFM schema performance will be affected negatively if other schemas in that instance are heavily used.
And this is no different to having 5 instances (on the same server). If you had multiple *servers* this might be a different situation, but 5 instances on one server will be similar to 1 instance that holds 5 applications on one server. The latter would be slightly more efficient (less background processes etc)
4) I think that If there are multiple schema’s a restart of an Instance will take longer time to recover. Let’s say that I need to restart Hyperion schema due to corruption. I have to restart instance which will take longer as it has to restart a large schema called HFM. I’m not sure though.
How often do you bounce your existing instances due to corruption ? I would hope the answer is "never" or "almost never". If not, you've got other problems to worry about :-) But if you are, then yes, I would quarantine the problem instance elsewhere.
(5) patches to EPM apps, backups and restores of schemas are more complicated if they are all in one schema? ) Presumably they are all separate tablespaces as well ? In that event, you can recover at this level as well.
One thing you have not mentioned is the strictness of separation. For example, if you have collisions of any kind (public synonyms, role names, etc etc), then this might present unforeseen problems. If you have reference sites that are doing it fine, then you'd be ok.
But like anything - do a cost/benefit analysis here. I don't see an issue with consolidation, but ask your DBA - what *benefit* is it giving the organization. If they can justify the benefits (workload, cost, license, etc), then proceed.