LATEST TOPICS

Oracle Database 12c (12.1): Datapump Export and Pluggable databases

As a DBA, we all are familiar with the Datapump (EXPDP/IMPDP) utility for exporting and importing database objects in and across databases. However, with the introduction of Oracle Database 12c and particularly with the introduction of Multi-tenant architecture, there are certain changes in the methods that we use in datapump to export and import data.

In this article, I would be primarily focusing on the datapump export in the context of a container and pluggable database.


1. Taking Full export
2. Taking export of container database
3. Taking export of pluggable database
4. Datapump restrictions with pluggable database

Taking a Full database export

I had the opinion that, if I connect to the ROOT container and take a FULL export of the database; it would include all the PDBs belonging to the ROOT container. However, this is not the case. The FULL export from the ROOT container doesn’t export the PDB at all, instead it just exports the objects belonging to the ROOT container.

For instance, I have a container database ‘PRODCDB‘ with four pluggable databases.

sys@PRODCDB> select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PRODPDB1                       READ WRITE          3
PRODPDB2                       READ WRITE          4
PRODPDB3                       READ WRITE          5
PRODPDB4                       READ WRITE          6

When I try to take a FULL export, it just takes export of the objects belonging to the ROOT container rather than objects from all pluggable databases.

[oracle@labserver ~]$ expdp directory=DPUMP dumpfile=exp_cdb_full_%U.dmp logfile=exp_cdb_full.log full=Y parallel=4

Export: Release 12.1.0.1.0 - Production on Thu Mar 26 23:10:41 2015

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

Username: / as sysdba

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

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=DPUMP dumpfile=exp_cdb_full_%U.dmp logfile=exp_cdb_full.log full=Y parallel=4
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 90.70 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.734 KB       8 rows
Processing object type DATABASE_EXPORT/ROLE
. . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.515 KB       2 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS"               6.937 KB       2 rows
Processing object type DATABASE_EXPORT/RADM_FPTM
. . exported "LBACSYS"."OLS$PROPS"                       6.210 KB       5 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.507 KB      14 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.929 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.898 KB       1 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.304 KB       1 rows
. . exported "SYSTEM"."REDO_DB"                          23.42 KB       1 rows
. . exported "WMSYS"."WM$ENV_VARS$"                      6.054 KB       5 rows
. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.789 KB      12 rows
. . exported "WMSYS"."WM$HINT_TABLE$"                    9.429 KB      75 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.351 KB       1 rows
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.960 KB       1 rows
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.08 KB       1 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          6.539 KB       8 rows
. . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
. . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
. . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
. . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
. . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
. . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
. . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
. . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
. . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.054 KB      36 rows
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.640 KB       4 rows
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
. . exported "ORDDATA"."ORDDCM_DOCS"                     252.9 KB       9 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           10.18 KB      22 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.929 KB       2 rows
. . exported "SYS"."NACL$_ACE_EXP"                       9.906 KB       1 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.890 KB       1 rows
. . exported "WMSYS"."WM$EXP_MAP"                        7.695 KB       3 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
. . exported "C##ABBAS"."T"                              72.67 MB  667728 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /backup/exp/prodcdb/exp_cdb_full_01.dmp
  /backup/exp/prodcdb/exp_cdb_full_02.dmp
  /backup/exp/prodcdb/exp_cdb_full_03.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Mar 26 13:49:26 2015 elapsed 0 00:08:34

As, we can see only the objects belonging to the ROOT container were exported with the FULL option. Hence, there is no way we can take export of all the pluggable databases and the ROOT container together.

Taking export of container database

Taking export of the container or ROOT database (CDB) is typically not required as container is primarily hosting other pluggable databases. and in the worst scenario, we can create a new container database and plug the existing pluggable databases. However, we may be interested in taking export of the common users/roles.

For instance, I have the following common user in my container database (CDB)

sys@PRODCDB> select USERNAME,COMMON from dba_users where common='YES' and oracle_maintained='N';

USERNAME        COM
--------------- ---
C##ABBAS        YES

We can take an export of the common user/role belonging to a CDB in the following way by initiating datapump export for the CDB.

[oracle@labserver ~]$ expdp directory=DATA_PUMP_DIR dumpfile=common_usr_cdb.dmp logfile=common_usr_cdb.log schemas=C##ABBAS

Export: Release 12.1.0.1.0 - Production on Thu Mar 26 23:45:26 2015

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

Username: / as sysdba

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

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=common_usr_cdb.dmp logfile=common_usr_cdb.log schemas=C##ABBAS
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
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 "C##ABBAS"."T"                              72.67 MB  667728 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /backup/exp/prodcdb/common_usr_cdb.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 14:16:53 2015 elapsed 0 00:01:16

Moreover, in an ideal situation we need not take export of the ROOT container objects unless we have data belonging to the common users in the ROOT container.

This is why, we can observe the following WARNING message while taking export of the ROOT container (CDB).

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Taking export of pluggable database

In 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. Hence, we would be more interested in taking export of the objects from the pluggable database.

Using datapump export for pluggable database (PDB) is identical to using datapump export for a Non-CDB database (normal oracle database).

The only difference in using datapump export for PDB is that, we must use a CONNECT IDENTIFIER (TNS ALIAS) in the DATAPUMP EXPORT command prompt while initiating the export. This is to ensure that, we are initiating the datapump 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

Notice 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)

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 27 00:12:00 2015

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

Enter user-name: sys@prodpdb1 as sysdba
Enter 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

sys@PRODPDB1> show con_name

CON_NAME
------------------------------
PRODPDB1

Apart from this pre-requisite, we can use datapump export in pluggable database just the same way we do for a Non-CDB database.

Datapump restrictions with pluggable database

One important restriction while using datapump for a pluggable database is that, we can not use a directory owned by the ROOT container (CDB) or owned by a different pluggable database for performing datapump export/import.

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

Trying to use a directory belonging to another pluggable database or to the ROOT container, would result into following kind of 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 can not 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 can not 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 datapump export/import.

6 Comments
  1. Claudia
  2. feras
  3. majid
  4. Charlie Chen
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed