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?
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?
ASKER
how can I find that out?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>what OS are you using?
Since you mention 'su' I assume Unix.
Once you find the folder:
ls -al /path/to/folder
Since you mention 'su' I assume Unix.
Once you find the folder:
ls -al /path/to/folder
ASKER
I know my DATA_PUMP_DIR, I just need to find the owner. I am using UNIX AIX
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tried it and it created the file
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;
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;
ASKER
same thing
One more shot in the dark:
GRANT read, write ON DIRECTORY DATA_PUMP_DIR TO system;
GRANT read, write ON DIRECTORY DATA_PUMP_DIR TO system;
ASKER
made no difference:( I don't know what to do
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I looked at that one already but looks like listener works fine - I ran lsnrctl status and didn't get any errors
ASKER
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
TNS-12505: TNS: listener does not currently know of SID given in connect descriptor
>>found the following error:
Are those errors about the same time as the impdp runs?
Are those errors about the same time as the impdp runs?
ASKER
yes.
So I checked the listener.ora specified when I run lsnrctl status and there is no entry for the SID I am using
So I checked the listener.ora specified when I run lsnrctl status and there is no entry for the SID I am using
ASKER
current contents of listener.ora are as follows:
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)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server03.domain.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oraclehow 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
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.
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.
>>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.
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.
ASKER
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!
The Oracle database user isn't the issue.