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   gt 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  quot E  app admin oratest dpdump WB PROD F ULL 20MAY11 dmp quot  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

User · Accepted Answer

How was the database exported    If it was exported using exp and a full schema was exported  then   Create the user   create user  lt username gt  identified by  lt password gt  default tablespace  lt tablespacename gt  quota unlimited on  lt tablespacename gt    Grant the rights   grant connect  create session  imp full database to  lt username gt    Start the import with imp   imp  lt username gt   lt password gt   lt hostname gt  file  lt filename gt  dmp log  lt filename gt  log full y    If it was exported using expdp  then start the import with impdp   impdp  lt username gt   lt password gt  directory  lt directoryname gt  dumpfile  lt filename gt  dmp logfile  lt filename gt  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

User · Answer

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

User · Answer

If you are using impdp command example from  sathyajith-bhat response   impdp  lt username gt   lt password gt  directory  lt directoryname gt  dumpfile  lt filename gt  dmp logfile  lt filename gt  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

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

Examples related to oracle

Examples related to oracle11g

Examples related to oracle-dump