ORA-39002, ORA-39070, ORA-29283, ORA-6512 Using DataPump Export EXPDP or Import IMPDP
During DataPump export or import received below error messages:
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
Above errors are having various reason, these are,
Reason 1)
Listener process has not been started under the same account as the database instance service.
Check the output of,
Ps –ef | grep pmon
Ps –ef | grep tnslsnr
SolutionWhen using ASM, it might possible that listener may have been started from ASM home instead of RDBMS home and security settings not accepting request.
Reason 2)
In RAC instance, sometimes you may face trouble by using connection string, example is given as below,
expdp scott/tiger@mydb directory=expimp dumpfile=scott.dmp logfile= scott.log schemas=scott
Oracle has explain very well i.e.,
“The reason can be that the connect string (TNS Name) is a load balancing connect string and whenever you try to use the connect string with expdp/impdp, it goes to the other node where the directory information is not available, or the directory might be a local folder which is not a shared one.”
Solution1) Use expdp/impdp without connection string
2) Make sure directory are shared between all nodes and permissions are correct.
Reason 3)
One Single node or RAC instance both with connection string and without connection string may not work due to non existence of directory or path may be different.
SolutionWhen creating directory using “create directory” command, directory should be created on same path and on both nodes in RAC instance.
Reason 4)
Directories are available but ownership or not accessible to oracle user.
SolutionGrant required permission to oracle user from where import/export is performed.
Note: At last must check user has proper permission to export to run utl_file package.
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
