[askdba] Re: Merging Databases
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>
- Date: Wed, 6 Oct 2004 13:52:22 -0600
The basic option of creating separate schemas in a single database and
segregating the application data into its own schema will be orders of
magnitude less work than integrating the applications. This will also
generally be possible while making little or no change to the
applications. The problems you are likely to hit here are
- Public synonyms must be avoided, since there may be multiple
"customer" tables and you want to ensure that users aren't accidentally
referring to the wrong table.
- Since resources are shared, applications need to "play nice". If one
application is allowed to use all the TEMP space, for example, it could
cause errors in other applications. If one application generates enough
UNDO that it fills the UNDO tablespace in under undo_retention seconds,
other applications may get ORA-01555 errors. You can mitigate this by
using Resource Manager to limit the load any one application generates
on the system.
- User management may take a bit of effort. Each database may have a
user "bob" today that maps to a different human user, which would need
to be resolved. If you have users that have been granted extensive
privileges, (i.e. anything with ANY TABLE), you probably want to lock
that down to ensure they don't now have access to all your schemas.
- Patches, backup, and recovery. Once you go to a single database, all
the applications have to move to new versions of Oracle in unison. They
also have to share the same backup & recovery strategy. If these are
third-party applications, the vendors may impose mutually incompatible
versioning requirements (i.e. Vendor A says you must run on 8.1.7.4,
vendor B says it only supports 8.1.7.0 through 8.1.7.2, and vendor C
only certifies on 9.2).
Moving everything into a single database schema will be vastly more
work, but there may be significantly greater gains. If you combine data
from different tables, you may be able to have a significantly richer
view of your customer data. The downside will be that combining data
from similar tables in different schemas is hard, so it will take time
to identify all the nuances and get everything right. It will also
probably require some significant changes to the applications, since you
will be changing the data they rely on.
Justin Cave <jcave@xxxxxxxxxxx>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Rajesh Puneyani
Sent: Wednesday, October 06, 2004 3:35 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: Merging Databases
Hi Justin,
You got it right.
That's precisely what I have to present in the management meeting.
These were 2 options which I already thought about but was wondering
if there is any detailed documentation with someone regarding
pros/cons of each strategy or any other method to accomplish the same.
All the databases are remotely located on different physical locations
so one of the method is going to be create all those databases in a
one central location on one big server.
Any hint on technical aspects of these would be helpful.
Thanks
On Wed, 6 Oct 2004 13:22:09 -0600, Justin Cave (DDBC)
<jcave@xxxxxxxxxxx> wrote:
> Can you define "merge" here a bit more precisely? In particular, are
> you going to be creating a number of schemas in a single database, one
> per application? Or will you be trying to integrate data from each of
> these applications into a single database schema?
>=20
> Justin Cave <jcave@xxxxxxxxxxx>
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
>=20
>=20
>=20
> -----Original Message-----
> From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
> On Behalf Of Rajesh Puneyani
> Sent: Wednesday, October 06, 2004 2:27 PM
> To: askdba@xxxxxxxxxxxxx
> Subject: [askdba] Merging Databases
>=20
> Hi guys,
>=20
> I have to present a detailed proposal/technical plan of merging a few
> databases of similar functional nature (not essentially similar DB
> objects) without changing the application (or minimum possible
> changes).
>=20
> e.g. Several customer Information databases will have customer
> specific data but underlying tables etc could be different in all of
> them. The Requirement is to merge them.
>=20
> I have a few strategies to achieve that but to elaborate upon them and
> see if there could be more strategies, I was looking for some external
> documentation.
>=20
> Does anyone has any white paper or some documentation on this topic ?
> Any help would be more than appreciated.
>=20
> Thanks
>=20
>
Other related posts:
