Impdp expects wrong file path

datapumporacleoracle-xe

Summary:

I'm using expdp / impdp to export a database from a test / demo database (Oracle 11.2) and import it into an instance of Oracle Express (11g) on my own machine.

The import fails because it tries to create imported database files in locations from the source server. Literally, my source temo / test database is on a machine with an E:\ drive. But my computer only has one C:\ drive. And I get errors because it can't find things on the E:\ drive.

This error feels like a red herring. Either I've missused my tools, or I'm getting a slightly bogus error message.

Questions:

  • Did I run expdp wrong, and include full file paths somehow
  • Is my real problem that the SIDs don't line up ('demo' vs 'XE')
  • If I need to remap something, what specifically woudl that be?

Many thanks.

Full explanition

I run this command on the demo server:

expdp name/password@localhost:1521/demo full=y directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=exportDemo.log

I've coppied the file to my notebook, to the appropriate folder within Oracle (C:\oraclexe\app\oracle\admin\XE\dpdump). I then run this command:

impdp SYSTEM/password@localhost:1521/XE  directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y

The command runs for a while, but with errors:

;;; 
Import: Release 11.2.0.2.0 - Production on Sat Jun 11 10:32:21 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    ;;; 
    Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/********@localhost:1521/XE directory=DATA_PUMP_DIR dumpfile=demo.dmp logfile=impdpDemo.log full=y 
    Processing object type DATABASE_EXPORT/TABLESPACE
    ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
    ORA-31684: Object type TABLESPACE:"TEMP" already exists
    ORA-31684: Object type TABLESPACE:"USERS" already exists
    ORA-39083: Object type TABLESPACE failed to create with error:
    ORA-01119: error in creating database file 'E:\ORACLE\PRODUCT\11.2.0\ORADATA\DEMO\MOBILE01.DBF'
    ORA-27040: file create error, unable to create file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.

This is the start of the log, but variations of this error happen throughout the import process, and it ultimately fails.

Best Answer

  1. path to data files is included in full dump.
  2. No, SIDs don't have to match
  3. You may want to add REMAP_DATAFILE option (https://docs.oracle.com/database/121/SUTIL/GUID-7C9DDBED-91AC-4FA2-AB91-1FF214DA89B0.htm#SUTIL926 ) to impdp . Alternatively, you can just pre-create all tablespaces on target database.
Related Question