OraFAQ Forum: Server Utilities » Data Migration using Datapump

Home » RDBMS Server » Server Utilities » Data Migration using Datapump (Linux-Oracle 10g to Windows-Oracle 11g)

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

Data Migration using Datapump [message #506881]

Wed, 11 May 2011 09:51

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

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

Report message to a moderator

Re: Data Migration using Datapump [message #506883 is a reply to message #506882]

Wed, 11 May 2011 09:57

geobraik
Messages: 34
Registered: April 2011

Member

expdp is used for creating the dump file

Report message to a moderator

Re: Data Migration using Datapump [message #506884 is a reply to message #506883]

Wed, 11 May 2011 10:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

CONTENT=METADATA_ONLY SQLFILE=old_ddl.sql

Report message to a moderator

Re: Data Migration using Datapump [message #506910 is a reply to message #506884]

Wed, 11 May 2011 14:52

geobraik
Messages: 34
Registered: April 2011

Member

OK.
Then run the old_ddl.sql. and import the full dump. right?

Report message to a moderator

Re: Data Migration using Datapump [message #506911 is a reply to message #506910]

Wed, 11 May 2011 15:01

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.

Report message to a moderator

Re: Data Migration using Datapump [message #507096 is a reply to message #506911]

Thu, 12 May 2011 07:54

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

Report message to a moderator

Re: Data Migration using Datapump [message #507839 is a reply to message #507096]

Wed, 18 May 2011 03:49

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

Report message to a moderator

Re: Data Migration using Datapump [message #507841 is a reply to message #507839]

Wed, 18 May 2011 03:55

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Do NOT use TOAD
2/ Make the export with Data Pump

Regards
Michel

Report message to a moderator

Re: Data Migration using Datapump [message #507850 is a reply to message #507841]

Wed, 18 May 2011 04:36

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.

Report message to a moderator

Re: Data Migration using Datapump [message #507853 is a reply to message #507850]

Wed, 18 May 2011 04:45

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

Don't TOAD exports just consist of a lot of insert statements?

Report message to a moderator

Re: Data Migration using Datapump [message #507856 is a reply to message #507853]

Wed, 18 May 2011 05:14

geobraik
Messages: 34
Registered: April 2011

Member

No. It is a .dmp file

Report message to a moderator

Re: Data Migration using Datapump [message #507861 is a reply to message #507856]

Wed, 18 May 2011 05:25

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Is it not possible to continue with this export?

Try to use imp or impdp with it.

Regards
Michel

Report message to a moderator

Re: Data Migration using Datapump [message #507940 is a reply to message #507861]

Wed, 18 May 2011 09:46

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Data Migration using Datapump [message #507949 is a reply to message #507940]

Wed, 18 May 2011 10:10

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Data Migration using Datapump [message #507952 is a reply to message #507949]

Wed, 18 May 2011 10:16

geobraik
Messages: 34
Registered: April 2011

Member

But how can they know, for which roles scripts to be generated?

Report message to a moderator

Re: Data Migration using Datapump [message #507954 is a reply to message #507952]

Wed, 18 May 2011 10:21

cookiemonster
Messages: 13965
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?

Report message to a moderator

Re: Data Migration using Datapump [message #507958 is a reply to message #507954]

Wed, 18 May 2011 10:24

geobraik
Messages: 34
Registered: April 2011

Member

Yes. You are correct

Report message to a moderator

Re: Data Migration using Datapump [message #507961 is a reply to message #507958]

Wed, 18 May 2011 10:26

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ask them to provide a full dump then you will sort that out.

Regards
Michel

Report message to a moderator

Re: Data Migration using Datapump [message #507963 is a reply to message #507961]

Wed, 18 May 2011 10:49

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

Report message to a moderator

Re: Data Migration using Datapump [message #507968 is a reply to message #507963]

Wed, 18 May 2011 11:17

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1. If you then import full there is no problem
2. Without importing them before, no.

Regards
Michel

Report message to a moderator

Re: Data Migration using Datapump [message #507982 is a reply to message #507968]

Wed, 18 May 2011 13:38

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).

Report message to a moderator

Re: Data Migration using Datapump [message #507989 is a reply to message #507982]

Wed, 18 May 2011 14:30

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Data Migration using Datapump [message #507999 is a reply to message #507982]

Wed, 18 May 2011 16:59

cookiemonster
Messages: 13965
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.

Report message to a moderator

Re: Data Migration using Datapump [message #508012 is a reply to message #507999]

Thu, 19 May 2011 01:08

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Using FTP?

Regards
Michel

Report message to a moderator

Re: Data Migration using Datapump [message #508036 is a reply to message #508012]

Thu, 19 May 2011 02:27

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	Backup through Batch file.
Next Topic:	data migration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 20 23:40:14 CST 2025