Q
Problem solve Get help with specific problems with your technologies, process and projects.

Multiple instances vs. single instance

Published: 22 Oct 2003

I am a veteran Oracle DBA with a strategic question.

Our business unit has a UNIX server with five instances -- running a variety of small apps, a small data warehouse and a few repositories. Having five instances wastes resources and duplicates administrative tasks, so I'd like to realign the instances. My question is: What's an appropriate disposition and why?

Here are some givens:

  • There are five repositories: OEM, ERWin, Informatica ETL, Brio Reporting and Oracle Names.
  • Our applications are small and not hardcore OLTP/ERP apps. The repositories probably put more OLTP pressure on the instance than the apps.
  • The data warehouse is small (under 1 GB) and does not (yet) have OLAP queries running against it. ETL loads run at night.
  • The hardware (with five instances) is still underutilized. This project is not a response-time optimization, it's a resource/DBA optimization.
  • The applications, data warehouse and repositories are all vital apps. If we lose one, we effectively lose them all -- so application isolation is not an issue.

    • Some of the options I'm considering are:

  • Leave everything alone. Pros: If it's not broke (from the users' point of view) don't fix it. Cons: Waste of resources.
  • Consolidate everything into one instance. Pros: Easiest administration. Cons: Makes me nervous -- especially the repositories.
  • Align the instances according to use (OLTP, Repository and DW). Pros: Isolates the repositories. Cons: The OLTP instance would be underutilized.
  • Variation on the above (create an OLTP/Repository instance and a DW instance). Pros: Best balance of resources and allows for future hardware isolation.
  • Align the instances according to department (Finance, Supply Chain, Marketing). Pros: Improves response times for users. Cons: Does not address the current need.

    • I'm not looking for the "book" answer from Oracle, I'm looking for practical, pragmatic reasoning. I appreciate your time and any thoughts you might have.
    Given that you said 1) this is not a response-time optimization but a resource/DBA optimization and 2) that if you lose one app, you effectively lose them all, I'd suggest going with a single instance. You noted that the pro of doing this would be how much the administrative burdens would be lessed but that it "makes you nervous". So, let's look at how you could have a single instance and give yourself some piece of mind as well.

    Why not consider the single instance with a standby database providing failover? You could have your standby on the same server (although having it on another would be "ideal") but simply protect (so to speak) it by using different disk volumes for the standby database's datafiles. Let's say that you have a disk failure in your primary instance. The standby database could be brought up in minutes and your users could continue to work while the problem is corrected.

    I also think some strategic datafile placements could also help prepare you for future growth. Try and isolate datafiles by function across disk volumes (OLTP, Repository and DW). Also, consider partitioning if you have a lot of static data. If data is static, consider creating tablespaces (by partition possibly) which can be made read-only at some point, thus allowing you to create a static one-time backup of that data in case recovery is necessary.

    Bottom line (I think), the key to any choice would be to have a well-planned backup/recovery strategy in place. Given the environment you describe, I'd really consider pulling everything into a single instance, do some "creative compartmentalization" of your datafiles across disk volumes, work out and test a backup/recovery mechanism to be as air-tight as possible and go for it!

    Dig Deeper on Oracle database design and architecture

    SearchDataManagement

    SearchBusinessAnalytics

    SearchSAP

    SearchSQLServer

    TheServerSide.com

    SearchDataCenter

    SearchContentManagement

    SearchHRSoftware

    Close