CODEWITHSUNDEEP
oracle-databaseoracle12cdatapump
user9517303
user9517303

Reputation:

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

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?

Upvotes: 9

Views: 106260

Answers (4)

Helder Dos Santos
Helder Dos Santos

Reputation: 1

I was exporting scheme from oracle 11g, on Oracle Linux OS.

expdp "'sys/pass@PEDX as sysdba'" dumpfile=05042023.dmp LOGfile=05042023.log directory=EXPORT schemas=DEMO

I got this error messagem:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation: cannot open file [29435]

The problem was on the directory export, it ISCSI drive and was not mounted on my linux.

Upvotes: 0

Edson Vicente Carli Junior
Edson Vicente Carli Junior

Reputation: 158

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.

Upvotes: 0

Susil Parida
Susil Parida

Reputation: 400

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

  1. 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>

  2. Give Grants GRANT READ, WRITE ON DIRECTORY DUMP_DIR_2 TO schema_name;

  3. Copy your dump file to newly created folder.

  4. Fire your import command

Upvotes: 3

kfinity
kfinity

Reputation: 9091

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.

Upvotes: 7

Related Questions