Pluggable Databases and “DATA_PUMP_DIR” – ORA-39087

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.

This entry was posted in Common, Multitenant. Bookmark the permalink.