Re: 1 or 2 databases and/or servers?

Yong CC (Joe) wrote:
>
> Got a small dilemma here. A colleague of mine is involved in a
> project that uses Oracle 7 for two (2) independent departments within
> the same company. The _only_ connection between these two departments is
> that they may need to query data from each other's database. There are no
> relations whatsoever between the two databases. They merely need to have
> access (with certain restrictions) to the information on each other's
> database. Now, when consulted, I recommended that there be two distinct
> databases sitting on individual servers. My justification include the
> following factors (in brief):
> - security
> - ease of maintenance
> - independence of operations/maintenance
> - minimal "downtime"
> - caters for upgrades/expansion
> My colleague shares my opinion but she is having some trouble
> getting the same support from the rest of her team. Some of the reasons
> they provided include difficulty in implementing such a setup, difficulty
> in writing applications (they're using Oracle Forms 4.5) to work with this
> setup and lack of documented proof that this solution was better that having
> both database sit on the same Oracle server or even just having one large
> database even though there will be two, generally unrelated set of tables.
> I reject these as invalid arguments but it is difficult to explain to them
> since they are all from AS/400 and/or mainframe background with no RDBMS
> experience (no offense intended).
> Does anyone out there have any experience with such a setup or knows
> of such a setup and does not mind sharing some info/experience. I am
> preparing a report to present my recommendation and any information would
> be most appreciated. Thank you.
>
> Joe Yong
> Software Alliance (M) Sdn. Bhd.

From the information you give, I would agree that creating two databases seems to be the best approach. Here are some thoughts.

If you are forced to create both databases on the one server, but are concerned about independence and wish to minimise downtime, then a possible alternative might be to use Oracle's snapshot mechanism. With this approach, you could have a master database on one machine, and setup a read-only copy of it (or a subset of it) on a different machine. Oracle will refresh the read-only copy at pre-set time intervals (which you can choose). The refresh occurs due to the action of triggers in the master database which take effect where database changes have occurred. An advantage of this approach is that the read-only copy is still available if the master goes down. The down-side is that the dba needs to monitor the snapshot on a regular basis to verify that automatic refresh is still active. We used this approach on a trial basis for a number of months to provide query access to an external client, and found it to be reasonably satisfactory as long as refreshes are not too frequent. (We did our refreshes once per night.)

Our present situation is that we have two independent databases which contain land information, one holding land ownership and valuation data, the other holding spatial data showing the land parcel boundaries. In our case both databases reside on the same machine, with user access provided by a distributed client/server application running under the control of a transaction monitor (in our case, Tuxedo).

Good luck.

-- 
Mike Biggin	(mbiggin_at_denr.sa.gov.au)
Department of Environment and Natural Resources, South Australia
Telephone:	Oz: (08) 8204 9217; International: (618) 8204 9217
Mail:		GPO Box 1047, ADELAIDE 5001     Australia