ORA-31640
Tried to import data into the database from a dump file, but it failed with ORA-31640.
Linux
[oracle@test ~]$ impdp system/password@orcl full=yes directory=BACKUP2 dumpfile=full.dmp logfile=full.log
...
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/backup2/dumps/full.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Another error pattern on Linux would be:
[oracle@test ~]$ impdp system/password@orcl full=yes directory=BACKUP2 dumpfile=full.dmp logfile=full.log
...
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/backup2/dumps/full.dmp" for read
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
AIX
For AIX OS, we saw the same error.
$ impdp system/password@orcl full=yes directory=BACKUP2 dumpfile=full.dmp logfile=full.log
...
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/backup2/dumps/full.dmp" for read
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
No matter what platform you run command, ORA-31640 means that user oracle cannot see the file in the directory you specified in the command. It could be missing from the directory or has a permission problem.
From another angle, the errors also reveal something:
- The database is open.
- The user's credential is correct.
- The directory object is valid.
Solutions
There're several possibilities for such error.
No such file or directory
It means that the dump file does not exist in the directory you specified in the command. Let's check the file's existence.
[oracle@test ~]$ ll /backup2/dumps/full.dmp
ls: cannot access '/backup2/dumps/full.dmp': No such file or directory
Although the directory object is valid, you may use the wrong directory object to do it. Let's find it.
[root@test ~]# find /backup*/dumps -name "full.dmp" -type f
/backup3/dumps/full.dmp
OK, it's at another directory.
So the solution is simple, move the file back to the directory that you want to use, or change the directory object in the command.
Permission denied
It means that the dump file exists in the directory you specified in the command, but user oracle cannot use it, most likely, it's because of ownership.
[oracle@test ~]$ ll /backup2/dumps/full.dmp
-rw-r----- 1 root root 53907456 Jan 26 01:46 /backup2/dumps/full.dmp
To correct the permission problem, you may provide a full permission (777) on the file by the owner.
[root@test ~]# chmod 777 /backup2/dumps/full.dmp
Or change the ownership back to oracle by the owner.
[root@test ~]# chown oracle:oinstall /backup2/dumps/full.dmp
RAC Database
For data pump operations in a RAC database, you may ask yourself.
- Is the directory a shared storage for all nodes?
- Can this dump file be seen on all nodes?
If the directory is not a shared folder, you may not see the dump file on some nodes.
To overcome this, please connect to the node which is able to see the dump file, then add CLUSTER=NO to the command. For example
[oracle@test ~]$ impdp system/password@orcl_1 full=yes directory=BACKUP2 cluster=no dumpfile=full.dmp logfile=full.log