Skip to Main Content
  • Questions

  • impdp full database from solaris to windows database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Cindy.

Asked: February 21, 2017 - 8:13 pm UTC

Last updated: February 28, 2017 - 5:05 am UTC

Version: 11.2.0.1

Viewed 1000+ times

You Asked

Hi, I am new to Oracle DB. I am trying to import an exported database from a Solaris oracle 11.2.0.1 to a windows 2008 oracle 11.2.0.1. I installed Oracle 11.2.0 in a windows 2008 OS with a sample database name orcl. Below are the steps I follow to import the database:
1. Create directory dir as 'c:\datafile';
2. Grant read, write on directory dir to system;
3. Grant datapump_imp_full_database, datapump_exp_full_database to system;
4. go to orcle_home/RDBMS/ADMIN and run @catexp.sql
5. log into SQLPLUS as system
6. I issued this command to import the database: impdp system/xxxxx dumpfile=dir:full_exp%u.dmp logfile=dir:clog3.log full=y;
The import error is that it can't create tablespaces. See error below. I see where the error is: it's trying to create tablespace in '/data01/oradata...' It looks like I need to change the path to 'c:/data01/oradata/....'. My question is how do I change the path within the dump file or is there a better way of doing that? The only way I can think about doing that is to create the table space separately but than I would have to pull the tablespace name out from the log file. Any help is appreciate, Thank you!!



Restarting "SYSTEM"."IMPORT000021":
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/data01/oradata/YMC/ymcpdata/ctxd01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The sy
stem cannot find the path specified.
Failing sql is:
CREATE TABLESPACE "CTXD" DATAFILE '/data01/oradata/YMC/ymcpdata/ctxd01.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 10485760 MAXSIZE 1939865600 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL U
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/data01/oradata/YMC/ymcpdata/owad01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The sy
stem cannot find the path specified.
Failing sql is:

and Connor said...

You can either use REMAP_DATAFILE to have the import create them, for example:

REMAP_DATAFILE='/data01/oradata/YMC/ymcpdata/ctxd01.dbf':'c:\oracle\YMC\ymcpdata\ctxd01.dbf'


and have one of those listed for each datafile you intend to create.

Or you can do an import with SQLFILE=xxx, which will write all the DDL commands out to a sql file, and precreate the tablespaces as you prefer.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cindy Chu, February 24, 2017 - 12:19 am UTC

Thanks for your response Connor. Since I just have the dump files to work with, is there a way to get a listing of all the tablespace I need to remap?
Connor McDonald
February 25, 2017 - 1:01 am UTC

"Or you can do an import with SQLFILE=xxx, which will write all the DDL commands out to a sql file"

Once you have the SQL file, you can easily extract the list of all the datafiles you would need to remap, or get a list of the tablespaces you will need to precreate.

Thank you. one final question

Cindy Chu, February 28, 2017 - 2:56 am UTC

I tried to put all the remap_datafile parameters into 1 parfile and feed this in through the impdp command (impdp system/xxx parfile='c:\perfile.par') and got the following error: "Multiple values not allowed for parameter 'parfile'" There are about 100 remap_datafile lines. Is there a better way of doing this? below is sample from the parfile: Thank you.

CONTENT=ALL
DIRECTORY=dir
DUMPFILE=full_exp%u.dmp
FULL=y
LOGFILE=clog1.log
REMAP_DATAFILE="'/data01/oradata/YMC/ymcpdata/ctxd01.dbf':'C:\APP\ORADATA\ORCL\ctxd01.dbf'"
REMAP_DATAFILE="'/data01/oradata/YMC/ymcpdata/ctxd01.dbf' :'C:\APP\ORADATA\ORCL\ctxd01.dbf'"
REMAP_DATAFILE="'/data01/oradata/YMC/ymcpdata/owad01.dbf':'C:\APP\ORADATA\ORCL\owad01.dbf'"
REMAP_DATAFILE="'/temp01/oradata/YMC/ymcpdata/temp01.dbf':'C:\APP\ORADATA\ORCL\temp01.dbf'"
REMAP_DATAFILE="'/temp01/oradata/YMC/ymcpdata/temp04.dbf':'C:\APP\ORADATA\ORCL\temp04.dbf'"
REMAP_DATAFILE="'/temp01/oradata/YMC/ymcpdata/temp03.dbf':'C:\APP\ORADATA\ORCL\temp03.dbf'"
REMAP_DATAFILE="'/temp01/oradata/YMC/ymcpdata/temp02.dbf':'C:\APP\ORADATA\ORCL\temp02.dbf'"
REMAP_DATAFILE="'/data01/oradata/YMC/ymcpdata/funx01.dbf':'C:\APP\ORADATA\ORCL\funx01.dbf'"
Connor McDonald
February 28, 2017 - 5:05 am UTC

Multiple remap datafile commands are supported. I think you have too many quotes. Try this:

REMAP_DATAFILE='/data01/oradata/YMC/ymcpdata/ctxd01.dbf':'C:\APP\ORADATA\ORCL\ctxd01.dbf'
REMAP_DATAFILE='/data01/oradata/YMC/ymcpdata/ctxd01.dbf' :'C:\APP\ORADATA\ORCL\ctxd01.dbf'
REMAP_DATAFILE='/data01/oradata/YMC/ymcpdata/owad01.dbf':'C:\APP\ORADATA\ORCL\owad01.dbf'
REMAP_DATAFILE='/temp01/oradata/YMC/ymcpdata/temp01.dbf':'C:\APP\ORADATA\ORCL\temp01.dbf'

and perhaps full specify your dumpfile name.

Thank you

Cindy Chu, March 01, 2017 - 1:07 am UTC

Thank you Connor. Taking out the double quotes and adding the full dumpfile names did not work either. I'm going to use the results of the sqlfile parameter to pre-create the datafiles and tablespaces then run impdp. I'll get errors on the create tablespace and datafile statements but I'm hoping the rest will run fine.

Thank you for your help!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.