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

Question

How do I create a database from a dmp file? I do not have an existing database with the same structure in my system, so it should be filled with tasks, events, tables, etc.

I placed the dmp and the log file in E: drive

I tried the import utility

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

But I get an error like

 invalid argument value bad dump file specification unable to open dump file "E:\app\Vensi\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 a dmp file (taken from a Linux server) is displayed as a crash dump file

I do not understand how I can solve this problem. Please help me solve this problem.

I am a complete newbie to Oracle ...

Answer 1

How was the database exported?

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

  • Create a user:

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

  • Grant Rights:

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

  • Run the import using imp :

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

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

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

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

Either move the export file to the path above, or create a directory object that points to the path where the dmp file is located and pass the name of the object to the impdp above.

Answer 2

All this code is placed in a * .bat file and runs immediately:

My code to create a 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 the import.bat file and put the following lines into 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 careful if you import from one user to another. For example, if you have a user named user1 and you import it into user2, you may lose all grants, so you will have to recreate it.

Good luck, Ivan