Home » RDBMS Server » Server Utilities » how to import dump of one databse to another database (Oracle 10g , windows xp2)
how to import dump of one databse to another database [message #636337] Wed, 22 April 2015 03:05 Go to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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".

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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@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

[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 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, 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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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


Yes.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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?

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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@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.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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).

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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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


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

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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@michel let me reach home, i will provide error details.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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?

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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
in about all the customers I worked, developers can't export and import.

+1
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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.
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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
jgjeetu
Messages: 363
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

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
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 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Understood. But Copy & Pasting is worth a thousand words for next time Smile
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:
  


Current Time: Mon Jun 17 14:48:01 CDT 2019