Solved

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

Posted on 2013-05-21
21
8,824 Views
Last Modified: 2013-05-21
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
Comment
Question by:YZlat
  • 11
  • 10
21 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What OS user/group owns the folder defined by DATA_PUMP_DIR?

The Oracle database user isn't the issue.
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
how can I find that out?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>what OS are you using?

Since you mention 'su' I assume Unix.

Once you find the folder:
ls -al /path/to/folder
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
I know my DATA_PUMP_DIR, I just need to find the owner. I am using UNIX AIX
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Doesn't look like a permission issue.

Just for grins while sitting in that folder try the following command:
touch myschema.log
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
tried it and it created the file
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
same thing
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
One more shot in the dark:
GRANT read, write ON DIRECTORY  DATA_PUMP_DIR TO system;
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
made no difference:( I don't know what to do
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
I looked at that one already but looks like listener works fine - I ran lsnrctl status and didn't get any errors
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>found the following error:

Are those errors about the same time as the impdp runs?
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now