ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在异机上利用dblink做impdp数据导入

在异机上利用dblink做impdp数据导入

原创 Linux操作系统 作者:sxitsxit 时间:2012-03-02 10:48:50 0 删除 编辑
oracle10g开始,增加了impdp、expdp功能。这几天用dbblink做了一下异机上的数据导入,
这样就节省了数据导出(expdp),然后再导入的过程。

实验环境:
OS: rhel4as4
DB:oracle10gr2

源库:192.168.1.200 devdb 在源库(192.168.1.200)上创建了tt表
目的库:192.168.1.10 proddb


实验目的:

在目的库(10)上建立到源库(200)的dblink
在目的库(200)上执行impdp操作,将源库(10)中scott用户下的所有对象都导入到目的库(200)上


两个库的tnsname.ora 及listen.ora如下:

源库:192.168.1.200 devdb 上:

[oracle@sphost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)

ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[oracle@sphost admin]$
[oracle@sphost admin]$
[oracle@sphost admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sphost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

[oracle@sphost admin]$



目的库192.168.1.10 proddb上
[oracle@catalog admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA10HHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10hha)
)
)

ORA10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[oracle@catalog admin]$
[oracle@catalog admin]$
[oracle@catalog admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)

ora10hha =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

[oracle@catalog admin]$



实验步骤:


在目的库192.168.1.10 proddb上
以sysdba身份登录数据库,给scott用户授予创建dblink的权限

[oracle@catalog ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 15:49:03 2011

Copyright (c) 1982, 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

SQL> grant create database link to scott;

Grant succeeded.


在目的库192.168.1.10 proddb上创建一个到源库的dblink

SQL> create database link todevdb200 connect to scott identified by tiger using 'ORA10';

Database link created.

SQL> select count(*) from tt@todevdb200;

COUNT(*)
----------
199272

SQL>

能够查到次信息,表示dblink创建完毕


源库:192.168.1.200 devdb 有一张表tt

现在想把源库200中scott用户下的数据库导入到目的库10上

于是在目的库10上执行如下操作:


首先查看目标库(10)中的目录:

[oracle@catalog ~]$ sqlplus system/test as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 16 16:06:40 2011

Copyright (c) 1982, 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

SQL> column owner format a10;
SQL> column db_link format a15;
SQL> column host format a10;
SQL> column username format a8;
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- -------- ---------- ------------
SCOTT TODEV SCOTT ORA10 16-MAY-11


oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;


日志如下:

[oracle@catalog ~]$ impdp scott/tiger schemas=SCOTT network_link=TODEV;

Import: Release 10.2.0.1.0 - Production on Tuesday, 17 May, 2011 0:13:25

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_IMPORT_SCHEMA_01": scott/******** schemas=SCOTT network_link=TODEV
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.43 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
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
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."SALGRADE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
. . imported "SCOTT"."TT" 199272 rows
. . imported "SCOTT"."SYS_EXPORT_SCHEMA_01" 1057 rows
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
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at 00:15:04

可以看出,已经导入了tt表,为了验证
目的库192.168.1.10 proddb上执行



SQL> select count(*) from tt@todev;

COUNT(*)
----------
199272

SQL> select count(*) from tt;

COUNT(*)
----------
199272

SQL>
可以看出,目的库中已经成功导入了tt表。


注:如果要删除dblink,执行如下命令:

SQL> drop database link todev;
Database link dropped.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24862808/viewspace-717497/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle10g闪回实验
请登录后发表评论 登录
全部评论

注册时间:2011-04-14

  • 博文量
    98
  • 访问量
    293445