Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
To import only the data, you can use the trick: content=data_only, that loads only table row data into existing tables; no database objects are created. –  Shaunyl Dec 26 '13 at 12:32
    
That loads the indexs, functions, triggers, views, etc too ? –  Jorge Vega Sánchez Dec 26 '13 at 15:40
    
Your answer works fine if the tables have been created before the dump. In my case there are no tables created. I changed your parameter to content=all –  Jorge Vega Sánchez Dec 26 '13 at 16:39

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.