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:
- 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.
- 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!