oracle10g的expdp与impdp的导出与导入测试-凯发app官方网站

实验环境：

服务器a：172.21.1.30

服务器b：172.21.1.40

两台服务器上分别运行两套oracle数据库，本实验以scott用户为例进行测试。

实验目的：

通过db_link的复制功能，利用expdp,impdp进行数据的远程备份及恢复。

进行测试前，将服务器b上scott用户的dept,emp进行删除，本测试只是使用

impdp将服务器a数据库下scott用户的dept,emp表导入到服务器b数据库

scott用户下。

操作步骤：

1.在服务器a，b上分别以sys身份登录数据库，并创建directory,db_link,命令如下：

session a: > create directory dp01 as '/bk/dp01';

directory created.

session a: >create public database link ln_db40 connect to scott identified by tiger using 'ip40';

database link created.

session a: >grant  connect,resource,exp_full_database,imp_full_database  to scott identified by tiger;

grant succeeded.

 

session b: > create directory dp02 as '/bk/dp02';

directory created.

session b: >create public database link ln_db30 connect to scott identified by tiger using 'ip30';

database link created.

session b: >grant  connect,resource,exp_full_database,imp_full_database  to scott identified by tiger;

grant succeeded.

 

2.在确认以上步骤中的db_link没问题后，做以下操作：

 

a.    服务器a：(将本地数据库中scott用户下的dept,emp两张表导出备份文件为exp01.dmp)

[oracle@session a: dp01]$  expdp scott/tiger directory=dp01 dumpfile=exp01.dmp  network_link=ln_db30  tables=emp,dept

 

export: release 10.2.0.1.0 - production on wednesday, 03 august, 2011 14:39:05

凯发app官方网站 copyright (c) 2003, 2005, oracle.  all rights reserved.

connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
starting "scott"."sys_export_schema_01":  scott/********@ip30 directory=dp01 dumpfile=exp01.dmp network_link=ln_db30  tables=emp,dept
estimate in progress using blocks method...
processing object type schema_export/table/table_data
total estimation using blocks method: 320 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/pre_schema/procact_schema
processing object type schema_export/table/table
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/table/comment
processing object type schema_export/table/constraint/ref_constraint
. . exported "scott"."sys_export_schema_01"              172.8 kb    1073 rows
. . exported "scott"."dept"                              5.656 kb       4 rows
. . exported "scott"."emp"                               7.820 kb      14 rows
master table "scott"."sys_export_schema_01" successfully loaded/unloaded
******************************************************************************
dump file set for scott.sys_export_schema_01 is:
  /bk/dp01/exp01.dmp
job "scott"."sys_export_schema_01" successfully completed at 14:39:37

 

[oracle@session a: dp01]$ ls

exp01.dmp   export.log  import.log

b. 将备份文件exp01.dmp从服务器a拷贝到服务器b：

[oracle@session a: dp01]$  scp   –r  exp01.dmp   172.21.1.40:/bk/dp02/

 

c.在服务器a上，执行以下命令，将dept,emp表导入到服务器b的scott用户下：

[oracle@session a: dp01]$  impdp scott/tiger@ip40 directory=dp02 dumpfile=exp01.dmp  tables=emp,dept

import: release 10.2.0.1.0 - production on wednesday, 03 august, 2011 14:34:57

凯发app官方网站 copyright (c) 2003, 2005, oracle.  all rights reserved.

connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
master table "scott"."sys_import_table_01" successfully loaded/unloaded
starting "scott"."sys_import_table_01":  scott/********@ip40 directory=dp02 dumpfile=exp01.dmp tables=emp,dept
processing object type table_export/table/table
processing object type table_export/table/table_data
. . imported "scott"."dept"                              5.656 kb       4 rows
. . imported "scott"."emp"                               7.820 kb      14 rows
processing object type table_export/table/index/index
processing object type table_export/table/constraint/constraint
processing object type table_export/table/constraint/ref_constraint
job "scott"."sys_import_table_01" successfully completed at 14:34:55

3.使用sys用户登录服务器a的数据库，进行测试，查看b的scott用户下的dept,emp表是否存在：

$ sqlplus / as sysdba           

session a: > conn scott/tiger

connected.

session a: > select * from tab;

tname                          tabtype  clusterid
------------------------------ ------- ----------
dept                           table
emp                            table

 

session a: > select * from tab@ln_db40;

tname                          tabtype  clusterid
------------------------------ ------- ----------
dept                           table
emp                            table

由以上实验不难看出，表dept,emp已成功导入b的scott用户。

expdp 可以导出所有数据库的数据，只要它能连接到这个数据库，或者它登录的数据库
上能读取到被导出的库的数据，因为此时可以通过数据库链来读取被导出的库的数据。

如下测试：

可以分为三个服务器：a，b，c，以下命令在a在执行：

[oracle@session a: dp01]$  expdp directory=dp02 dumpfile=exp01.dmp  network_link=ln_db50  tables=emp,dept

export: release 10.2.0.1.0 - production on wednesday, 03 august, 2011 14:39:05

凯发app官方网站 copyright (c) 2003, 2005, oracle.  all rights reserved.

connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
starting "scott"."sys_export_schema_01":  directory=dp02 dumpfile=exp01.dmp network_link=ln_db50  tables=emp,dept
estimate in progress using blocks method...
processing object type schema_export/table/table_data
total estimation using blocks method: 320 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/pre_schema/procact_schema
processing object type schema_export/table/table
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/table/comment
processing object type schema_export/table/constraint/ref_constraint
. . exported "scott"."sys_export_schema_01"              172.8 kb    1073 rows
. . exported "scott"."dept"                              5.656 kb       4 rows
. . exported "scott"."emp"                               7.820 kb      14 rows
master table "scott"."sys_export_schema_01" successfully loaded/unloaded
******************************************************************************
dump file set for scott.sys_export_schema_01 is:
  /bk/dp01/exp01.dmp
job "scott"."sys_export_schema_01" successfully completed at 14:39:37

说明：

expdp 在服务器a上；

连接到服务器b的数据库；

network_link=ln_db30 是服务器b的数据库中scott用户的数据库链，其指向服务器c的用户scott，
scott10用户中只有两个表dept,emp.

从上面的操作可以看出：expdp 可以是客户端的程序，但它导出的数据（dmp）必须存放在它登录

的数据库的目录下（本例中，存放在机器b的数据库directory=dp02下），而被导出的数据是通过

数据库链(network_link)到机器c的数据库里读取的。

另外注意：在这过程中,a数据库用户scott和c数据库用户scott（或者数据库链中的用户）都必须被

授予exp_full_database 权限。

nelwork_link意思是从本地数据库导入datafile直接到remote database,中间省略了exp的操作,

nelwork_link=source_database_link,确认nelwork_link参数是一个存在的目标数据库名称,同时

存在database link.

补充一下两个限制条件：

nelwork_link parameter restrictions

network imports do not support the use of evolved types.

a:  when the network_link parameter is used in conjunction with the tables parameter,

only whole tables can be imported (not partitions of tables).

所以，当使用impdp导入时多张表时，如果添加network_link参数，就会报错如下：

udi-00011: parameter dumpfile is incompatible with parameter network_link

b:  if the userid that is executing the import job has the imp_full_database role on the

target database, then that user must also have the exp_full_database role on the source

database.the only types of database links supported by data pump import are: public,

fixed-user, and connected-user. current-user database links are not supported.