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

最新推荐文章于 2024-01-08 10:59:06 发布

cuibaodun6837

最新推荐文章于 2024-01-08 10:59:06 发布

阅读量149

点赞数

文章标签：数据库网络操作系统

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

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

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

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