Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-21
21
Medium Priority
?
9,506 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
21 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184522
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
ID: 39184569
how can I find that out?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 39184581
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184585
>>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
ID: 39184601
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
ID: 39184606
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39184636
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
ID: 39184644
tried it and it created the file
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39184836
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
ID: 39184981
same thing
0
 
LVL 77

Expert Comment

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

Author Comment

by:YZlat
ID: 39185166
made no difference:( I don't know what to do
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 39185191
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
ID: 39185294
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
ID: 39185310
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39185319
>>found the following error:

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

Author Comment

by:YZlat
ID: 39185361
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
ID: 39185377
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39185482
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39185491
>>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
ID: 39185505
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

688 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