Shulou(Shulou.com)06/01 Report--

12C introduced pluggable databases, where multiple database pdb exists simultaneously in the form of multi-tenancy in a container cdb. When doing data pump import and export for pdb, it is slightly different from the traditional database.

1. Need to add tansnames for pdb

2. When importing and exporting, you need to specify the value of its tansnames in the userid parameter, such as userid=user/pwd@tnsname

Data pump export

1. Check the current SID, view pdb, and switch to the container database. The pluggable database here is pdborcl [oracle@test admin] $echo $ORACLE_ Sid [oracle @ test admin] orcl.

Log in to cdb and view 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. Take a look at the sample user scott, whose data will be used for future schema-level imports and exports. 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 permission, SQL > grant dba to dp identified by dp;Grant succeeded.

4. Create a data pump directory dp_dir, and the path is oracle home directory SQL > create or replace directory dp_dir as'/ home/oracle';Directory created.SQL > exit

5. Grant dp users read and write permissions in the data pump path.

(if this step of dba permission can be omitted, it is reserved here for the integrity of the experiment.)

SQL > grant read,write on directory dp_dir to dp;Grant succeeded.

6. Set tnsnames.ora and add pdborocl. SERVICE_NAME is the instance name of pdb Here is pdborcl [oracle@xqzt admin] $pwd/data/app/oracle/product/12.1.0/dbhome_1/network/ admin [oracle @ xqzt 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 you return OK (0 msec), it means that the configuration is successful [oracle@xqzt admin] $tnsping pdborclTNS Ping Utility for Linux: Version 12.1.0.2.0-Production on 10-DEC-2015 09:10:34Copyright (c) 1997, 2014, 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 username and password is dp/dp and points to pdborcl through tnsnames

The data pump directory is dp_dir, and the OS path is / home/oracle

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

The export log is: / home/oracle/scott_pdborcl.log

Export mode is scheme, which can also be understood as user: scott

[oracle@xqzt] $expdp dp/dp@pdborcl 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" 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 [oracle@xqzt] $

10. View the exported file [oracle@xqzt ~] $ls-l scott_pdborcl.dmp scott_pdborcl.log-rw-r- 1 oracle oinstall 356352 December 10 09:32 scott_pdborcl.dmp-rw-r--r-- 1 oracle oinstall 09:32 scott_pdborcl.log

11. To test whether the exported file can be imported normally, we first delete the scott user SQL > select count (*) from scott.DEPT; COUNT (*)-SQL > drop user scott cascade; User dropped.SQL > of pdborcl.

The table that accesses the user no longer exists.

SQL > select count (*) from scott.DEPT;select count (*) from scott.DEPT * ERROR at line 1:ORA-00942: table or view does not exist12, import scott user [oracle@xqzt] $impdp dp/dp@pdborcl 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 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 [oracle@xqzt] $

13. Test import results

SQL > select count (*) from scott.DEPT; COUNT (*)-4

Import succeeded!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.