Oracle 12c PDB imports and exports using EXPDP/IMPDP

12c has launched a pluggable database with multiple database PDB in the form of multiple tenants in a single container cdb. The data pump import and export for the PDB is slightly different from the traditional database.

1, need to add tansnames for PDB

2, import and export need to specify its tansnames value within the UserID parameter, such as Userid=user/[email protected]

Data Pump Export

1. View the current SID, view the PDB and switch to the container database, where the pluggable database is PDBORCL

[Email protected] admin]$ echo $ORACLE _sid[[email protected] Admin]orcl

Log in to CDB, view the PDB,

Sql> show con_namecon_name------------------------------cdb$rootsql> show pdbs     CON_ID CON_NAME               open mode  restricted---------- ------------------------------ ----------  ----------pdb$seed                       READ ONLY    NOPDBORCL                         MOUNTEDSQL> alter pluggable database all open; Pluggable database altered. sql> show pdbs    con_id con_name               open mode  restricted---------- ---------- -------------------- ---------- ----------pdb$seed                       READ ONLY   nopdborcl                        read write no

Switch to PDBORCL

Sql> alter session set CONTAINER=PDBORCL; Session altered. Sql>

2. Look at the sample user Scott, and use that user's data for import and export at a later schema level.

sql> select owner, table_name from dba_tables where owner= ' SCOTT '; owner                     table_name------------------------------ ----------------------------------------scott                     salgradescott                    BONUSSCOTT                    EMPSCOTT                    dept 

3. Create a data pump user with DBA authority separately

Sql> Grant DBA to DP identified by DP; Grant succeeded.

4, create a data pump directory Dp_dir, the path is the Oracle home directory

sql> Create or replace directory Dp_dir as '/home/oracle ';D irectory created. Sql> exit

5. Grant DP users read and write access to the data pump path

(If this step of DBA authority can be omitted, for the completeness of the test is kept here)

Sql> Grant Read,write on the directory Dp_dir to DP; Grant succeeded.

6, set Tnsnames.ora, increase PDBOROCL. Service_Name is the instance name of the PDB, here is PDBORCL

[[Email protected] admin]$ pwd/data/app/oracle/product/12.1.0/dbhome_1/network/admin[[email  protected] admin]$ cat tnsnames.ora # tnsnames.ora Network  configuration file: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora#  Generated by oracle configuration tools. orcl =   (description =     (address =  (PROTOCOL =  TCP) (HOST = XQZT) (port = 1521))      (connect_data =        (server = dedicated)        (service_name =  ORCL)     )    pdborcl =   (description =      (address =  (protocol = tcp) (HOST = XQZT) (port = 1521))      (connect_data =       (server  = dedicated)        (SERVICE_NAME =PDBORCL)     )   )

7, test the validity of Tnsnames.ora, if return OK (0 msec) indicates the configuration is successful

[[email protected] admin]$ tnsping pdborcltns Ping Utility for linux:version 12.1.0.2.0-production on 10-dec-2015 09:10  : 34Copyright (c) 1997, Oracle. All rights reserved. Used parameter files:/data/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.oraused TNSNAMES adapter to  Resolve the aliasattempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = xqzt) (PORT = 1521)) (Connect_data = (SERVER = dedicated) (service_name =PDBORCL)) OK (0 msec)

8. Data Pump Export

• The user name password is DP/DP and is pointed to by TNSNames PDBORCL

• The data Pump directory is: dp_dir, OS path is/home/oracle

• The export file is:/home/oracle/scott_pdborcl.dmp

• The Export log is:/home/oracle/scott_pdborcl.log

• The export mode is scheme and can also be understood as User: Scott

[[Email protected] ~]$ expdp dp/[email protected] directory=dp_dir dumpfile =scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scottexport: release 12.1.0.2.0  - production on thu dec 10 09:32:05 2015copyright  (c)  1982,  2014, oracle and/or its affiliates.  all rights reserved. connected to: oracle database 12c enterprise edition release  12.1.0.2.0 - 64bit productionwith the partitioning, olap, advanced  analytics and real application testing optionsstarting  "DP". " Sys_export_schema_01 ":   dp/******** @pdborcl  directory=dp_dir dumpfile=scott_pdborcl.dmp  logfile=scott_pdborcl.log schemas=scott estimate in progress using blocks  method ... Processing object type schemA_export/table/table_datatotal estimation using blocks method: 192 kbprocessing  object type schema_export/userprocessing object type schema_export/system_ grantprocessing object type schema_export/role_grantprocessing object type  Schema_export/default_roleprocessing object type schema_export/pre_schema/procact_ schemaprocessing object type schema_export/table/tableprocessing object type  schema_export/table/index/indexprocessing object type schema_export/table/constraint/ Constraintprocessing object type schema_export/table/index/statistics/index_ statisticsprocessing object type schema_export/table/constraint/ref_constraintprocessing  object type schema_export/table/statistics/table_statisticsprocessing object type  schema_export/statistics/marker. . exported  "SCOTT". " DEPT "                               6.023 KB        4 rows. . exported  "SCOTT". " EMP "                                8.773 KB       14 rows. . exported  "SCOTT". " Salgrade "                           6.023 KB       10 rows. . exported  "SCOTT". " BONUS "                           &Nbsp;      0 kb       0 rowsmaster  table  "DP". " Sys_export_schema_01 " successfully loaded/unloaded************************************************** DUMP FILE SET FOR DP. sys_export_schema_01 is:  /home/oracle/scott_pdborcl.dmpjob  "DP". " Sys_export_schema_01 " successfully completed at Thu Dec 10 09:32:29  2015 elapsed 0 00:00:21[[email protected] ~]$

10. View the Export file

[[email protected] ~]$ ls-l scott_pdborcl.dmp scott_pdborcl.log-rw-r-----1 Oracle oinstall 356352 December 09:32 scott_p dborcl.dmp-rw-r--r--1 Oracle Oinstall 1960 December 09:32 Scott_pdborcl.log

11, in order to test whether the export file can be imported, we first delete the PDBORCL Scott user

Sql> Select COUNT (*) from Scott.  DEPT; COUNT (*)----------sql> drop user Scott Cascade; User dropped. Sql>

The table that accesses the user is no longer present.

Sql> Select COUNT (*) from Scott. Dept;select Count (*) from Scott. DEPT *error at line 1:ora-00942:table or view does not exist

12. Import Scott Users

[[Email protected] ~]$ impdp dp/[email protected] directory=dp_dir dumpfile =scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scottimport: release 12.1.0.2.0  - Production on Thu Dec 10 09:39:02 2015Copyright  (c)  1982,  2014, oracle and/or its affiliates.  all rights reserved. connected to: oracle database 12c enterprise edition release  12.1.0.2.0 - 64bit productionwith the partitioning, olap, advanced  analytics and real application testing optionsmaster table  "DP". " Sys_import_schema_01 " successfully loaded/unloadedStarting " DP "." Sys_import_schema_01 ":   dp/******** @pdborcl  directory=dp_dir dumpfile=scott_pdborcl.dmp  logfile=scott_pdborcl_imp.log schemas=scott processing&nbsP;object type schema_export/userprocessing object type schema_export/system_ grantprocessing object type schema_export/role_grantprocessing object type  Schema_export/default_roleprocessing object type schema_export/pre_schema/procact_ schemaprocessing object type schema_export/table/tableprocessing object type  schema_export/table/table_data. . imported  "SCOTT". " DEPT "                               6.023 KB        4 rows. . imported  "SCOTT". " EMP "                                8.773 KB       14 rows. . imported  "SCOTT". " Salgrade "                           6.023 KB       10 rows. . imported  "SCOTT". " BONUS "                                  0 kb        0 rowsprocessing object type schema_export/table /index/indexprocessing object type schema_export/table/constraint/constraintprocessing  Object type schema_export/table/index/statistics/index_statisticsprocessing object type  schema_export/table/constraint/ref_constraintprocessing object type schema_export/table/ statistics/table_statisticsprocessing object type schema_export/statistics/markerjob  "DP". " Sys_import_schema_01 " successfully completed at Thu Dec 10 09:39:06  2015 elapsed 0 00:00:04[[email protected] ~]$

13. Test the results of the import

Sql> Select COUNT (*) from Scott.  DEPT; COUNT (*)----------4

Import Successful!

Oracle 12c PDB imports and exports using EXPDP/IMPDP