Complete Guide to Oracle Database Import from DMP Files: Resolving Common Errors and Best Practices

Problem Background and Error Analysis

During Oracle database migration, complete import from DMP files is a common requirement. Users attempting to use the impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp command encountered typical errors:

invalid argument value
bad dump file specification
unable to open dump file "E:\app\admin\oratest\dpdump\WB_PROD_FULL_20MAY11.dmp" for read
unable to open file
unable to open file
(OS 2) The system cannot find the file specified.

The core cause of this error is that Oracle Data Pump Import searches for DMP files in specific default directories. When files are located in non-default locations, directory objects must be explicitly specified.

Solutions: Two Import Methods Based on Export Tools

Method 1: Handling Traditional exp Tool Exports

If the database was exported using the traditional exp tool, follow these steps:

1. Create Target User: First create the corresponding database user

create user <username> identified by <password> default tablespace <tablespacename> quota unlimited on <tablespacename>;

grant connect, create session, imp_full_database to <username>;

imp <username>/<password>@<hostname> file=<filename>.dmp log=<filename>.log full=y;

Method 2: Handling Data Pump expdp Tool Exports

If the database was exported using the modern expdp tool, use the corresponding impdp tool:

impdp <username>/<password> directory=<directoryname> dumpfile=<filename>.dmp logfile=<filename>.log full=y;

Core Problem Analysis: Directory Object Configuration

From the error message, it's clear that Oracle attempts to find DMP files in the default directory E:\app\Vensi\admin\oratest\dpdump\. There are two solutions to this problem:

Solution A: Move Files to Default Directory

Move the DMP file to Oracle Data Pump's default directory - this is the simplest solution.

Solution B: Create Directory Object

Create a directory object pointing to the actual location of the DMP file:

CREATE DIRECTORY dpump_dir AS 'E:/';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO system;

Then specify this directory in the import command:

impdp system/tiger@oratest DIRECTORY=dpump_dir DUMPFILE=WB_PROD_FULL_20MAY11.dmp FULL=y

In-depth Analysis of Oracle Data Pump Import

Import Modes Detailed Explanation

Oracle Data Pump Import supports multiple import modes, each suitable for different scenarios:

• Full Mode (FULL): Imports entire database content, default for file imports
• Schema Mode (SCHEMA): Imports all objects of specified schemas, default for network imports
• Table Mode (TABLE): Imports specified tables and their dependent objects
• Tablespace Mode (TABLESPACE): Imports all objects within specified tablespaces
• Transportable Tablespace Mode (TRANSPORT_TABLESPACES): Imports tablespace metadata

Key Parameter Analysis

DIRECTORY Parameter: Specifies the default location where the import job finds the dump file set and creates log files. Defaults to the DATA_PUMP_DIR directory object.

DUMPFILE Parameter: Specifies the names of the dump file set, which can include directory objects and substitution variables:

DUMPFILE=dpump_dir1:exp1.dmp, exp2%U.dmp

FULL Parameter: Specifies full database import, default mode for file imports but must be explicitly specified for network imports.

Filtering Mechanisms

Oracle Data Pump Import provides powerful filtering capabilities to limit information types during import:

• Data Filtering: Restrict imported table rows through QUERY and SAMPLE parameters
• Metadata Filtering: Include or exclude specific objects through EXCLUDE and INCLUDE parameters

Best Practices and Important Considerations

Privilege Management

Executing full imports requires appropriate privileges:

• For file imports: Requires DATAPUMP_IMP_FULL_DATABASE role
• For network imports: Requires DATAPUMP_IMP_FULL_DATABASE role (target database) and DATAPUMP_EXP_FULL_DATABASE role (source database)

Error Handling

Common import errors and solutions:

• File Not Found Error: Check DIRECTORY parameter configuration and file paths
• Insufficient Privileges: Ensure users have necessary directory read/write privileges and database roles
• Character Set Mismatch: Ensure source and target databases use compatible character sets

Performance Optimization

For large database imports, employ the following optimization strategies:

impdp system/password DIRECTORY=dpump_dir DUMPFILE=full.dmp PARALLEL=4 \
TRANSFORM=SEGMENT_ATTRIBUTES:N TABLE_EXISTS_ACTION=REPLACE

Using the PARALLEL parameter can significantly improve import performance, especially in multi-CPU environments.

Conclusion

Successful Oracle database import from DMP files requires accurate understanding of export tool types, proper directory object configuration, and appropriate privilege granting. By analyzing specific error information and adopting corresponding solutions, database migration tasks can be efficiently completed. The rich parameters and filtering mechanisms provided by Oracle Data Pump Import make the database import process more flexible and controllable.