Data pump import and export for Oracle 12c PDB

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 Import and export example1. View the current SID, view the PDB and switch to the container database, where the pluggable database is PDBORCL

[[email protected] admin] $ echo $ORACLE _SIDORCL

Log in to CDB, view the PDB,

Sql>Show Con_namecon_name------------------------------Cdb$rootsql>show PDBs con_id con_nameOPENMODE RESTRICTED---------- ------------------------------ ---------- ----------     2Pdb$seedREAD  onlyNO3PDBORCL Mountedsql> AlterPluggableDatabase  All Open; PluggableDatabasealtered. SQL>show PDBs con_id con_nameOPENMODE RESTRICTED---------- ------------------------------ ---------- ----------     2Pdb$seedREAD  onlyNO3PdborclREADWRITE NO

Switch to PDBORCL

SQL>alterset 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.

Reference: Oracle 12c add Scott Users

SQL> fromwhere owner='SCOTT'; owner                   TABLE_NAME----------------------------------------------------------------------  SCOTT                   Salgradescott                   bonusscott                   empscott                   DEPT

3. Create a data pump user with DBA authority separately

SQL>Grant to by DP; Grant succeeded.

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

SQL>Createorreplaceas  '/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>grantread on 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 ConfigurationFile:/Data/App/Oracle/Product/12.1.0/Dbhome_1/Network/Admin/tnsnames.ora# Generated byOracle 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 forLinux:version12.1.0.2.0-Production onTen-dec- -  the:Ten: theCopyright (c)1997, the, 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 (0msec

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:release12.1.0.2.0-Production on Thu DecTen  the: +: to  -Copyright (c)1982, the, Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release12.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 SC Hema_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 to rows. . Exported "SCOTT". " Salgrade "6.023 KB rows. . Exported "SCOTT". " BONUS "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 09:32:29 elapsed 0 00:00:21[[email protected] ~]$

10. View the Export file

ls  -l scott_pdborcl.dmp  scott_pdborcl.log1356352: scott_pdborcl.dmp 1 Oracle oinstall   1960: Scott_pdborcl.log

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

SQL>Selectcount(* from Scott. DEPT;   COUNT (*) -- --------     4 SQL > Drop User Cascade   ; User dropped. SQL>

The table that accesses the user is no longer present.

SQL>Selectcount(* from Scott. DEPT; Select Count (* from Scott. DEPT                           *1: ORA-00942table or View  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:release12.1.0.2.0-Production on Thu DecTen  the: the: Geneva  -Copyright (c)1982, the, Oracle and/or its affiliates. All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release12.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 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 to rows. . Imported "SCOTT". " Salgrade "6.023 KB rows. . Imported "SCOTT". " BONUS "0 KB 0 rowsprocessing object Type schema_export/table/index/indexprocessing o Bject 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 09:39:06 elapsed 0 00:00:04[[email protected] ~]$

13. Test the results of the import

SQL>Selectcount(* from Scott. DEPT  ;   COUNT (*) -- --------     4

Import Successful!

Data pump import and export for Oracle 12c PDB