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?

Is "dimpfile" a typo, or is that what it says in your command? — kfinity, Jun 18 '18 at 14:02

score 6 · Answer 1 · answered Jun 18 '18 at 14:22

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.

score 2 · Answer 2 · answered Jan 31 '19 at 22:12

The problem You have is Your Oracle is not able to write to DIRECTORY (DUMP_DIR) you specified. In Windows 10, It behaves unpredictably. Solution

Create another Oracle directory with preferably in C:\Users\Public\ folder, where you are 100% sure access would not be issue. CREATE OR REPLACE DIRECTORY DUMP_DIR_2 AS 'C:\Users\Public\<name>
Give Grants GRANT READ, WRITE ON DIRECTORY DUMP_DIR_2 TO schema_name;
Copy your dump file to newly created folder.
Fire your import command

score 0 · Answer 3 · answered Sep 14 '22 at 09:53

First is very important the Oracle have the permission to write and read the folder. If you already test this, try the solution bellow:

I had the same situation, in my case the command was (password is only for an instance) :

impdp 'sys/passExample as sysdba' directory=C:/oracle/oradata/EXEMPLODB dumpfile=preupd.bak

I put the preup.bak into the folder EXEMPLODB

The correct is change the directory folder by the name of directory, the correct command is:

impdp 'sys/passExample as sysdba' directory=EXT_DATA_FILES dumpfile=preupd.bak

The EXT_DATA_FILES is the directory name, I found with the query

select * from all_directories;

into the system db.

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

3 Answers3