oracle dblink删除、测试、创建方法及注意事项

chairrat

已于 2022-10-11 11:14:04 修改

阅读量2.3k

点赞数

文章标签： oracle 数据库

于 2022-10-10 09:13:29 首次发布

本文链接：https://blog.csdn.net/chairrat/article/details/126956889

版权

1、oracle 创建dblink方法总结了下，有如下几种创建写法:

-- Remote Username: scott
-- Remote Password: tiger

--(1): create public database link to a user in a remote database, with full connection string.

CREATE PUBLIC DATABASE LINK db103
CONNECT TO scott IDENTIFIED BY "tiger"
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=vir-PC)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbdao)))';

--(2): create public database link using tnsname of tnsnames.ora cd $ORACLE_HOME/network/admin
vi tnsnames.ora
--adding following tnsname into tnsnames.ora file:

dao =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbdao)
    )

--注意:tnsnames.ora文件的格式，每行要有必要的缩进和空格，不能直接写成一行，其他比如sqlnet.ora文件配置参数时也要注意格式这个问题。

--利用上面配置的tnsname创建public database link:
create public database link db102 connect to scott identified by "tiger" using 'dao';

--(3):create public databse link: using 'IP:PORT/service_name':

create public database link db101 connect to scott identified by "tiger" using '192.168.31.101:1521/dbdao';

--注意上面语句，由于1521是默认端口，所以可以省略的('192.168.31.101/dbdao')。

--通过SHOW PARAMETER GLOBAL_NAMES，可以查看到其值是FALSE或者TRUE。GLOBAL_NAMES指明连接数据库的方式。如果这个参数设置为TRUE，那么在建立数据库链接时就必须用相同的名字连接远程数据库。当GLOBAL_NAMES参数设置为TRUE时，使用DATABASE LINK时，DATABASE LINK的名称必须与被连接库的GLOBAL_NAME一致(目标远程库可以查到GLOBAL_NAME "select * from global_name;")。对于GLOBAL_NAMES=FALSE的情况，则DBLINK的名称可以自定义。

2、dblink的删除和查询:

--删除dblink

drop public database link db103;

--查询所有dblink：

select * from dba_db_links;

3、dblink的测试:

--简单测试:

sys@DB11G> select * from dual@db102;

D
-
X

4、查看dblink DDL语句:

(1)脚本

-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/script_creation/db_link_ddl.sql
-- Author : Tim Hall
-- Description : Creates the DDL for DB links for the specific schema, or all schemas.
-- Call Syntax : @db_link_ddl (schema or all)
-- Last Modified: 16/03/2013
-- -----------------------------------------------------------------------------------
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('DB_LINK', db_link, owner)
FROM dba_db_links
WHERE owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'));

SET PAGESIZE 14 LINESIZE 1000 FEEDBACK ON VERIFY ON

--------------------------------------------------------------------------------------------------------------------------------

SELECT 'SELECT dbms_metadata.get_ddl(''DB_LINK'',''' || db_link || ''',''' ||
owner || ''') FROM dual;'
FROM dba_db_links;

--------------------------------------------------------------------------------------------------------------------------------

--当通过dbms_metadata.get_ddl获得dblink的ddl语句时，从11.2.0.4开始，是无法显示密码的，这可能是出于安全的考虑，这时候如果要做dblink迁移，知道密码的话可以用密码替换掉ddl语句中的变量，来重新执行ddl语句进行创建。但是对于大批量且不知道密码的情况下，可以参考下面MOS上的文章利用数据泵导出导入来解决。比如:"SELECT dbms_metadata.get_ddl('DB_LINK','DBSCOTT','PUBLIC') FROM dual;" 执行结果如下:(其中IDENTIFIED BY VALUES ':1') , " ':1'" 替换掉了原来的密码值。

CREATE PUBLIC DATABASE LINK "DBSCOTT"
CONNECT TO "SCOTT" IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.31.88)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = db11g)))'

DBMS_METADATA.GET_DDL for 'DB_LINK' Always Returns BY VALUES ':1' (Doc ID 1912244.1)

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

In versions earlier than 11.2.0.4, DBMS_METADATA.get_ddl for 'DB_LINK' would return encrypted password for values in identified by clause. So the returning command could be run successfully to recreate the db link in some other database.

Starting 11.2.0.4, DBMS_METADATA.get_ddl for 'DB_LINK' would return ':1' for values in identified by clause.

eg.

On 11.2.0.1:
=========

SQL> create database link db11201 connect to vp identified by vp using 'ORCL';
SQL> set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
SQL> select dbms_metadata.get_ddl('DB_LINK','DB11201.VP.COM') from dual;

CREATE DATABASE LINK "DB11201.VP.COM"
CONNECT TO "VP" IDENTIFIED BY VALUES '05755352C6B78A96D8B47A8557DE0E2A08'
USING 'ORCL'

On 11.2.0.4:
=========

SQL> create database link db11204 connect to vp identified by vp using 'ORCL';
SQL> set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
SQL> select dbms_metadata.get_ddl('DB_LINK','DB11204') from dual;

CREATE DATABASE LINK "DB11204"
CONNECT TO "VP" IDENTIFIED BY VALUES ':1'
USING 'ORCL'

CHANGES

None

CAUSE

Bug 18461318 was opened for this issue which is closed as not feasible to fix as this behavior was introduced to fix the security bug.

此问题的Bug 18461318已打开，由于不可修复而关闭，因为引入此行为是为了修复安全Bug。

SOLUTION

The bug is not feasible to fix. So the workarounds are:

1. Use actual password in returned DDL:

IDENTIFIED BY VALUES :1
by
IDENTIFIED BY current_dblink_password

2. If the password for db link is unknown then use datapump exp/imp using include=db_link

e.g.

E.g.

- create a parfile with the database link that you need to export:

INCLUDE=DB_LINK:"LIKE 'TEST01'"

- run the export

#> expdp system/password dumpfile=link.dmp content=metadata_only parfile=parfile.par full=y

- run the import

#> impdp system/password dumpfile=link.dmp logfile=link.log full=y