Importing Oracle database from dump where source db filesystem has changed

Question

I'm migrating a database from one server to other. In the migration process we changed the filesystem structure from :

/appli2/oradata05/HISTO/
/appli2/oradata06/HISTO/
/appli2/oraredolog01/HISTO/
/appli2/oraarch/HISTO/

to

/appli2/oracle/HISTO/oradata05
/appli2/oracle/HISTO/oradata06/HISTO/
/appli2/oracle/HISTO/oraredolog01/HISTO/
/appli2oracle/HISTO/oraarch/HISTO/

And i'm getting error according to this changes:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/******** DIRECTORY=MIG11G DUMPFILE=20131125_full_HISTO.expdp LOGFILE=impdp_dump_HISTO_r7.log parallel=4 
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDO" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"TOOLS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "TAB_ANC" DATAFILE '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf' SIZE 1048576000,'/appli2/oradata05/HISTO/HISTO_tab_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM 
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "IDX_ANC" DATAFILE '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf' SIZE 1048576000,'/appli2/oradata06/HISTO/HISTO_idx_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM 

Is it possible to import only de data in the current filesystem structure ? I saw the remap command but i never used it, someone could put an example to use it if it was the answer to the problem.

remap_tablespace:source_tablespace_1:user,source_tablespace_2:user

Answer 1

To respond to your comment, all is the default value of content keyword, so you can ignore this one.

Anyway, I have done some tests. I don't know how is structured your old database, but from your question I note that you want to import only two tablespaces (TAB_ANC, IDX_ANC) and with all object/schemas within them. If you want to do this, you can perform this kind of import:

impdp system/passwd@HISTO directory=MIG11G dumpfile=20131125_full_HISTO.expdp  parfile=imp_histo.par

The parameter file is this one:

include=schema:"in ('SCHEMA1', 'SCHEMA2', ... , 'SCHEMAn')"
include=tablespace:"in ('TAB_ANC', 'IDX_ANC')"
remap_datafile=/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf:/appli2/oracle/HISTO/oradata05/HISTO_tab_anc_01.dbf
remap_datafile=/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf:/appli2/oracle/HISTO/oradata06/HISTO/HISTO_idx_anc_01.dbf

As you can see, you need to include all schemas contained in those tablespaces (or you can also create them from scratch in the new database). To verify these schemas you can run a query like the following:

SELECT username, default_tablespace FROM dba_users;

Remapping datafiles is useful when you move databases between platforms that have different file naming conventions.

I think you can use remap_tablespace when you have already created (manually) all the required tablespace in the target database, but with different names.