oracle data pump import ORA-39002 with ORA-39070, ORA-29283 and others on Windows 10

Question

I am using data pump to perform an import on 4 .dmp files and keep on receiving the set of errors as below:

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 am new to oracle and cannot find a helpful solution.

I am performing the import as in here, although I'm using oracle 12c.

The command I run in the windows command like looks like this:

impdp user/pass@db_name directory=DUMP_DIR dimpfile="file_name.dmp" schemas=schema_name content=all parallel=4

DUMP_DIR is created in oracle and appropriate privs were granted.

I also ran this command with

... logfile=file_name.log

added at the end but I'm not sure if the log file was created or where it was saved.

I have found this - it's about exactly the same set of errors but on export and on linux. At the end of the answer there's a sentence 'If we are on a Windows machine, then we need to make sure that both the listener and the database have been started with the exact same username.' Is this useful in case of import? If yes - what does it mean exactly?

Answer 1

There's a great short answer here, which is basically "The database isn't able to write to the log file location."

The link above suggests a simple test to troubleshoot the issue.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('DUMP_DIR', 'test.txt', 'w');
  utl_file.put_line(f, 'test');
  utl_file.fclose(f);
end;
/

If this fails, Oracle can't write to that directory at all, probably because of Windows file permissions. Check which Windows user(s) the Oracle services are running as, and change the folder permissions to allow them write access.

If that worked, it's a problem specific to impdp. You might try changing your command string - one option might be to specifically write your log file to a different Oracle directory, e.g. logfile=DATA_PUMP_DIR:file_name.log.

If none of these options work, you can also disable the logfile completely by using NOLOGFILE=Y, but you'll have to monitor the impdp output on your console, because it won't get saved anywhere else.