Expert: Peter Choi - 9/30/2009

Question
QUESTION: I am using window server 2003 sp 1 and oracle 10g. When I am trying to import database using IMPDP utility. Can you explain using simple steps to use impdp and expdp utilites.

ANSWER: Hi Hari,

As you know, starting with Oracle 10g and higher, Oracle is recommending the use of the DataPump utility to replace the Export/Import utilities. It should be noted that once you create a .dmp file using datapump, it is NOT backwards compatible with Export/Import.

From this point onwards, we will refer to the DataPump Export (EXPDP) and DataPump Import (IMPDP) tools.

To get some help on the syntax,  on the command line type

> expdp -?
or
> impdp -?


The most basic command could look like this:

> expdp <user>/<password>@<database_instance> parm=[parm1] parm2=[parm2] ...

example1: expdp hr/hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=y NOLOGFILE=y
example2: expdp hr/hr@inst1 DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

example3: impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.log
example4: mpdp hr/hr@inst1 DIRECTORY=dpump_dir DUMPFILE=hr.dmp TABLES=employees


Enclosed are the references which you will find useful.

Reference 1: http://www.oracle.com/technology/obe/obe10gdb/storage/datapump/datapump.htm
Reference 2: http://www.oracle-base.com/articles/10g/OracleDataPump10g.php
Reference 3: http://www.orafaq.com/wiki/Datapump
Reference 4: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm

Hope this helps.

Peter

---------- FOLLOW-UP ----------

QUESTION: Using help provided by you it is possible to export database using EXPDP but when I am trying to import using IMPDP it is giving following errors :
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid  
Dump file that is using by me is prepared by using EXP utility and is placed at D:\backup\mydump.dmp

Please help me to IMPORT this using IMPDP utility.


Answer
Hi Hari,

If you took a look at Reference 3, you will see that your error message is the same for IMPDP (the example shown is for EXPDP). What is missing is the need to create the directory path as a database object (this is new) instead of just passing the path in the command line. Following the Create Database Directories from Reference 3:

-- Begin Snippet --

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.

PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/app/oracle/product/10.2.0/rdbms/log/

-- End Snippet --

If you do not specify the DUMPFILE parameter, it will use the default. If you override the default by specifying the DUMPFILE parameter, you will need to create it first then invoke it in the command line.

Hope this helps.

Peter