[askdba] Re: Merging Databases
- From: Ganesh Raja <ganesh.raja@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Thu, 7 Oct 2004 09:42:38 +0100
A Nice Article on VPD is there in http://www.dizwell.com/
Cheers
Ganesh R
On Thu, 7 Oct 2004 09:33:24 +0100, Regis Biassala
<regis.biassala@xxxxxxxxxx> wrote:
>
> 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.
>
> Regards,
> Regis
>
>
>
>
> -----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
>
> Justin,
>
> 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.
> Regards
> Rajesh
>
> On Wed, 6 Oct 2004 13:52:22 -0600, Justin Cave (DDBC) <jcave@xxxxxxxxxxx>
> 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 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:
