Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10031
  • Last Modified:

Errors ORA-39002, ORA-39070, ORA-29283, and ORA-06512 while running impdp

I am running data import using data pump impdp (Oracle 11g)

impdp system directory= DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log schemas=’MYSCHEMA’

I get prompted for password and after I enter it, the import seems to take off but then I get an error:


Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 21 May, 2013 8:27:58

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I su as an oracle user and run the tool under system account. DATA_PUMP_DIR is defined and I cd into it before running the impdp

Usually this error occurs when the user does not have read and write permissions to DATA_PUMP_DIR, but system user would not have this issue, would it?

Can anyone help to resolve this issue?
0
YZlat
Asked:
YZlat
  • 11
  • 10
3 Solutions
 
slightwv (䄆 Netminder) Commented:
What OS user/group owns the folder defined by DATA_PUMP_DIR?

The Oracle database user isn't the issue.
0
 
YZlatAuthor Commented:
how can I find that out?
0
 
slightwv (䄆 Netminder) Commented:
Find out what?

To find the folder:  Look in v$parameters, spfile, from sqlplus: show parameter DATA_PUMP_DIR.

To find the 'owner', what OS are you using?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
slightwv (䄆 Netminder) Commented:
>>what OS are you using?

Since you mention 'su' I assume Unix.

Once you find the folder:
ls -al /path/to/folder
0
 
YZlatAuthor Commented:
I know my DATA_PUMP_DIR, I just need to find the owner. I am using UNIX AIX
0
 
YZlatAuthor Commented:
ls -al /path/to/folder

returned:


total 27982784
drwxr-xr-x    2 oracle   dba             256 May 21 08:14 .
drwxr-xr-x    4 oracle   dba             256 May 21 08:14 ..
-rwxr--r--    1 oracle   dba      14327181312 May 20 16:32 myschema.dmp
0
 
slightwv (䄆 Netminder) Commented:
Doesn't look like a permission issue.

Just for grins while sitting in that folder try the following command:
touch myschema.log
0
 
YZlatAuthor Commented:
tried it and it created the file
0
 
slightwv (䄆 Netminder) Commented:
I've been trying to find something on this error.

If you are running the import as SYSTEM then there shouldn't be any issues.

Just for grins try re-granting permissions to system.

Connect as SYS and do:
grant imp_full_database to SYSTEM;
0
 
YZlatAuthor Commented:
same thing
0
 
slightwv (䄆 Netminder) Commented:
One more shot in the dark:
GRANT read, write ON DIRECTORY  DATA_PUMP_DIR TO system;
0
 
YZlatAuthor Commented:
made no difference:( I don't know what to do
0
 
slightwv (䄆 Netminder) Commented:
You might find this interesting.  I would have never thought of this.

Check out the following note on Oracle Support:
Errors ORA-39002 ORA-39070 ORA-29283 ORA-6512 When Using DataPump Export (EXPDP) or Import (IMPDP) [ID 1305166.1]
0
 
YZlatAuthor Commented:
I looked at that one already but looks like listener works fine - I ran lsnrctl status and didn't get any errors
0
 
YZlatAuthor Commented:
Actually, I just checked the alert log and found the following error:
TNS-12505: TNS: listener does not currently know of SID given in connect descriptor
0
 
slightwv (䄆 Netminder) Commented:
>>found the following error:

Are those errors about the same time as the impdp runs?
0
 
YZlatAuthor Commented:
yes.

So I checked the listener.ora specified when I run lsnrctl status and there is no entry for the SID I am using
0
 
YZlatAuthor Commented:
current contents of listener.ora are as follows:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = mn11_ABCD.domain.com)
     (SERVICE_NAME = mn11.domain.com)
      (SID_NAME = mn11)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server03.domain.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Open in new window


how should I add an entry for SID DEV_DB?

Would the following work?

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = mn11_ABCD.domain.com)
     (SERVICE_NAME = mn11.domain.com)
      (SID_NAME = mn11)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
    )
(SID_DESC =
     (GLOBAL_DBNAME = DEV_DB.domain.com)
     (SERVICE_NAME = DEV_DB.domain.com)
      (SID_NAME = DEV_DB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server03.domain.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
The instance should automatically register with the listener when it is started.

Check the local_listener init parameter in the database.  Make sure it matches the listener the server is using.

You can always manually hard-code the service_name in the listener.ora file if you wish.
0
 
slightwv (䄆 Netminder) Commented:
>>Would the following work?

Sorry, I should have refreshed.  I didn't see this last post.

It doesn't look quite right.  Oracle is very picky about the syntax in the tnsnames and listener files.

Remove the blank line and make sure nothing you add is in column 1.  As long as the parans line up, it should work.

It's easy enough to test.  Just try to start the listener with the new config file.
0
 
YZlatAuthor Commented:
I found the problem - I forgot UNIX was case sensitive and I set DATA_DUMP_DIR to a path that had one letter capitalized. As soon as I fixed the problem, it worked. Thank you so much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now