Home » RDBMS Server » Server Utilities » IMP Datapump error (Oracle 10.2.0.4 , Linux AS4)
IMP Datapump error [message #391606] Thu, 12 March 2009 12:57 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I did expdp on production and trying to impdp into test environment for refresh .

The export datapump was successful but after moving the file to the test environment , when I'm trying to import I'm getting the below error :

[oracle@usfldbd7 export]$ impdp system/me3taud1t parfile=salep1_salepoint2_mar12.par

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 12 March, 2009 13:42:45

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/oracle/data/SALET01/dpump/salep1_salepoint2_mar12.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3.

And these are the contents in my parfile :

directory=dpump
dumpfile =salep1_salepoint2_mar12.dmp
schemas=SALEPOINT2
logfile=salep1_salepoint2_mar12.log

I have created the directory dpump in test and gave the permissions also . But still the same .

Can somebody help me where I'm wrong .

Regards,
Raj

Re: IMP Datapump error [message #391608 is a reply to message #391606] Thu, 12 March 2009 13:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Did you ftp in binary mode?
And what is your value for DATA_PUMP_DIR in DBA_DIRECTORIES?

[Updated on: Thu, 12 March 2009 13:06]

Report message to a moderator

Re: IMP Datapump error [message #391609 is a reply to message #391606] Thu, 12 March 2009 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many times do we have to say the exp/imp is NOT a backup strategy and so must not be in backup forum but in Server Utilities one (which description is "Datapump, Import, Export, ...", obvious doesn't it?)

You missed something as Oracle is unable to read your file.

Regards
Michel
Re: IMP Datapump error [message #391610 is a reply to message #391606] Thu, 12 March 2009 13:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I did expdp on production
Are you sure you used expdp?
Re: IMP Datapump error [message #391611 is a reply to message #391606] Thu, 12 March 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
post results from

ls -l /opt/oracle/data/SALET01/dpump/salep1_salepoint2_mar12.dmp
Re: IMP Datapump error [message #391612 is a reply to message #391610] Thu, 12 March 2009 13:10 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Yes ,

We used expdp only for export . When I give ls -ls , I get

-rw-r--r--  1 oracle dba 15248666624 Mar 12 07:33 salep1_salepoint2_mar12.dmp
-rw-r--r--  1 oracle dba       24194 Mar 12 07:30 salep1_salepoint2_mar12.log


Edit: Mahesh Rajendran
Added CODE tags.

[Updated on: Thu, 12 March 2009 13:20] by Moderator

Report message to a moderator

Re: IMP Datapump error [message #391613 is a reply to message #391611] Thu, 12 March 2009 13:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
OraFAQ at its best.
4 responses within 1 minute!
Thought I was the only one NOT restoring/recovering anything today at work Wink

[Updated on: Thu, 12 March 2009 13:16]

Report message to a moderator

Re: IMP Datapump error [message #391614 is a reply to message #391612] Thu, 12 March 2009 13:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems you messed with directory creation or its permissions inside oracle.
Re: IMP Datapump error [message #391616 is a reply to message #391606] Thu, 12 March 2009 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
use CUT & PASTE to show command & results


ls -l /opt/oracle/data/SALET01/dpump/salep1_salepoint2_mar12.dmp

Re: IMP Datapump error [message #391617 is a reply to message #391616] Thu, 12 March 2009 13:22 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
[oracle@usfldbd7 dpump]$ ls -l /opt/oracle/data/SALET01/dpump/salep1_salepoint2_mar12.dmp
-rw-r-----  1 oracle dba 143360 Mar 12 14:01 /opt/oracle/data/SALET01/dpump/salep1_salepoint2_mar12.dmp


[Updated on: Thu, 12 March 2009 13:26] by Moderator

Report message to a moderator

Re: IMP Datapump error [message #391618 is a reply to message #391617] Thu, 12 March 2009 13:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Use CODE tags!.

And now, why is filesize different from the previous posting?
>>143360
Re: IMP Datapump error [message #391619 is a reply to message #391606] Thu, 12 March 2009 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
use CUT & PASTE to show command & results


env | sort

Please NOTE in this thread the salep1_salepoint2_mar12.dmp files are different files!
Re: IMP Datapump error [message #391620 is a reply to message #391618] Thu, 12 March 2009 13:36 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
The below was the syntax , i used to expdp

expdp system/*** parfile=abc.par

abc.par
directory= DATA_PUMP_DIR
dumpfile= salep1_salepoint2_mar12.dmp
SCHEMAS=SALEPOINT2
logfile= salep1_salepoint2_mar12.log
exclude=statistics
Flashback_scn=5046872170694

And after that ftp the file in binary mode . Created dpump directory and gave the permission and impdp the file as

impdp system/*** parfile=abc.par

abc.par=
directory=dpump
dumpfile=salep1_salepoint2_mar12.dmp
logfile=salep1_salepoint2_mar12.log
schemas=salepoint2

Is there anything wrong in the syntax , please let me know .

Or if you can send me the syntax , I can do it once again.

[Updated on: Thu, 12 March 2009 13:38]

Report message to a moderator

Re: IMP Datapump error [message #391621 is a reply to message #391620] Thu, 12 March 2009 13:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

>> Created dpump directory and gave the permission
Post what you did.
And answer please answer Joy's question.
>>And what is your value for DATA_PUMP_DIR in DBA_DIRECTORIES?
Re: IMP Datapump error [message #391622 is a reply to message #391621] Thu, 12 March 2009 13:50 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
I did drop that directory and re-created a new one and did the import again as

SQL> create directory dpump as '/opt/oracle/db/rdbms/log/dpump';

Directory created.

SQL> grant READ, WRITE ON DIRECTORY dpump to SALEPOINT2;

Grant succeeded.


[oracle@usfldbd7 data]$ impdp system/me3taud1t dumpfile=salep1_salepoint2_mar12.dmp logfile=salep1_salepoint2_mar12.log schemas=salepoint2 directory=dpump

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 12 March, 2009 14:32:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/oracle/db/rdbms/log/salep1_salepoint2_mar12.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- ------------------------------
SYS DPUMP /opt/oracle/db/rdbms/log/dpump


And while FTP , I use bin and did the FTP .
Re: IMP Datapump error [message #391623 is a reply to message #391622] Thu, 12 March 2009 13:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You grant the perm to SALEPOINT2 and do the import as system.
Why?
Re: IMP Datapump error [message #391624 is a reply to message #391623] Thu, 12 March 2009 14:00 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
No need to grant the permission to salepoint2 schema?

And we did the expdp with system schema only .

mahesh ,
Is the above syntax in any manner ?

If it's wrong please tell me how to follow it for now.
Re: IMP Datapump error [message #391627 is a reply to message #391624] Thu, 12 March 2009 14:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to grant the perm to user who will be accessing the file.
In this case, the user used to import.
>>And we did the expdp with system schema only .
That was in the other database.
Who knows the status of System user in this database?
Do not user SYS/SYSTEM for these kind of operations.
Re: IMP Datapump error [message #391631 is a reply to message #391627] Thu, 12 March 2009 14:24 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Mahesh,

So for a final recap , the syntax will look like :

impdp salepoint2/*** dumpfile=salelpoint.dmp logfile=salepoint.log directory=dpump schemas=salepoint2

Is the above syntax OK .

And should I be in the same directory as dpump and move the dumpfile to that directory and do the IMPDP.

please let me know.
Re: IMP Datapump error [message #391633 is a reply to message #391631] Thu, 12 March 2009 14:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You granted the permission to system. Try as system.

Re: IMP Datapump error [message #391635 is a reply to message #391606] Thu, 12 March 2009 14:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>ORA-31640: unable to open dump file "/opt/oracle/db/rdbms/log/salep1_salepoint2_mar12.dmp" for read

For what ever reason it is NOT looking for the file in the dpump folder!

a softlink would likely resolve this mess.
Re: IMP Datapump error [message #391637 is a reply to message #391635] Thu, 12 March 2009 14:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

>>a softlink would likely resolve this mess.
Totally agree!.
Re: IMP Datapump error [message #391644 is a reply to message #391635] Thu, 12 March 2009 15:46 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi all,

The impdp works well now. Thanks again to all of you for your time on this. The object count of both production and test are correct but in the test I got the below error :

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'SALEP_TO_ILOG' does not exist
Failing sql is:
GRANT SELECT ON "SALEPOINT2"."TRENDWEST_MF_CONTRACTS_V" TO "SALEP_TO_ILOG"

What does it indicate

The specified role is not even there in the production box also.

[Updated on: Thu, 12 March 2009 15:50]

Report message to a moderator

Re: IMP Datapump error [message #391646 is a reply to message #391644] Thu, 12 March 2009 15:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The specified role is not even there in the production box also.
Well, it has to be!.
It could be a user or a role.
Re: IMP Datapump error [message #391652 is a reply to message #391646] Thu, 12 March 2009 16:09 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Yes Mahesh , it's a user in prodution. But the object count in Prod and Test is the same for that schema .

So , you mean to say the IMPDP has gone wrong somewhere ?


Re: IMP Datapump error [message #391656 is a reply to message #391652] Thu, 12 March 2009 17:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> So , you mean to say the IMPDP has gone wrong somewhere ?
Where did I say that?
You exported schema A along with grants.
Schema B has some kind of access to objects in Schema A.
In target you imported schema A. Impdp is trying to grant the same access to schema B in target.
Either recreate the schema B in target (if you want)
else
Just ignore.
Object counts have nothing to with this. Grants are NOT objects.

Re: IMP Datapump error [message #391679 is a reply to message #391656] Fri, 13 March 2009 00:51 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

>>We used expdp only for export . When I give ls -ls , I get

Post your export (mean expdp) log file.

Babu
Previous Topic: ORA-01555..during export
Next Topic: Getting EXP-00091, error- ora 04031 while performing export
Goto Forum:
  


Current Time: Sat Dec 03 06:09:49 CST 2016

Total time taken to generate the page: 0.08502 seconds