Migrating Oracle Database with expdp and impdp

Question

Does the target database need to be prepared with tablespace and/or user (schema) in order to successfully migrate a schema from one database (11g R1) to another (11g R2)?

Currently I only can make tests with the 11g R1 database. My test was to export a schema and import it into a new schema within the same database.

expdp system/systempass schemas=oldschema

After that I tried to import it with:

impdp system/systempass expdat.dmp remap_schema=oldschema:newschema

But this leads to the error:

UDI-00014: invalid value for parameter, 'attach'

Does this error tell me that newschema is not defined?
Do I have to create a new user?
Do I have to create a tablespace for such a new user?

The questions also relate to a new installed 11g R2 database on a new PC. Any suggestions migrating a database to a fresh installed database are welcome!

cagcowboy · Accepted Answer · 2011-09-23T11:56:23.877

5

"Does the target database need to be prepared with tablespace and/or user (schema) in order to successfully migrate a schema from one database (11g R1) to another (11g R2)?"

Tablespaces - yes. Users - no.

UDI-00014: invalid value for parameter, 'attach'

I think the order of parameters is incorrect when calling impdp. Should be of the form:

impdp username/password DUMPFILE=<filename> [and then whatever other parameters you need for your import]

Since you're doing a "migration" I assume it's a full imp/exp, in which case you probably want:

impdp username/password DUMPFILE=<filename> FULL=Y

With regard to your current test within the same db, I suggest you check-out:

http://psoug.org/reference/datapump.html

for a couple of examples

edited Sep 23 '11 at 11:56

answered Sep 23 '11 at 11:51

cagcowboy

30,012
11
69
93

Thank you, I didn't saw the wrong parameter I was convinced that something other was the reason. – Christian Ammer Sep 23 '11 at 19:51
Just in case: I had this problem because I was writing the dumpfile name but lost the "dumpfile=" somewhere in the way. – Alfabravo Jun 28 '16 at 20:01

score 1 · Answer 2 · answered Jan 30 '18 at 16:57

I asume that a lot of people came here for: 'UDI-00014: invalid value for parameter' error like me. For those my situation was the following.

I had a different scenario and for me it was complaining on 'remap_schema' parameter. It appeared that I need to add double quotes to values like the following:

Before (failed)

impdp user/password directory=dump_dir dumpfile=myfile.dmp remap_schema=my_schema:my_schema remap_tablespace=my_schema:my_schema logfile=mylog.log

After (worked)

impdp user/password directory=dump_dir dumpfile=myfile.dmp remap_schema="my_schema":"my_schema" remap_tablespace="my_schema":"my_schema" logfile=mylog.log

score 0 · Answer 3 · answered Sep 29 '14 at 15:38

0

If you are not using the ATTACH parameter during the import then open a new command line window and run the import and it should now work. For me the environment variables are the same, but some how this is now working.

answered Sep 29 '14 at 15:38

Greg Parrish

1

score 0 · Answer 4 · edited Dec 28 '22 at 12:17

I had the same error when using the transform parameter(UDI-00014: invalid value for parameter, 'transform'), I moved the logfile bit to be the last part of the command. In other words, check the order.

Before:(failed)

impdp MY_CLONE/password schemas=ORIGINAL directory= db_import_dmp  dumpfile=ORIGINAL.dmp logfile= CLONE.log remap_schema=ORIGINAL:MY_CLONE transform=oid:n;

After:(worked)

impdp MY_CLONE/password schemas=ORIGINAL directory= db_import_dmp  dumpfile=ORIGINAL.dmp remap_schema=ORIGINAL:MY_CLONE transform=oid:n logfile= CLONE.log;

Migrating Oracle Database with expdp and impdp

4 Answers4