[askdba] Re: Merging Databases

  • From: Regis Biassala <Regis.Biassala@xxxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 7 Oct 2004 09:33:24 +0100

To add more to what Justin juste elaborated.
If you are thinking of combining those applications into one single schema
with data separation, you might as well consider oracle VPD 
(virtual private database) which already has a collection of
package/procedure that you could use.

Always create a prototype based on your situation to evaluate the amount of
time this is going to require.


-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx] On
Behalf Of Rajesh Puneyani
Sent: 06 October 2004 21:15
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: Merging Databases


Thank you very much.
It really is going to help me put together my detailed presentation.

If anyone has any other point to share then please do that.

On Wed, 6 Oct 2004 13:52:22 -0600, Justin Cave (DDBC) <jcave@xxxxxxxxxxx>
> 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,
> vendor B says it only supports through, 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: