Skip to content
Home » Oracle » How to Resolve ORA-39087: directory name is invalid

How to Resolve ORA-39087: directory name is invalid

ORA-39087

Saw errors ORA-39087 and ORA-39070, when tried to export data AS SYSDBA through data pump expdp.

[oracle@test ~]$ expdp \"/ as sysdba\" full=y directory=BACKUP_DUMPS dumpfile=expdp.dmp logfile=expdp.log
...
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name BACKUP_DUMPS is invalid

ORA-39087 means that the directory object you specified in the command cannot be found in directory list of the database.

Solution

We have to make sure that the directory object does exist in the database before performing a data pump operation.

To check the existence of the directory object, we can list all directory objects by querying DBA_DIRECTORIES.

SQL> select directory_name from dba_directories order by 1;

The query lists all valid directory names that we can use. So the solution is to correct the directory name to a valid one or choose one of which to specify the destination for data pump.

If you still got the same error, you should make sure that you went for the right destination. Let's move on.

ORACLE_SID

In this case, we found that the directory name is valid, so we additionally check its directory path.

SQL> select directory_path from dba_directories where directory_name = 'BACKUP_DUMPS';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/backup/dumps

Everything seems alright, so I guess we might go for the wrong database. We should check ORACLE_SID for sure.

[oracle@test ~]$ echo $ORACLE_SID
ORCLCDB

This ORACLE_SID is the instance name of our Container Database (CDB). How about Pluggable Database (PDB)?

ORACLE_PDB_SID

Just like we export data from a PDB by using AS SYSDBA without password, we have to take care one more environment variable, ORACLE_PDB_SID. Otherwise, it goes to the root container, also known as container database (CDB$ROOT).

So the solution is to set ORACLE_PDB_SID correctly.

[oracle@test ~]$ export ORACLE_PDB_SID=ORCLPDB
[oracle@test ~]$ echo $ORACLE_PDB_SID
ORCLPDB

Now we can export data again.

Leave a Reply

Your email address will not be published. Required fields are marked *