12c pdb的数据泵导入导出简单示例

2019独角兽企业重金招聘Python工程师标准>>>

12c pdb的数据泵导入导出简单示例 2015-02-26 17:29:19

分类： Oracle

 

12c推出了可插拔数据库，在一个容器cdb中以多租户的形式同时存在多个数据库pdb。在为pdb做数据泵导入导出时和传统的数据库有少许不同。

1，需要为pdb添加tansnames

2，导入导出时需要在userid参数内指定其tansnames的值，比如userid=user/pwd@tans

 

下面通过一个例子演示pdb的数据泵导入导出操作

 

 

 

1，指定当前的sid为可插拔数据库。如果数据库中安装了多个实例，其中有普通单实例的，有插拔数据库的等等，为了减少错误首先确定其sid

[oracle@snow ~]$ export ORACLE_SID=cdb

 

2，登录cdb，查看pdb。如果此时pdb1是mount状态可以切换到pdb1下执行alter database open命令和普通数据库一样。或者使用alter pluggable database all open开启所有的pdb。

[oracle@snow ~]$ sqlplus / as sysdba

 

SYS@cdb >show pdbs

 

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

 

3，切换到pdb1

SYS@cdb >alter session set container=pdb1;

 

4，解锁示例用户hr，以后的schema级别导入导出演示就使用该用户的数据。

SYS@cdb >alter user hr identified by hr account unlock;

 

5，单独创建一个dba权限的数据泵用户

SYS@cdb >grant dba to dp identified by dp;

 

6，创建一个数据泵目录dp_dir，路径为oracle家目录

SYS@cdb >create or replace directory dp_dir as '/home/oracle';

 

7，dp用户在数据泵路径有读写权限（如果是dba权限的这一步可以省略，为了试验的完整性这里保留）

SYS@cdb >grant read,write on directory dp_dir to dp;

 

SYS@cdb >exit

 

8，设置tnsnames.ora，增加pdb1的链接。HOST按照自己主机的地址添加，SERVICE_NAME为pdb的示例名，这里为pdb1

[oracle@snow ~]$ cd $ORACLE_HOME/network/admin

[oracle@snow admin]$ cat tnsnames.ora

pdb1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

)

)

 

测试tnsnames.ora的有效性，如果返回OK (0 msec)表示配置成功

[oracle@snow admin]$ tnsping pdb1

 

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 09-FEB-2015 18:26:29

 

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

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))

OK (0 msec)

 

9，使用数据泵导出

• 用户名密码为dp/dp，并且通过tnsnames指向pdb1。命令行模式userid参数可以省去
• 数据泵目录为：dp_dir, OS路径是/home/oracle
• 导出文件为：/home/oracle/hr_pdb1.dmp
• 导出日志为：/home/oracle/hr_pdb1.log
• 导出模式为SCHEMA，也可以理解为用户：hr

 

[oracle@snow ~]$ expdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1.log schemas=hr

 

Export: Release 12.1.0.1.0 - Production on Mon Feb 9 18:29:37 2015

 

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

 

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

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DP"."SYS_EXPORT_SCHEMA_01": dp/********@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1.log schemas=hr

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 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/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

. . exported "HR"."COUNTRIES" 6.437 KB 25 rows

. . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows

. . exported "HR"."EMPLOYEES" 17.06 KB 107 rows

. . exported "HR"."JOBS" 7.085 KB 19 rows

. . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows

. . exported "HR"."LOCATIONS" 8.414 KB 23 rows

. . exported "HR"."REGIONS" 5.523 KB 4 rows

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

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

Dump file set for DP.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/hr_pdb1.dmp

Job "DP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 9 18:30:27 2015 elapsed 0 00:00:39

 

10，测试导出效果

10-1 删除pdb1的hr用户

SYS@cdb >alter session set container=pdb1;

 

Session altered.

 

SYS@cdb >select count(*) from hr.employees;

 

COUNT(*)

----------

107

 

SYS@cdb >

SYS@cdb >drop user hr cascade;

 

User dropped.

 

此时访问该用户的表已经不存在了

SYS@cdb >select count(*) from hr.employees;

select count(*) from hr.employees

*

ERROR at line 1:

ORA-00942: table or view does not exist

 

10-2 导入hr用户

 

[oracle@snow ~]$

[oracle@snow ~]$ impdp dp/dp@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1_imp.log schemas=hr

 

Import: Release 12.1.0.1.0 - Production on Mon Feb 9 18:37:42 2015

 

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

 

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

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "DP"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "DP"."SYS_IMPORT_SCHEMA_01": dp/********@pdb1 directory=dp_dir dumpfile=hr_pdb1.dmp logfile=hr_pdb1_imp.log schemas=hr

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/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HR"."COUNTRIES" 6.437 KB 25 rows

. . imported "HR"."DEPARTMENTS" 7.101 KB 27 rows

. . imported "HR"."EMPLOYEES" 17.06 KB 107 rows

. . imported "HR"."JOBS" 7.085 KB 19 rows

. . imported "HR"."JOB_HISTORY" 7.171 KB 10 rows

. . imported "HR"."LOCATIONS" 8.414 KB 23 rows

. . imported "HR"."REGIONS" 5.523 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "DP"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Feb 9 18:38:01 2015 elapsed 0 00:00:16

 

10-3 测试导入结果

SYS@cdb >select count(*) from hr.employees;

 

COUNT(*)

----------

107

 

导入成功

 

全文完！

转载于:https://my.oschina.net/rootliu/blog/1536006