Migrating Oracle Database with expdp and impdp

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'

1. Does this err开发者_运维技巧or tell me that newschema is not defined?
2. Do I have to create a new user?
3. 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!

"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

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

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.

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;