Solved

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

Posted on 2013-05-21
21
8,923 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)
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 76

Assisted Solution

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

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 76

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
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)
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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 76

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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

919 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

19 Experts available now in Live!

Get 1:1 Help Now