1

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...

2

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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