[askdba] Re: Merging Databases

  • From: Nisar Tareen <ntareen@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 7 Oct 2004 05:53:12 -0700 (PDT)

Did you cosider Replication. 
Nisar Tareen 

Rajesh Puneyani <rajpuneyani@xxxxxxxxx> wrote:

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) wrote:
> 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 
> 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)
> 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 
> > 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
> >

Do you Yahoo!?
vote.yahoo.com - Register online to vote today!

Other related posts: