欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 10g的EXPDP与IMPDP的导出与导入测试

程序员文章站 2024-01-06 22:54:52
...

通过DB_LINK的复制功能,利用expdp,impdp进行数据的远程备份及恢复。进行测试前,将服务器B上scott用户的dept,emp进行删除,本测

实验环境:

服务器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

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

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 scott/tiger@ip40 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

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/********@ip40 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上;

scott/tiger@ip40 连接到服务器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.