Oracle 11g dmp import. ORA-39000 / ORA-39143 Errors

Question

iam really new in oracle and databases at all. So sorry for a maybe stupid question.

Here is my problem. I have a DB export (not mine so i dont know how it was exported: are there differences?) and i want to import it with the following script:

@Echo off
Break off
Rem fullimpdp.cmd
set NLS_LANG=american_america.WE8MSWIN1252
set ORACLE_HOME=C:\Oracle\ora11
Set DUMP_HOME=C:\DBDump
set /p ORACLE_SID="Oracle_sid = "
set /p FILE="Dump file name = "
if exist %DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql del /F /Q %DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
echo CREATE OR REPLACE DIRECTORY backup_dir AS '%DUMP_HOME%'; >%DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
echo CREATE OR REPLACE DIRECTORY dmpdir AS '%DUMP_HOME%'; >>%DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
echo commit; >>%DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
echo exit    >>%DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
set ORACLE_SID=%ORACLE_SID%
%ORACLE_HOME%\bin\sqlplus xpower/xpower @%DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
%ORACLE_HOME%\bin\impdp xpower/xpower FULL=y DIRECTORY=backup_dir DUMPFILE=%FILE% logfile=impdp_%ORACLE_SID%.log JOB_NAME=impfull_%Oracle_sid%
if exist %DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql del /F /Q %DUMP_HOME%SetBackupDir_%ORACLE_SID%.sql
pause

But not the following errors occur:

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
ORA-39000: bad dump file specification
ORA-39143: dump file "C:\DBDump\ev122.dmp" may be an original export dump file

What is wrong? How can i fix it in this script? And how can i import the file not using the script? I read about the imp file=filename command. but where to type it in? :)

Thank you.

BR

Sufism

This message "may be an original export dump file" tells you what the problem is. You need to use the (deprecated) imp tool, not the new impdp tool. Note that imp works on files located on the client (where imp is running) – Trawler

and how do i use the imp tool? – Sufism

Answer 1

Try like this,

imp xpower/xpower FULL=y file=<file_name>.dmp log=log_file_name.log

Or you can import using system user,

imp system/<password> file=<file_name>.dmp log=log_file_name.log fromuser = <from_user_name> touser= xpower

Lynnett

Thank you. and how do i choose the database i want to import in? – Sufism

You can mention the dbname in imp command. like, imp <username>/<password>@dbname .... – Lynnett

Thank you. But where should i typ that command in? sorry - but iam really new :) – Sufism

You need to run this command in the path where your dumpfile exists. – Lynnett

or you can use file=/path/to/dump.dmp and run it anywhere.. Thank you btw, you saved a critical data today :) – Lubricant

Just to mention that this way can be used to import files exported by user in TOAD. To Oracle new comers this can be a life saver. – Therewithal

Answer 2

impdp command is used to import dump files exported by data pump. Try to use "imp" command.

Ethelynethene

Answer 3

importing dump file in oracle 11g

In SQL Command Line,

1. CREATE USER username IDENTIFIED BY password

2. grant dba to username;

In Command Line,

1. imp userid=username/password FULL=y FILE="D:\yourdumpfilename.dmp"

Note ..Be sure the path of your dump file .

Ananias

Answer 4

@user2794543

Go to your Command-Prompt (Ctrl+R on your desktop -> type cmd in search box -> hit enter -> you will land in Command Prompt).

Now, change your directory to root C: (type "cd.." -> hit enter -> type "cd.." gain -> hit enter -> now you are at root C: directory).

Next you have to import using "imp" and not "impdp". Type the following command line :

imp userid=system/[password]  file=[drive]:\[folder]\[dump_file_name].dmp full=y;

Hit enter and wait for the process flow.

*Ignore the square braces. They are for referencial purpose.

Pear

Answer 5

DMP was exported by imp.exe utility of Oracle 12c and I was getting an error while trying import by imp.exe of Oracle 11g. Resolution in my case is: copy and paste imp.exe from 12c to 11g ...\bin folder and succesfully imported.

Truett

Answer 6

Below example worked for me:

imp userid=solfis/solfis  file=fissetup_110.dmp full=y;

Niemann

Answer 7

If you are still having trouble use imp to import .dmp file in 19c database. write imp in powershell and sql will drive you through all the process.

Headsman