认真就输

In this Document

APPLIES TO:

Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***

GOAL

This article documents a resolution for errors ORA-39000, ORA-31640 and ORA-27037 when performing DataPump export/import.

DataPump Import can fail with the following errors:

SOLUTION

The parameter DIRECTORY specifies the location to which the DataPump Export or Import  is not properly created or having permission issues.

Drop  and re-create a new oracle directory object and change the expdp/impdp command to point to the new directory to resolve this issue.  You must have the DBA Privilege to create a Directory.

For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:

Then grant read and write permissions to the users who will be performing the data pump export and import.

REFERENCES

NOTE:266875.1 – Export/Import DataPump Parameter DIRECTORY – How to Specify a Directory

In this Document

APPLIES TO:

Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
IBM AIX on POWER Systems (64-bit)
***Checked for relevance on 10-Jun-2014***

SYMPTOMS

The following errors are reported while trying to import from an NFS mount point, even if the file system is mounted properly.

CAUSE

From IBM AIX documentation, it is clear that all mount point information is supposed to be present in the ‘/etc/filesystems’ file located on the system. When mount information is not present in ‘/etc/filesystems’ file it can lead to abnormalities.

SOLUTION

The following steps helps in eliminating the above error during import:

1. Ensure the filesystem is mounted with the required mount options. I.e. rsize=32k, wsize=32k, hard, actimeo=0

2. Set the following event in the init.ora, so that disable the mount point parameter checking:

In this Document

APPLIES TO:

Oracle Server – Enterprise Edition – Version 10.2.0.4 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

GOAL

DataPump import (IMPDP) from multiple directories using the substitution variable (%U) fails with:

How to import from multiple directories using the substitution variable (%U) ?

FIX

This is an expected behavior when using multiple dump files with %U. Follow the simplified example below to understand the DataPump behavior for this scenario:

1. Run the export using test_%U.dmp for dumpfile:

2. Move the dump files test_04.dmp, test_05.dmp and test_06.dmp to directory TESTDP2 (which points to /oracle/users/testdp2):

3. After transferring some of the dump files, the directories TESTDP1 and TESTDP2 contain:

3. Run the import using the substitution variable %U for dump files, e.g:

The DataPump utility expects the dump file test_01.dmp in directory TESTDP2 directory, too (expected behavior). When %U is used, the DataPump Utility starts to search for the first dump file (in this case test_01.dmp) in all the directories specified in the DataPump command line. In this particular case, the dump files: test_01.dmp, test_02.dmp,….test_0<n>.dmp must exist in both directories TESTDP1 and TESTDP2 when running the import.

To avoid the errors ORA-31640 and ORA-27037 errors when multiple directories with the substitution variable %U are used, the following options are available:

A. In the source database, create the directories TESTDP1, TESTDP2, TESTDP3, etc and run expdp with:

Then move the generated dump files exactly as they are generated to directories TESTDP1, TESTDP2, TESTDP3, etc in the target environment.

Or:

B. Instead of running impdp with substitution variable %U and multiple directories, explicitly specify the dump files: