Solved

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

Posted on 2013-05-21
21
9,024 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
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: 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
 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
su - oracle could not open session 6 77
Use of Exception to end a Loop 3 31
Create Index on a Materialized View 5 24
Oracle - Query link database loop 8 39
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

777 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