Pluggable Databases and “DATA_PUMP_DIR” – ORA-39087

2015-11-27 Off By Markus Flechtner

Some days ago I ran into the issue that using the directory “DATA_PUMP_DIR”, which is created in every Oracle 12c database, cannot be using when exporting data of a PDB. This is documented in the “Utilities Guide” (Chapter „Overview of DataPump“) “The default Data Pump directory object, DATA_PUMP_DIR, does not work with PDBs. You must define an explicit directory object within the PDB that you are exporting or importing.”. If you try it, the result is an “ORA-39087: directory name DATA_PUMP_DIR is invalid”. As it is documented, it is a not a bug, but a feature 🙂 – but what’s going on there.

Let’s check the existing directories in our Multitenant Database:

SQL> connect / as sysdba
Connected.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 CRM01 READ WRITE NO
SQL> set linesize 200
SQL> set pagesize 100
SQL> column owner format a10
SQL> column directory_name format a20
SQL> column directory_path format a60
SQL> select con_id,origin_con_id,directory_name,directory_path
2 from cdb_directories
3 where con_id in (1,3) and directory_name like ‘DATA%’
4 order by con_id,origin_con_id,directory_name;

CON_ID ORIGIN_CON_ID DIRECTORY_NAME DIRECTORY_PATH
———- ————- ——————– ————————————-
1 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/
3 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/

Please note that there is a directory DATA_PUMP_DIR in Container #3 (=PDB “CRM01”) but the originating Container is 1 (= CDB$ROOT).

Let’s check the directories in our PDB:

SQL> alter session set container=crm01;
Session altered.

SQL> select origin_con_id,directory_name,directory_path
2 from dba_directories
3 where directory_name like ‘DATA%’;

ORIGIN_CON_ID DIRECTORY_NAME DIRECTORY_PATH
————- ——————– ———————————————-
1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/

Running a DataPump export of the CDB$ROOT works fine:

oracle@training:~/ [TVD12CDB] expdp system directory=DATA_PUMP_DIR dumpfile=root.dmp logfile=root.log full=yes
Export: Release 12.1.0.2.0 - Production on Fri Nov 27 23:19:33 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=DATA_PUMP_DIR dumpfile=root.dmp logfile=root.log full=yes
Estimate in progress using BLOCKS method...
[..]
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u00/app/oracle/admin/TVD12CDB/dpdump/root.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Fri Nov 27 23:21:54 2015 elapsed 0 00:02:13

We’ll ignore the message that a DataPump export of CDB$ROOT is not useful.

What happens when running the same export on PDB level:

oracle@training:~/ [TVD12CDB] expdp system@crm01 directory=DATA_PUMP_DIR dumpfile=crm01.dmp logfile=crm01.log full=yes
Export: Release 12.1.0.2.0 - Production on Fri Nov 27 23:23:54 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

OK, I had mentioned the ORA-39087 before.

Let’s try to recreate the directory DATA_PUMP_DIR in the PDB as a workaround:
SQL> alter session set container=crm01;
Session altered.

SQL> create or replace directory data_pump_dir as ‘/tmp’;
create or replace directory data_pump_dir as ‘/tmp’
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

So, this is not a workaround. We’ll create a new directory DATA_PUMP_DIR_PDB on PDB level:

SQL> alter session set container=crm01;
SQL> create or replace directory data_pump_dir_pdb as '/u00/app/oracle/admin/TVD12CDB/dpdump/';
Directory created.

And DataPump works fine with this directory.

Let’s create the same directory in CDB$ROOT:

SQL> connect / as sysdba
Connected.
SQL> create or replace directory data_pump_dir_pdb as '/u00/app/oracle/admin/TVD12CDB/dpdump/';
Directory created.

Let’s check the directories in our database:

SQL> set linesize 200
SQL> set pagesize 100
SQL> column owner format a10
SQL> column directory_name format a20
SQL> column directory_path format a60
SQL> select con_id,origin_con_id,directory_name,directory_path
2 from cdb_directories
3 where con_id in (1,3) and directory_name like 'DATA%'
4 order by con_id,origin_con_id,directory_name;
CON_ID ORIGIN_CON_ID DIRECTORY_NAME DIRECTORY_PATH
---------- ------------- -------------------- -------------------------------------
1 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/
1 1 DATA_PUMP_DIR_PDB /u00/app/oracle/admin/TVD12CDB/dpdump/
3 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/
3 3 DATA_PUMP_DIR_PDB /u00/app/oracle/admin/TVD12CDB/dpdump/

And – again – export with DATA_PUMP_DIR_PDB works fine on both CDB$ROOT and PDB.

Did you notice that CON_ID and ORIGIN_CON_ID are equal for both rows for DATA_PUMP_DIR_PDB. These directories seem to be “local” in the container.

Let’s create another directory in CDB$ROOT:

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> create or replace directory data_pump_dir_tst as ‘/u00/app/oracle/admin/TVD12CDB/dpdump/’;
Directory created.

SQL> select con_id,origin_con_id,directory_name,directory_path
2 from cdb_directories
3 where con_id in (1,3) and directory_name like ‘DATA%’
4 order by con_id,origin_con_id,directory_name;

CON_ID ORIGIN_CON_ID DIRECTORY_NAME DIRECTORY_PATH
———- ————- ——————– ——————————————————
1 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/
1 1 DATA_PUMP_DIR_PDB /u00/app/oracle/admin/TVD12CDB/dpdump/
1 1 DATA_PUMP_DIR_TST /u00/app/oracle/admin/TVD12CDB/dpdump/
3 1 DATA_PUMP_DIR /u00/app/oracle/admin/TVD12CDB/dpdump/
3 1 DATA_PUMP_DIR_TST /u00/app/oracle/admin/TVD12CDB/dpdump/
3 3 DATA_PUMP_DIR_PDB /u00/app/oracle/admin/TVD12CDB/dpdump/

By default, the data dictionary of the CDB is protected. E.g.you cannot open PDB$SEED read/write or create a local user in CDB$ROOT.
Setting “_oracle_script” to TRUE disables this protection. And when creating a directory in this situation, this is a “common” directory,
which appears on PDB level, but the ORIGIN_CON_ID is 1.
And it’s the same as with DATA_PUMP_DIR, running DataPump with the new directory DATA_PUMP_DIR_TST results in “ORA-39087: directory name DATA_PUMP_DIR_TST is invalid”.

So these directories appear in the DBA_DIRECTORIES view in the PDB, but you cannot use them. From my point of view it would have been better to restrict DBA_DIRECTORIES on PDB level to the directories with CON_ID=ORIGIN_CON_ID.

If you want to use the well known directory DATA_PUMP_DIR on PDB level, you can:

  1. Drop the directory DATA_PUMP_DIR in CDB$ROOT
  2. Re-Create the directory DATA_PUMP_DIR in CDB$ROOT (with “_oracle_script”=FALSE
  3. Create the directory DATA_PUMP_DIR in all the PDBs in which you want to use this directory.

But I’m pretty sure that this work around is not supported.