How to import an Oracle database from dmp file and log file?

Question

How would I go about creating a database from a dump file? I do not have an existing database with the same structure on my system so it has to be complete with jobs, events, tables, and so on.

I placed the dump and log file in E: drive

I have tried the import utility

E:/>impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp

But I'm getting error as

invalid argument value
bad dump file specification
unable to open dump file "E:\app\admin\oratest\dpdump\WB_PROD_F
ULL_20MAY11.dmp" for read
unable to open file
unable to open file
(OS 2) The system cannot find the file specified.

And when I see in Windows Explorer DMP file(taken from Linux server) is showing as Crash dump file

I don't understand how I can resolve this issue. Please help me to solve this issue.

I'm a complete newbie on Oracle...

Pampa

Answer 1

How was the database exported?

• If it was exported using exp and a full schema was exported, then

  1. Create the user:

     create user <username> identified by <password> default tablespace <tablespacename> quota unlimited on <tablespacename>;
     

  2. Grant the rights:

     grant connect, create session, imp_full_database to <username>;
     

  3. Start the import with imp:

     imp <username>/<password>@<hostname> file=<filename>.dmp log=<filename>.log full=y;
     

• If it was exported using expdp, then start the import with impdp:

  impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;
  

Looking at the error log, it seems you have not specified the directory, so Oracle tries to find the dmp file in the default directory (i.e., E:\app\Vensi\admin\oratest\dpdump\).

Either move the export file to the above path or create a directory object to pointing to the path where the dmp file is present and pass the object name to the impdp command above.

Desimone

to export they used exp, and they used Owner in the cmnd. so i used imp cmnd here i donot create the user i tried directly with imp cmnd im able to import tables to local db but im not seeing some tables and packages imported. Please tell me how i can solve this issue. Thank you very much for reply – Pampa

edit your question and post the import process logs @Praveenb – Desimone

this is imp cmnd i used, imp userid=system/tiger FILE="e:\WB_PROD_FULL_20MAY11.dmp" FUll=y, i donot know where import log is stored please let me know thanks – Pampa

use imp userid=system/tiger FILE="e:\WB_PROD_FULL_20MAY11.dmp" log =e:\wb_prod_imp.log FUll=y & the log will be in your e: drive @Praveenb – Desimone

Thankyou very much for you help, after importing db im seeing import terminated successfully with warnings. i there any thing i can work out with these warnings or i shall leave it..... – Pampa

if it's warnings.. it should be fine, but it's best to ensure there are no warnings or errors. @Praveenb – Desimone

what about invalid operation? – Seely

@Sathya : is it necessary to put a DUMP file into `E:\app\Vensi\admin\oratest\dpdump` dir??? I mean can't we import a File from different Location? – Mignonne

I have a related question. Are you willing to help me with it? I am brand new to oracle, so all these commands are new to me, though i am a seasoned app developer and all i need to do is get data into a new oracle installation. Here is the link: dba.stackexchange.com/questions/130515/… – Scooter

The most of people who are not the DBA (same as me) needs to resolve the 'UDI-00014' (#21182696 ) even if you noticed to this in your last two sentence, because is not obvious what 'create a directory object' exactly means – Declamation

A directory object is an alias to a directory on the file system and can be used in specific sql commands to refer to files. You can create such an object by using the following example: CREATE OR REPLACE DIRECTORY your_dir AS '/usr/your_dir'; – Weimer

If you want to specify an SID and login as sysdba: impdb \'username/password@ORCL as sysdba\' – Corticosteroid

Answer 2

All this peace of code put into *.bat file and run all at once:

My code for creating user in oracle. crate_drop_user.sql file

drop user "USER" cascade;
DROP TABLESPACE "USER";

CREATE TABLESPACE USER DATAFILE 'D:\ORA_DATA\ORA10\USER.ORA' SIZE 10M REUSE 
    AUTOEXTEND 
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT  AUTO
/ 

CREATE  TEMPORARY TABLESPACE "USER_TEMP" TEMPFILE 
    'D:\ORA_DATA\ORA10\USER_TEMP.ORA' SIZE 10M REUSE AUTOEXTEND
    ON NEXT  5M  EXTENT MANAGEMENT LOCAL 
    UNIFORM SIZE 1M    
/

CREATE USER "USER"  PROFILE "DEFAULT" 
    IDENTIFIED BY "user_password" DEFAULT TABLESPACE "USER" 
    TEMPORARY TABLESPACE "USER_TEMP" 
/    

alter user USER quota unlimited on "USER";

GRANT CREATE PROCEDURE TO "USER";
GRANT CREATE PUBLIC SYNONYM TO "USER";
GRANT CREATE SEQUENCE TO "USER";
GRANT CREATE SNAPSHOT TO "USER";
GRANT CREATE SYNONYM TO "USER";
GRANT CREATE TABLE TO "USER";
GRANT CREATE TRIGGER TO "USER";
GRANT CREATE VIEW TO "USER";
GRANT "CONNECT" TO "USER";
GRANT SELECT ANY DICTIONARY to "USER";
GRANT CREATE TYPE TO "USER";

create file import.bat and put this lines in it:

SQLPLUS SYSTEM/systempassword@ORA_alias @"crate_drop_user.SQL"
IMP SYSTEM/systempassword@ORA_alias FILE=user.DMP FROMUSER=user TOUSER=user GRANTS=Y log =user.log

Be carefull if you will import from one user to another. For example if you have user named user1 and you will import to user2 you may lost all grants , so you have to recreate it.

Good luck, Ivan

Precision

Answer 3

If you are using impdp command example from @sathyajith-bhat response:

impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;

you will need to use mandatory parameter directory and create and grant it as:

CREATE OR REPLACE DIRECTORY DMP_DIR AS 'c:\Users\USER\Downloads';
GRANT READ, WRITE ON DIRECTORY DMP_DIR TO {USER};

or use one of defined:

select * from DBA_DIRECTORIES;

My ORACLE Express 11g R2 has default named DATA_PUMP_DIR (located at {inst_dir}\app\oracle/admin/xe/dpdump/) you sill need to grant it for your user.

Declamation