你好,游客 登录 注册 搜索
背景:
阅读新闻

expdp 只导出dblink

[日期:2017-02-11] 来源:Linux社区  作者:hbxztc [字体: ]

最近生产数据库要修改生产用户的密码,由于用户密码修改,各个库之间创建的dblink就失效了,需要重建。上生产一看dblink还真不少,而且谁建的都有,改起来比较费劲。于是想到可以使用expdp的方式导出所有的dblink,即可获取dblink的创建语句,然后修改原密码再在库上创建就可以了。

Oracle 生产环境中的库有两个版本10.2.0.4和11.2.0.3下面分别在10g和11g上做测试

Oracle 测试10g:

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0  Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

创建dblink到11g数据库

SQL> create public database link link_11g
connect to system identified by "123456"
using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )'; 
 
Database link created.
SQL> select * from dual@link_11g;
 
D
-
X
 
col owner for a30
col username for a30
col db_link for a30
col host for a50
set linesize 200
set pagesize 999
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links;
OWNER                  DB_LINK                USERNAME                HOST
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
PUBLIC                LINK_11G              SYSTEM              (DESCRIPTION =
                                                (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.
                                                2)(PORT = 1521))
                                                (CONNECT_DATA =
                                                  (SERVER = DEDICATED)
                                                  (SERVICE_NAME = orcl)
                                                )
                                                  )

使用expdp导出dblink

[oracle@rhel5 ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp full=y include=db_link
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 November, 2016 15:26:02
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=dump dumpfile=dblink.dmp full=y include=db_link
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/dblink.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 15:26:13

使用impdp查看导出的dblink的创建语句

[oracle@rhel5 ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
 
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 November, 2016 15:28:31
 
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 15:28:33
 
[oracle@rhel5 ~]$ cat /home/oracle/dblink.sql
-- CONNECT SYSTEM
-- new object type path is: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE PUBLIC DATABASE LINK "LINK_11G"
  CONNECT TO "SYSTEM" IDENTIFIED BY VALUES '0588B5151FD4089DC7B87F64727E740D26'
  USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )';

测试11g

sys@ORCL>select * from v$version;
 
BANNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建dblink

create public database link link_10g
connect to system identified by "123456"
using '(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )';
 
Database link created.
 
sys@ORCL>select * from dual@link_10g;
 
DUM
---
X
 
sys@ORCL>select OWNER,DB_LINK,USERNAME,HOST from dba_db_links;
 
OWNER                  DB_LINK                USERNAME                HOST
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------
PUBLIC                LINK_A                  SCOTT                orcl
PUBLIC                LINK_10G              SYSTEM              (DESCRIPTION =
                                                (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.
                                                11)(PORT = 1521))
                                                (CONNECT_DATA =
                                                  (SERVER = DEDICATED)
                                                  (SERVICE_NAME = mydb)
                                                )
                                                  )

expdp导出dblink

[oracle@rhel6 ~]$ expdp system/123456 directory=dump dumpfile=dblink.dmp full=y include=db_link
 
Export: Release 11.2.0.4.0 - Production on Thu Nov 10 15:34:24 2016
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=dump dumpfile=dblink.dmp full=y include=db_link
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/dblink.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Nov 10 15:34:42 2016 elapsed 0 00:00:15

使用impdp查看导出的dblink的创建语句

[oracle@rhel6 ~]$ impdp system/123456 directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
 
Import: Release 11.2.0.4.0 - Production on Thu Nov 10 15:35:38 2016
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** directory=dump dumpfile=dblink.dmp sqlfile=dblink.sql
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Nov 10 15:35:40 2016 elapsed 0 00:00:01
 
[oracle@rhel6 ~]$ cat /home/oracle/dblink.sql
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE PUBLIC DATABASE LINK "LINK_10G"
  CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1'
  USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )';
CREATE PUBLIC DATABASE LINK "LINK_A"
  CONNECT TO "SCOTT" IDENTIFIED BY VALUES ':1'
  USING 'orcl';

使用上面的方式可以查看dblink的创建语句。

从上面dblink创建语句的输出结果也可以看出10g导出的dblink创建语句可以看到密码的密文,而11g里就看不到了。

官方文档:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-02/140529.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款