Forum OpenACS Q&A: 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:
- 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
- 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.)
- 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!