Home » RDBMS Server » Server Utilities » Data Migration using Datapump (Linux-Oracle 10g to Windows-Oracle 11g)
Data Migration using Datapump [message #506881] Wed, 11 May 2011 09:51 Go to next message
geobraik
Messages: 34
Registered: April 2011
Member
Hi
I got an assignment to create Oracle 11g db. I will be provided the full datapump export dump of an Oracle 10g db in linux. I need to import it to 11g Database in Windows. I have no information about the tablespaces, users etc
I have created db with system,sysaux,undotbs temp and users tablespaces.
Please suggest me best practices and steps to be followed.

Thanks in advance
Geobraik

[Updated on: Wed, 11 May 2011 09:53]

Report message to a moderator

Re: Data Migration using Datapump [message #506882 is a reply to message #506881] Wed, 11 May 2011 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
was file made with exp or expdp?

You will need to pre-create tablespaces prior to "importing" the remaining objects
Re: Data Migration using Datapump [message #506883 is a reply to message #506882] Wed, 11 May 2011 09:57 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
expdp is used for creating the dump file
Re: Data Migration using Datapump [message #506884 is a reply to message #506883] Wed, 11 May 2011 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CONTENT=METADATA_ONLY SQLFILE=old_ddl.sql
Re: Data Migration using Datapump [message #506910 is a reply to message #506884] Wed, 11 May 2011 14:52 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
OK.
Then run the old_ddl.sql. and import the full dump. right?
Re: Data Migration using Datapump [message #506911 is a reply to message #506910] Wed, 11 May 2011 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Then run the old_ddl.sql

The content of file needs to be modified to change *nix directories to Windows folder names.
Re: Data Migration using Datapump [message #507096 is a reply to message #506911] Thu, 12 May 2011 07:54 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Hi,
Thank you very much for the reply.

I got some more doubts.

1.From the old_ddl.sql do I need to create the tablespaces only or the whole scripts to be executed? Because ther is create alter profile, create profile, etc.
Also create user SYS.. also ther. But these are already created while creating the db itself.

2.Will creating the tablespace with name same of source db and importing the full dump with parameter remap_datafile be enough? Or I need do perform some other steps?

3. Will the content of system and sysaux tablespace of target(11g)be overwritten with that of source(10g)?

Please reply asap

Thanks in advance
Re: Data Migration using Datapump [message #507839 is a reply to message #507096] Wed, 18 May 2011 03:49 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Hi,
There is a difference in the requirement.
There are 4 schemas to be imported to Oracle 11gR@ on Windows from two different databases. Two schemas from Oracle 10gR2 on linux and two schemas from Oracle 11gR2 on windows.
Export is done using Toad. Also scripts for tablespace creation, user creation and role creation are given.
I have created a Oracle 11gR2 database on windows. Kindly suggest how to proceed.

Thanks in advance,
Geobraik
Re: Data Migration using Datapump [message #507841 is a reply to message #507839] Wed, 18 May 2011 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do NOT use TOAD
2/ Make the export with Data Pump

Regards
Michel
Re: Data Migration using Datapump [message #507850 is a reply to message #507841] Wed, 18 May 2011 04:36 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Hi,
Client gave the export dumps which is created using TOAD. These databases are located at remote places and we dont have access. We have only the export dumps and scripts.
Is it not possible to continue with this export? If possible please tell me the steps to follow.
Re: Data Migration using Datapump [message #507853 is a reply to message #507850] Wed, 18 May 2011 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't TOAD exports just consist of a lot of insert statements?
Re: Data Migration using Datapump [message #507856 is a reply to message #507853] Wed, 18 May 2011 05:14 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
No. It is a .dmp file
Re: Data Migration using Datapump [message #507861 is a reply to message #507856] Wed, 18 May 2011 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it not possible to continue with this export?

Try to use imp or impdp with it.

Regards
Michel
Re: Data Migration using Datapump [message #507940 is a reply to message #507861] Wed, 18 May 2011 09:46 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Hi
I tried using exp. It is working.
But the problem is creating and granting roles and privileges. Can you tell me how to find roles granted to this user, roles and privileges granted to these roles etc
ie if user u1 has rol1; rol1 has role2; rol2 has rol3; rol3 has some system privileges..
Then how can I generate create script and grant script for these roles?
Please suggest so that I can send the script to client to generate sripts for roles

[Updated on: Wed, 18 May 2011 09:48]

Report message to a moderator

Re: Data Migration using Datapump [message #507948 is a reply to message #507940] Wed, 18 May 2011 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand, you are at the source of the dump file or at the target?

You said:
Quote:
Client gave the export dumps which is created using TOAD. These databases are located at remote places and we dont have access

So you cannot generate nothing.
Ask your client to provide you the DDL for these ones.

Regards
Michel
Re: Data Migration using Datapump [message #507949 is a reply to message #507940] Wed, 18 May 2011 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please suggest so that I can send the script to client to generate sripts for roles

Oh oh, I see, as they use TOAD they can do it with TOAD just clicking on "Generate the script".

Regards
Michel
Re: Data Migration using Datapump [message #507952 is a reply to message #507949] Wed, 18 May 2011 10:16 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
But how can they know, for which roles scripts to be generated?
Re: Data Migration using Datapump [message #507954 is a reply to message #507952] Wed, 18 May 2011 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So to clarify:
1) You want to create a new DB with the combined contents - users, roles, objects of two different existing databases.
2) You do not have access to these two existing databases.
3) The people who do have access have no idea how to give you the necessary scripts to recreate what they have.

Does that sum it up?
Re: Data Migration using Datapump [message #507958 is a reply to message #507954] Wed, 18 May 2011 10:24 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Yes. You are correct
Re: Data Migration using Datapump [message #507961 is a reply to message #507958] Wed, 18 May 2011 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask them to provide a full dump then you will sort that out.

Regards
Michel
Re: Data Migration using Datapump [message #507963 is a reply to message #507961] Wed, 18 May 2011 10:49 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
1.If I have full dump how to create roles related to a prticular user? Becuase roles are to be created before importing the schema, right?
2.Also I would like to know if there is any way to find the roles and privileges related to a user, if the case is user having rol1, rol1 has rol2, rol2 has system/object privilege

Thanks $ regards
Geobraik
Re: Data Migration using Datapump [message #507968 is a reply to message #507963] Wed, 18 May 2011 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. If you then import full there is no problem
2. Without importing them before, no.

Regards
Michel
Re: Data Migration using Datapump [message #507982 is a reply to message #507968] Wed, 18 May 2011 13:38 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Since I want to import only two schemas from two different database I cannot import full.
My question is, if I got full dump how should I proceed to import only two schemas. I have create script for the users and grant script for users. But I dont have create script for roles and grant script for roles.
And second question is how can I generate create script and grant script for roles, which ae granted to a user(from source database).
Re: Data Migration using Datapump [message #507989 is a reply to message #507982] Wed, 18 May 2011 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have 2 options:
- ask your client to send them to you
- import full

regards
Michel

[Updated on: Wed, 18 May 2011 14:30]

Report message to a moderator

Re: Data Migration using Datapump [message #507993 is a reply to message #507982] Wed, 18 May 2011 15:25 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
geobraik
Since I want to import only two schemas from two different database I cannot import full.

Obviously, not.


geobraik
My question is, if I got full dump how should I proceed to import only two schemas.

You'd use the FROMUSER IMP utilitie's parameter. Check it here; it says (among other useful information)
Oracle
The parameter enables you to import a subset of schemas from an export file containing multiple schemas (for example, a full export dump file or a multischema, user-mode export dump file).


The same goes for data pump import; only, the parameter name is now SCHEMAS.
Re: Data Migration using Datapump [message #507999 is a reply to message #507982] Wed, 18 May 2011 16:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
geobraik wrote on Wed, 18 May 2011 19:38
Since I want to import only two schemas from two different database I cannot import full.


Yes you can - it'll involve creating a temporary DB to be used as a staging DB. Then from there you can copy over exactly what you want to the target. It'll be far easier to control.
Re: Data Migration using Datapump [message #508012 is a reply to message #507999] Thu, 19 May 2011 01:08 Go to previous messageGo to next message
geobraik
Messages: 34
Registered: April 2011
Member
Dear Michel, cookiemonster and LittleFoot,
Thank you very much for the suggestions.
I wish to know one more thing. If a full dump is taken from linux machine how the dumpfile and logfile can be transferred to Windows machine?

[Updated on: Thu, 19 May 2011 01:18]

Report message to a moderator

Re: Data Migration using Datapump [message #508020 is a reply to message #508012] Thu, 19 May 2011 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using FTP?

Regards
Michel
Re: Data Migration using Datapump [message #508036 is a reply to message #508012] Thu, 19 May 2011 02:27 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
geobraik wrote on Thu, 19 May 2011 08:08
If a full dump is taken from linux machine how the dumpfile and logfile can be transferred to Windows machine?

If you are worried about different operating systems, well - no need to worry. Dump files are platform-independent (so you can move them around, wherever you have a valid Oracle installation). A log file is a textual file anyway so any text editor can read it.
Previous Topic: Backup through Batch file.
Next Topic: data migration
Goto Forum:
  


Current Time: Thu Mar 28 04:53:55 CDT 2024