Re: Merging 2 "almost" identical databases.

On 29 sep, 12:41, sybrandb <sybra..._at_gmail.com> wrote:
> On 29 sep, 11:55, Neomusashi <peter.de.win..._at_gmail.com> wrote:
>
>
>
> > Hi,
> > So my work for the next few weeks is sorting out how to merge 2 almost
> > identical databases used by 2 almost identical applications into one
> > application with one database. These applications were developed for a
> > client.
> > Why 2 DB' s and 2 Applications? Well it’s a legacy decision. My
> > predecessors decided it should be that way and now it ' s up to me to
> > fix this. They needed small application/functional differences for
> > other distribution channels and made a clone of the original app. .
> > Now several years later they would like to merge the two apps into
> > one, but of course they grew apart in functionality and structure. No
> > big differences, but enough to make this a nice project ... .
> > Short description:
> > Each application runs on a sun solaris 10 server in a websphere 6.0
> > application server (J2EE apps of course) and an orcale 10 database is
> > used. Database queries are done using Stored Procedures. Both have 2
> > schemas. One schema has the core data in about 20 tables and the other
> > contains about 9 generic servers and about tables. The tables purposes
> > don’ t really differ from each other, but columns,  DDL and data load
> > do.
>
> > What I need to come up with is a merging process we can deliver to our
> > client so they can easily merge the two databases.  The process has
> > two deliver a clear report.
> > I was thinking of three possible ways to go:
> > 1.      Shell scripts with logging:
> > pros: basic, quickest?
> > Cons: Debugging, testing, complex, report/logging not so fancy/clear,
> > inhouse expertise
> > 2.      Write a mini J2EE app:
> > pros: easy to debug, easier to make?, better report/logging
> > Cons: very slow to develop, work to throw away.
> > 3.      Write a java app:
> > pros: easy to debug, easy to make, better report/logging
> > cons: work to throw away, slow to develop
>
> > I’ m feeling most for the 3th solution. How would you approach this?
>
> I would make sure I do everything *in* the database, and do NOTHING at
> the client side.
> So I would use database links and SQL and PL/SQL, using the MERGE
> statement available since 9i.
> I would also try to find out whether simply using export/import, or
> expdp/impdp is not an option.
> This will be definitely the fastest solution.
> I would also try to find out what can be done using
> dbms_rectifier_diff.
>
> In short, I would use Oracle as much as possible, instead of various
> Java crap.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I was thinking that way already, we do not want to overload an application server.
The java part would only serve as trigger or to write some logging/ reporting on how things are going. They have to be able to see what is going on.
Since my oracle knowledge is pretty small, your input is really handy to me. My first tought was to do this using SQL scripts/ PL/SQL using a database link, but the thing you suggested are worth considering. Thanks!

Anyone else some ideas I should look at? Thanks in advance. Received on Mon Sep 29 2008 - 07:15:26 CDT