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

Four considerations for combining applications into one Oracle instance

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 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 & 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.
    It has been the IT business climate these days to squeeze more out of their systems in an effort to cut costs. One of the things that a DBA may consider is to consolidate instances. The first thing that I should tell you is that many experts advocate never running multiple applications against the same Oracle instance. Their advice is to just run multiple instances on the same server instead. While I try to follow this line of reasoning, there are business considerations that often dictate against this advice.

    So what do you need to consider when combining applications into one Oracle instance?

    1. Only one instance to monitor on one server. This can greatly reduce your administration overhead as well as reduce your hardware costs.

    2. Performance tuning becomes more difficult. You are sharing resources among applications so each application's performance now affects another application's performance. One application with a terrible SQL statement can impact other applications. When you look at the buffer cache, is it one or more applications that are causing a poor performing buffer cache?

    3. One of the biggest headaches concerns your database version and application certification against that version. This is especially important with third-party applications. You may have purchased a third-party application that is only certified on Oracle9i. But other third-party vendors will only certify their product on Oracle8i. So you can't upgrade the instance to support one product because the other products aren't ready for the new version. So what do you do? If you have many third-party applications, you will find that one of your application's vendor is keeping you on an older release. Is that fair to the other application users?

    4. Business rules of your application can cause some problems. One application needs to be up 24x7, but the other applications don't care. This can force you into a backup strategy or do other things to the instance that you wouldn't be constrained to otherwise.

    These are just a few of the things that come to mind. But it should start you down the road of determining in consolidation of your Oracle instances. Personally, I like to keep one instance per application. But there are times that I have disregarded that rule of thumb.

    Dig Deeper on Oracle database design and architecture

    Have a question for an expert?

    Please add a title for your question

    Get answers from a TechTarget expert on whatever's puzzling you.

    You will be able to add details on the next page.

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.