Home » Other » General » Migration project (Oracle 9i to Oracle 10g)
Migration project [message #383542] Thu, 29 January 2009 02:29 Go to next message
ashishdba19
Messages: 5
Registered: January 2009
Junior Member
This is basically an application upgrade and in order to do so we need to migrate its Oracle 9i database to a new structure on Oracle 10g. So we need to write scripts transferring data from source tables to destination tables. Here we have one source DB (schema) in Oracle 9i (for old application) and we need to migrate its data into Three destination DBs (schemas) in Oracle 10g (for new application).

Though we have written queries to do so. But I want to know that is there any systematic way doing it and any guidelines which I need to follow. How can I speed up my migration process? what extra measures can I follow? What techniques I can use in my scripts or in whole migration activity.
Re: Migration project [message #383547 is a reply to message #383542] Thu, 29 January 2009 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd do this as two different operations if I had the choice.
1) Migrate to 10g
2) Migrate to new application structure.
Re: Migration project [message #383548 is a reply to message #383542] Thu, 29 January 2009 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
exp/imp?

Regards
Michel
Re: Migration project [message #383550 is a reply to message #383547] Thu, 29 January 2009 02:44 Go to previous messageGo to next message
ashishdba19
Messages: 5
Registered: January 2009
Junior Member
Can you provide some more help on step 2? or redirect me to some useful URLs.
Re: Migration project [message #383553 is a reply to message #383548] Thu, 29 January 2009 02:46 Go to previous messageGo to next message
ashishdba19
Messages: 5
Registered: January 2009
Junior Member
exp/imp will not work in this scenerio because source and destination database are different in structure. So we have to write manual custom SQL scripts...
any help or URL would be appreciated.
Re: Migration project [message #383662 is a reply to message #383553] Thu, 29 January 2009 10:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What structure are you talking about?
Physical disk layout
or
Table structure?

If it is table and you want sql methods,
create a database link to other database,
write select statement with proper column mappings
and use
insert into local_table (select c1,c2 from remotetable@dblink).

If physical disk layout or tablespaces are different, it does not matter.
Exp/Imp will work fine
Re: Migration project [message #383736 is a reply to message #383662] Thu, 29 January 2009 22:21 Go to previous messageGo to next message
ashishdba19
Messages: 5
Registered: January 2009
Junior Member
thanks but I think I was not able to make you understand. This is not copying some tables to different DB or server. It is creating new DBs(three) from one DB with the help of some insert statement which also changes the structure of the DBs according to the new application version.
Re: Migration project [message #383741 is a reply to message #383542] Thu, 29 January 2009 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But I want to know that is there any systematic way doing it?
What sort of magic do you expect?
How is dumb software supposed to "know" into which of the three new schemas any old table is supposed to exist?

>Though we have written queries to do so.
You've already solved this problem, so proceed to deploy it.
Re: Migration project [message #383748 is a reply to message #383736] Thu, 29 January 2009 23:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
It is creating new DBs(three) from one DB with the help of some insert statement which also changes the structure of the DBs according to the new application version


And how is it different from what we have told before?
Before inserting data, you need the tables rights?
All you have to do is , do appropriate column mappings and use dblinks.

[Updated on: Thu, 29 January 2009 23:35]

Report message to a moderator

Re: Migration project [message #383749 is a reply to message #383741] Thu, 29 January 2009 23:28 Go to previous message
ashishdba19
Messages: 5
Registered: January 2009
Junior Member
Black Swan, If you cannot help then please do not comment. It is very easy to show how knowledgable you are but very difficult to help somebody with that knowledge.

By the way, thanks for your comments!
Previous Topic: Oracle Developer or DBA
Next Topic: Functional Overview document for "Retail Sales Audit"
Goto Forum:
  


Current Time: Thu Mar 28 03:29:47 CDT 2024