OraFAQ Forum: Server Utilities » how to import dump of one databse to another database

Home » RDBMS Server » Server Utilities » how to import dump of one databse to another database (Oracle 10g , windows xp2)

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

how to import dump of one databse to another database [message #636337]

Wed, 22 April 2015 03:05

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

I have 2 systems both have oracle 10g database, and windows xp sp2 os.

The sid name of first system is UNNR, and second system is ORCL.

I exported dump file from first system using following command:-

>EXP UNR/UNR@UNNR FILE=D:\UNRDMP.DMP FULL=Y ROWS=N GRANTS=Y

Now i am trying to import this dump on my second system , but getting error.
following are the steps i took.

> conn / as sysdba
> create user unr identified by unr.
> Grant connect,resource,dba to unr

after that i tried following import command.

IMP UNR/UNR FILE=D:\UNRDMP.DMP FULL=Y ROWS=N GRANTS=Y

Right now i am in office so cant tell about error that i got in detail.

but my question is " How to import dump of user of one database to same user on another database.
Please help.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636339 is a reply to message #636337]

Wed, 22 April 2015 03:20

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Wed, 22 April 2015 13:35

both have oracle 10g database

Any reason for using old export/import rather than Data Pump?

Quote:

The sid name of first system is UNNR, and second system is ORCL.
IMP UNR/UNR FILE=D:\UNRDMP.DMP FULL=Y ROWS=N GRANTS=Y

You have not mentioned the SID.

Quote:

but getting error

What error?

Edit : Typo

[Updated on: Wed, 22 April 2015 04:57]

Report message to a moderator

Re: how to import dump of one databse to another database [message #636340 is a reply to message #636337]

Wed, 22 April 2015 03:28

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

Senior Member
Account Moderator

Grant connect,resource,dba to unr

NEVER do that.
Create your own roles, do NOT use Oracle ones, they are for Oracle use only.

Quote:

How to import dump of user of one database to same user on another database.

Using "imp".

Report message to a moderator

Re: how to import dump of one databse to another database [message #636343 is a reply to message #636339]

Wed, 22 April 2015 04:28

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@lalit i tried both unr/unr@unnr and unr/unr@orcl at the time of import , but both are giving error , what error, cant tell coz right now i am in a office & i have already mentioned that.

but in one line i have to say "how to import to user/pwd@sid1 from user/pwd@sid2"
i think i am clear now.

[Updated on: Wed, 22 April 2015 04:28]

Report message to a moderator

Re: how to import dump of one databse to another database [message #636344 is a reply to message #636340]

Wed, 22 April 2015 04:29

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@michel thanks for letting me know but it is not a real project, i m doing it for practice purpose only.
but this problem has made me ask one more question and that is "Can we have 2 sid names at a time? " Smile

Smile

[Updated on: Wed, 22 April 2015 04:33]

Report message to a moderator

Re: how to import dump of one databse to another database [message #636345 is a reply to message #636344]

Wed, 22 April 2015 04:53

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

jgjeetu wrote on Wed, 22 April 2015 10:29

i m doing it for practice purpose only.

So why not practice doing it properly? Smile

Smile

Report message to a moderator

Re: how to import dump of one databse to another database [message #636347 is a reply to message #636344]

Wed, 22 April 2015 04:55

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

Senior Member
Account Moderator

Quote:

"Can we have 2 sid names at a time? "

Yes.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636348 is a reply to message #636343]

Wed, 22 April 2015 04:57

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

Senior Member
Account Moderator

Quote:

how to import to user/pwd@sid1 from user/pwd@sid2

This is meaningless.

You import with a user which is known in the database you import.
Don't you think it'd be wise if I can import in your database because I am known in mine?

Report message to a moderator

Re: how to import dump of one databse to another database [message #636358 is a reply to message #636348]

Wed, 22 April 2015 05:39

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@michel & all , let me remind my profile is of oracle developer .

i dont have deep knowledge about dba activities.

Quote:

This is meaningless.

You import with a user which is known in the database you import.
Don't you think it'd be wise if I can import in your database because I am known in mine?

if it is meaning less then why import command written in 1st comment is not working ?

i think i should wait till evening once i'll reach home , i will provide the error details.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636359 is a reply to message #636358]

Wed, 22 April 2015 05:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

In my opinion, import and export is not a Database Administration activity. At my workplace, developers frequently restore the customer's database to debug. They don't ask the DBA to do it for them.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636360 is a reply to message #636359]

Wed, 22 April 2015 05:57

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

Senior Member
Account Moderator

@Lalit,

This is not correct.
In almost all my clients, DBA makes the export and import for developers for the good reason that no developers has access to production database and no developers has the privilege to import from another account (assuming production and development accounts are distinct).

Report message to a moderator

Re: how to import dump of one databse to another database [message #636361 is a reply to message #636358]

Wed, 22 April 2015 05:58

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

Senior Member
Account Moderator

Quote:

why import command written in 1st comment is not working ?

"is not working" is not an Oracle error message.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636364 is a reply to message #636361]

Wed, 22 April 2015 06:03

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

@michel let me reach home, i will provide error details.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636366 is a reply to message #636364]

Wed, 22 April 2015 06:08

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

Senior Member
Account Moderator

So why do you post a question (and waste others time) if you have the elements to provide.
Is not the actual and complete error message an obvious information to provide to solve the error?

Don't you know you have to ALWAYS copy and paste all what you do and get to allow us to help you to solve an error?

Report message to a moderator

Re: how to import dump of one databse to another database [message #636367 is a reply to message #636360]

Wed, 22 April 2015 06:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel Cadot wrote on Wed, 22 April 2015 16:27

assuming production and development accounts are distinct.

Yes, I was talking only about lower environments or test environments. I thought it was evident that I am not talking about production. Of course, access is restricted on production. Perhaps, I should be more explicit while saying that export/import is not ONLY DBA task. Developers do use it as and when required depending on the requirement and the environment they are working on.

Mostly the testing teams or sustenance teams, who do not work on production systems, frequently does the import and export of the customers DB to debug and work on the fix. Hope it makes sense now.

Probably, this might be a practical scenario, the DBA at customer's side does the export, while developers at the sustenance team does the import.

Anyway, I think we should not get deeper into this Smile

Smile

[Updated on: Wed, 22 April 2015 06:21]

Report message to a moderator

Re: how to import dump of one databse to another database [message #636372 is a reply to message #636367]

Wed, 22 April 2015 06:41

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

Senior Member
Account Moderator

Even in case of dev only environment, when now you use Data Pump, you write on the server and have to transfer files between servers.
For the first point, DBA is responsible of all database servers, even dev ones, and so you can't allow a developer to fill some FS with his dumps.
For the second point, you can't allow developer to have access to database servers, it just a security point (without speaking about the same issue than the previous one).

And, even with the old imp and exp, assuming you don't allow access to developers on the db server and so they export and import across the network, you can't let them overload this later which is often shared with other environment like office (mail, Internet... even if protected through firewalls) and sometimes even with production environment (not all enterprises can afford to have and manage multiple networks).

As I said, in about all the customers I worked, developers can't export and import.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636373 is a reply to message #636372]

Wed, 22 April 2015 06:50

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

in about all the customers I worked, developers can't export and import.

+1

Report message to a moderator

Re: how to import dump of one databse to another database [message #636375 is a reply to message #636373]

Wed, 22 April 2015 07:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I completely understand.

Presently where I work, the machines are isolated and dedicated only for bug reproduction. Thus, developers import the customer's dump themselves and then work on the bug. Yes, the infrastructure is quite good, almost each developer/tester is provided a dedicated DB server.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636409 is a reply to message #636375]

Thu, 23 April 2015 01:45

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

issue resolved, i forgot to configure tnsnames file, now i am able to import , though expecting this solution from seniors . anyways thanks all for the comments. Smile

Smile

[Updated on: Thu, 23 April 2015 01:45]

Report message to a moderator

Re: how to import dump of one databse to another database [message #636410 is a reply to message #636409]

Thu, 23 April 2015 01:50

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

Senior Member
Account Moderator

If you'd told us what was the error, we could immediately tell you what was its origin which has NOTHING to do with exp/imp.
We expect you post the relevant information next time.

Report message to a moderator

Re: how to import dump of one databse to another database [message #636411 is a reply to message #636410]

Thu, 23 April 2015 04:05

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

jgjeetu@gmail.com

sorry my fault, but i was so keen to get the solution , so couldn't wait to reach home.
but i will make sure that this may not happen next time Smile

Smile

Report message to a moderator

Re: how to import dump of one databse to another database [message #636412 is a reply to message #636411]

Thu, 23 April 2015 04:41

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Understood. But Copy & Pasting is worth a thousand words for next time Smile

Smile

Report message to a moderator

Previous Topic:	Can we perform export/import from source & target db having different db_block_size ?
Next Topic:	load data from another table/flat file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 00:41:04 CST 2025