Tell me more ×
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.
up vote 1 down vote favorite

I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another instance on the same host, it's trying to recreate the same directory structures. The beginning of the import log looks like this:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@NEWINSTANCE full=Y directory=bak_dir 

dumpfile=dbexp.dmp 
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
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 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
Failing sql is:
CREATE TABLESPACE "TBLSP_73_DATA" DATAFILE 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT

It appears that it's attempting to use the same paths as the source database. How can I get the datapump import to point to a new path like D:\ORACLE\ORADATA\DB2...

share|improve this question

1 Answer

up vote 2 down vote accepted

Check the Oracle Utilities documentation. When using Datapump tools, you can reset paths using the REMAP_DATAFILE parameter for import jobs.

Also, insure your OS session variables reflect the new DB. This is often over-looked when working with a new DB on a server that already has a DB working on it.

share|improve this answer
REMAP_DATAFILE works, I found that solution but it took me a while to get the quoting/delimiting syntax correct. Can you expand on the session variables? I'm also having an issue with roles not importing, seemingly due to the fact that the object already exists. – sreimer Jul 11 '12 at 17:26
Can you post the solution that works? – MarkSchoonover Sep 24 '12 at 20:44

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.