Forum OpenACS Q&A: Multiple Oracle instances on one box?

Collapse
1: Multiple Oracle instances on one box?
Posted by Andrew Piskorski on 04/20/03 12:00 AM
Anyone here have experience running multiple Oracle instances on one box?

On a new server (very beefy Linux box, dual cpu, 8 GB RAM, SCSI RAID-10, etc.), I'm considering running 3 separate Oracle instances. If necessary, any of these three instances could be moved to their own machine, but I'm considering this primarily for Oracle configuration and maintenace reasons, not scalability, so if feasible I would leave all the instances running on the one big server. (Although the potential for improved scaleability is of course a nice side-effect.)

Why 3 instances, rather than multiple schemas all in one instance? The first reason I came up with is that you can only turn Oracle's Archivelog mode on and off instance wide. Also, there are certain features, namely transportable tablespaces, that you can only use between instances.

So far, here's what I'm thinking the three instances would look like:

  1. One OLTP style instance, in ARCHIVELOG mode, used probably for both Development and Production schemas. OpenACS for an intranet, plus perhaps other stuff, would live here. We use archivelog mode so that we never lose any committed transactions; in the event of a disk failure or etc., we can recover and roll forward to the point of failure.

  2. One Production data warehouse style instance, in non-archivelog mode. This has maybe 20-100 GB of data, but all stuff that if we lose one day's worth of data due to a failure, we can repopulate from the original data sources or the like. Therefore we can do without archivelog mode, and we want to do without it, because we regularly insert large quantities of data into this database, and leaving archivelog turned on can slow these very large inserts down a lot. (That's the theory anyway. The true performance impact of leaving archivelog on probably depends heavily on the disk I/O you have available, and we haven't really tested this yet to find out whether the peformance hit really is "a lot" or not.)

  3. A Dev/Testing version of the data warehouse. Populating a Dev schema via export/import tends not to work very well for large databases, it's just too slow. Lots of custom scripts using sqlldr may be an option, but take development, maintenance, etc. Transportable tablespaces should be the fastest way to clone Production data to Dev, and that requires that Dev be in a different instance.

I have not yet done much investigation into this, but if anyone else here has tried it, I'd definitely like to hear what you learned. E.g., any Oracle tuning settings necessary, sysadmin stuff, performance issues, etc. etc.

The OLTP stuff would probably need read-only access to the data warehouse stuff, but Oracle has database links between instances which seem to work ok, so I think it shouldn't matter whether the data is in the same instance or not.

Any other issues or problems, whether potential or stuff you've seen in practice, I'd definitely like to hear about them now, so I can better plan what I might need to research, test, etc. Thanks!

Collapse
2: Re: Multiple Oracle instances on one box? (response to 1)
Posted by mark dalrymple on 04/20/03 12:56 AM
I used to run two instances, an 8.1.6 and an 8.1.7 (mainly due to sloth, putting off moving the sites running on 8.1.6 to the 8.1.7 instance).  It was pretty easy to set up.  The main things were making sure that memory configs were such that both could live in memory happily without swapping at the OS level, and that the right ORACLE_HOME was set when running sqlplus/aolserver/whatever.  I had everything local (the bequeath adapter), so I didn't get into any listener / network configuration issues (but I don't think that should be too much of a problem)
Collapse
3: Re: Multiple Oracle instances on one box? (response to 1)
Posted by Hazi Gharagozlou on 04/21/03 01:03 PM
I have Oracle 8 and 9 running on the same machine. I created two oracle users oracle8 and oracle9 and they live happily together. The Oracle 9 migration documentation gives details of such a scenario.
Collapse
4: Re: Multiple Oracle instances on one box? (response to 1)
Posted by Dion Sweat on 04/24/03 07:07 PM
Here at work, we have one instance per application, for a total of 6 (small) instances running on our production database server (Sun Enterprise 420R, 2 CPU, 2G RAM).  We've had zero problems running this setup, and it's easy to add additional instances, assuming the server has the available resources (you may need to bounce the listener to pickup the new instance).  Though, running multiple instances on the same box will require you to be careful when sizing your SGA, but you've got lots of RAM.  Make sure you standardize on your filesystem layout for all instances.  And don't forget to update your oratab file, if you're manually creating instances.  You really shouldn't have any problems with this setup.