Oracle Database 12c – Data Pump Enhancements (EXPDP)-Part 2

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jun 7, 2017

Oracle Database 12c – Data Pump Enhancements (EXPDP)-Part 2

In the case of a container database (CDB), the actual data belongs to the underlying pluggable databases (PDB) and each pluggable database appears as a non-CDB to the client. Therefore, it makes more sense to take an export of the objects from a pluggable database. Click here to read Part 1.


Introduction:
In part two of this blog series, I will cover the details of taking an export of only pluggable databases and what are the Data Pump restrictions are with the pluggable databases.  

 Data_Pump_Part2.jpg

 

Image Source: https://www.google.co.in/search?q=data+pump+architecture&source=lnms&tbm=isch&sa=X&ved=0ahUKEwjS7Ofeg5_UAhUMP48KHbROAoMQ_AUICigB&biw=1366&bih=662#imgrc=Fqo_SJkHmI4paM  


Taking an Export of Pluggable Database:
In the case of a container database (CDB), the actual data belongs to the underlying pluggable databases (PDB) and each pluggable database appears as a non-CDB to the client. Therefore, it makes more sense to take an export of the objects from a pluggable database.

Using Data Pump export for pluggable database (PDB) is identical to using a Data Pump export for a Non-CDB database (normal Oracle database).

The only difference in using Data Pump export for PDB is that, we must use a CONNECT IDENTIFIER (TNS ALIAS) in the DATA PUMP EXPORT command prompt while initiating the export. This is to ensure that we are initiating the Data Pump export for a specific pluggable database. 

For instance, we can take an export of the user ABBAS’ belonging to the PDB ‘PRODPDB1’ as follows:

[oracle@labserver ~]$ expdp directory=DP_PDB1 dumpfile=pdb1_abbas.dmp logfile=pdb1_abbas.log schemas=abbas 

Export: Release 12.1.0.1.0 - Production on Fri Mar 27 00:08:09 2015 

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 

Username: sys@prodpdb1 as sysdba

Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/********@prodpdb1 AS        SYSDBA directory=DP_PDB1 dumpfile=pdb1_abbas.dmp logfile=pdb1_abbas.log schemas=abbas

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

. . exported "ABBAS"."TAB1"                              67.85 KB      41 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /backup/exp/prodpdb1/pdb1_abbas.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 14:39:44 2015 elapsed 0 00:01:08

Note that, while passing the Username; we have additionally specified the CONNECT IDENTIFIER for pluggable database ‘PRODPDB1’.

[oracle@labserver ~]$ expdp directory=DP_PDB1 dumpfile=pdb1_abbas.dmp logfile=pdb1_abbas.log schemas=abbas 

Export: Release 12.1.0.1.0 - Production on Fri Mar 27 00:08:09 2015 

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 

Username: sys@prodpdb1 as sysdba

Password: 

Here the CONNECT IDENTIFIER (prodpdb1) is resolving to the pluggable database ‘PRODPDB1’. 

[oracle@labserver ~]$ tnsping prodpdb1 

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 27-MAR-2015 00:11:54 

Copyright (c) 1997, 2013, Oracle.  All rights reserved. 

Used parameter files:

/app/oracle/db/12.1.0.1/network/admin/sqlnet.ora 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prodpdb1)))

OK (10 msec) 

Data Pump Restrictions with Pluggable Database:
One important restriction while using Data Pump for a pluggable database is that we cannot use a directory owned by the ROOT container (CDB) or owned by a different pluggable database for performing data pump export/import.

We must create a directory under the pluggable database i.e. the directory must be owned by the pluggable database to be able to use Data Pump export/import.

Trying to use a directory belonging to another pluggable database or to the ROOT container, would result into the following errors.

[oracle@labserver ~]$ expdp directory=DP_PDB2 dumpfile=pdb1_abbas.dmp logfile=pdb1_abbas.log schemas=abbas 

Export: Release 12.1.0.1.0 - Production on Fri Mar 27 00:21:08 2015 

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved. 

Username: sys@prodpdb1 as sysdba

Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name DP_PDB2 is invalid 

Further, we will not be able to use the DEFAULT directory DATA_PUMP_DIR for performing datapump export/import of a pluggable database. This is because DATA_PUMP_DIR is always owned by the ROOT container and the ownership cannot be altered.

Trying to create the default DATA_PUMP_DIR inside a pluggable database would result into following errors. 

sys@PRODPDB1> show con_name 

CON_NAME
------------------------------

PRODPDB1

sys@PRODPDB1> create or replace directory DATA_PUMP_DIR as '/backup/exp/prodpdb1';

create or replace directory DATA_PUMP_DIR as '/backup/exp/prodpdb1'

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database 

Therefore, we must always create directory explicitly for a pluggable database in order to perform a Data Pump export/import. 

Conclusion:
Starting with Oracle 10g, the Data Pump utility is an enhanced version of EXPORT and IMPORT introduced by Oracle. The new enhancements and additions in DATA PUMP (EXP) in Oracle Database 12c as discussed both in Part 1 and Part 2 can be put to use and leveraged for maximum benefit. With this I conclude the two part blog series. In later blogs, I will discuss about Data Pump features of Import in 12C. Until then, if you have any questions for me feel free to click below. You can also leave a comment and I will respond.


Ask Sanchit