Home » RDBMS Server » Backup & Recovery » Unable to import .DMP file.
icon5.gif  Unable to import .DMP file. [message #200997] Thu, 02 November 2006 03:29 Go to next message
grahambo
Messages: 9
Registered: November 2006
Junior Member
Hi

I wonder if anybody can help me. I created a *.dmp file from a database using the 'exp' untility.

the dump is of the entire database being exported. I now have created a new database with a different SID and am trying to import the file (I basically am testing that this way of backing up our DB actually works, i will be deleting this test Restore DB as soon as the import of the dump file is completed).

However I have encountered a problem when I try to import. It does not import the users correctly and is throwing up and error.

////////////Here is how I export the dump file

D:\oracle\ora92\rdbms\admin>exp

Export: Release 9.2.0.6.0 - Production on Thu Nov 2 09:22:09 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Username: sys as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > C:\mydump.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > E

Export grants (yes/no): yes > yes

Export table data (yes/no): yes > yes

Compress extents (yes/no): yes > yes

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)


//////////// And here is how I attempt to import the dump file

D:\oracle\ora92\rdbms\admin>set Oracle_SID=test

D:\oracle\ora92\rdbms\admin>imp

Import: Release 9.2.0.6.0 - Production on Thu Nov 2 09:28:51 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Import file: EXPDAT.DMP > C:\mydump.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
List contents of import file only (yes/no): no > no

Ignore create error due to object existence (yes/no): no > no

Import grants (yes/no): yes > yes

Import table data (yes/no): yes > yes

Import entire export file (yes/no): no > yes

And Here is the Error I am getting!

"ALTER SESSION SET CURRENT_SCHEMA= "RR_OWNER""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully

Can anyone help, My knowledge of oracle is very very limited. If you have a solution can you please explain it very clearly thanks

Graham
Re: Unable to import .DMP file. [message #201010 is a reply to message #200997] Thu, 02 November 2006 04:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Search the forum and FAQ section.
There are many many examples around.
It is easy to use exp/imp in non-interactive mode.
take an export
Exp user/pass@source full=y compress=n file=yourdmpfile.dmp 

do an import. You can import a single user like this
imp user/pass@target fromuser=x touser=y

If you are import into another database then the physical location of datafiles may change and so tablespace creation and user creation may fail.
TO avoid it, precreate the the user and tablepspaces and import again.


Re: Unable to import .DMP file. [message #201484 is a reply to message #201010] Sat, 04 November 2006 15:10 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Mahesh is right - And You have probably only given us the last error, not the initial error, which are responsible.

Precreating tablespaces and users are a good way of handling issues when moving a database from one server to another, where paths etc. are not similar, fx. Windows to Linux etc....

Mahesh - do not take this as an insult, but when You write

Quote:

If you are import into another database then the physical location of datafiles may change



I really do not hope any export/import changes the location of my datafiles!!!

Smile
Kim
Re: Unable to import .DMP file. [message #201491 is a reply to message #201484] Sat, 04 November 2006 16:23 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I stated:
>> If you are import into another database then the physical
>> location of datafiles may change and so tablespace creation and user creation may fail.
What i actually meant is
By default the full database import will try to recreate the datafiles in the same location ( as found
in exported dump). If the directory structure is different in the target machine eventually tablespace creation
and user creation will fail.
I apologize for using sloppy description i did earlier and many thanks to Kim for pointing it out.
Apparently, i should stop posting any lackadaisical responses by the wee hours. Smile
>>Re: Unable to import .DMP file. [message #201010 is a reply to message #200997 ] Thu, 02 November 2006 05:17

Kim wrote:
>>Mahesh - do not take this as an insult,
Nope. Never. Healthy criticism will bring better results and help to fine tune the skills.
So i actually owe you one here
http://www.orafaq.com/forum/t/65804/42800/

[Updated on: Sat, 04 November 2006 16:41]

Report message to a moderator

Previous Topic: help me to recover data
Next Topic: Lost of Recovery catalog & Control file
Goto Forum:
  


Current Time: Thu Dec 08 08:30:06 CST 2016

Total time taken to generate the page: 0.08661 seconds