Multiple instances vs. single 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:
Some of the options I'm considering are:
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!