[askdba] Re: Merging Databases

  • From: "Subodh Deshpande" <subodh_deshpande@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 07 Oct 2004 05:56:30 -0500

hi rajesh,

indeed nice replied..by justine

am not going in technical details as the effort involved this exercise is 
dependednt on how much is the structural difference in the database and 
applications..this is what you have to be sure..

and the management may ask you how you are going to synchronise the 
applications with one db.

answer may be - a detailed gap analysis report plus report on process 
mapping..number of servers presently used, with there hardware sizes, new 
servers its cost etc..

how the actual end users will be accesing the data from another locations

answer may be - all the users will have to shift to one locations or present a 
new network diagram, you may require lease line, VPN etc be ready for that..

what is the achievement out of this

answer may be all the customer database is at one place..so effective and more 
accurate analysis can be done which will be helpful..

down time at different locations, the interdependency of data within the 
locations if any will be avoided, which will increse the business hours 

maintenace of the applications and database is reduced, so the cost on IT  
department is reduced for increased business hours..

after this, management may ask you why not scrap everything and go for 
an ERP/CRM..

be ready for those answers..:-)

please correct if am wrong..

good luck..

----- Original Message -----
From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
Date: Wed, 6 Oct 2004 13:52:22 -0600
To: <askdba@xxxxxxxxxxxxx>
Subject: [askdba] Re: Merging Databases

> 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
> >

Good Luck..Subodh Deshpande

Experience Is Knowledge
Wisdom Is Philosophy

Find what you are looking for with the Lycos Yellow Pages

Other related posts: