AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
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
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';
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.
