OraFAQ Forum: Server Administration » Data migration between two different application

Home » RDBMS Server » Server Administration » Data migration between two different application (Oracle 9i, Oracle 11g, OS: windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data migration between two different application [message #498193]

Wed, 09 March 2011 03:53

ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore

Senior Member

Hi Friends

We are planning to migrate data from an application called clintrace to another application called argus safety. Both the applications are related to pharmacovigilence safety operation. Both the applications functionality are similar. So both the database are having the same data though the table structures might be different. Both the database are oracle clintrace db is 9i and argus db is 11g.

If any one has ever done such kind of data migrations, Please share your idea on the below;

1. The aproach to do this.
2. Special cases to be taken care.
4. Best practices.

Thanks
Ridhi Sundar

Report message to a moderator

Re: Data migration between two different application [message #498202 is a reply to message #498193]

Wed, 09 March 2011 04:10

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1. Carefully detailled the similarities and differences. Create views upon the tables, these views will be accessed by the application instead of the tables
2. Confidentiality.
4. Be really really careful in what you do; for instance, after 2 there is 3 not 4.

Regards
Michel

Report message to a moderator

Re: Data migration between two different application [message #498233 is a reply to message #498202]

Wed, 09 March 2011 05:19

ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore

Senior Member

Michale
It seems you did not get it properly what we are trying to do.

Both these applications are prebuilt. We just want to migrate the data from clintrace aplication's DB to Argus's DB. The possible solutions what I see are;

1. create dblink and use insert into .. as select querries to get the data.
2. export to flat files and again import back to argus db.

3. use some data migration tools(If available).

Thanks
Ridhi Sundar

Report message to a moderator

Re: Data migration between two different application [message #498258 is a reply to message #498233]

Wed, 09 March 2011 06:16

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

It seems you did not get it properly what we are trying to do.

It seems you didn't clearly explain what you want to do because for me
Quote:

migrate data from an application called clintrace to another application called argus

means you want to merge the schemas above all when you say:
Quote:

the database are having the same data though the table structures might be different

Why saying all this stuff and not just: "we want to copy the data from one database to another one"? What is the purpose of specifying there are 2 applications that have quite the same data and tables and so on?

export/import is the simplest one.

Regards
Michel

Report message to a moderator

Re: Data migration between two different application [message #498431 is a reply to message #498258]

Thu, 10 March 2011 00:00

ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore

Senior Member

Hi MIchale

How can we use export/import while the table structure are different?

Thanks
Ridhi Sundar

Report message to a moderator

Re: Data migration between two different application [message #498457 is a reply to message #498431]

Thu, 10 March 2011 01:07

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So do you want to copy or merge? If you change the specifications at each post we can't build a solution!

Regards
Michel

Report message to a moderator

Re: Data migration between two different application [message #498535 is a reply to message #498457]

Thu, 10 March 2011 03:42

ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore

Senior Member

I want to copy the data but the table structure on the databases are different.

As these are prebuilt applications we can not change the appliacation to look into the new table structure I will import. So I have to pull the data from the source and put in to the target database's respective tables.

Thanks
RIdhi Sundar

Report message to a moderator

Re: Data migration between two different application [message #498546 is a reply to message #498535]

Thu, 10 March 2011 04:05

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So my first answer was correct:

Quote:

1. Carefully detailled the similarities and differences. Create views upon the tables, these views will be accessed by the application instead of the tables

Regards
Michel

Report message to a moderator

Re: Data migration between two different application [message #498548 is a reply to message #498535]

Thu, 10 March 2011 04:12

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Usually:

1) Read the documentation and data dictionary about the data structure the source application.
2) Read the documentation and data dictionary about the data structure of the target application.

Also check if the target application has some sort of "interface" tables or procedures that you can use to batch load data with built-in integrity checks.

If there is no documentation (as I have seen more often than I care to remember) you would have to create them from scratch.

Then create a mapping between table/columns while you figure out which table/column data of the source application has to go into which table/column of the target application.

Set up a test environment for the target application, write select / insert statements that select the data from the source application db and write it to the target application db.

Then run those SQLs and see if the target application works as expected. If not throw away the test environment, correct the transfer SQLs and try again.

Report message to a moderator

Previous Topic:	Do DBA's use Oracle Enterprise Manager Grid Controlmore more than command line
Next Topic:	Insufficient Privilege

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 00:50:54 CDT 2024