一、DM-DM
配置前准备:
DM dblink需要借助 MAL 系统所以需要在数据库实例中配置MAL
配置补助
1、dm.ini开启MAL
MAL_INI=1
2、配置dmmal.ini
内容:mal_inst_name 和数据库INSTANCE_NAME一致,而且两个库不能一样
mal_port 端口不能和数据库的对外端口一致
[INST1]
mal_inst_name = DMSERVER
mal_host = 124.221.179.140
mal_port = 6236
[INST2]
mal_inst_name = DMDBLINK
mal_host = 124.221.179.140
mal_port = 6136
3、配置完成后重启数据库服务
4、创建dblink方式
方式1、instance_name
CREATE PUBLIC LINK TS1 CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING 'DMDBLINK'
方式2、ip+端口
CREATE PUBLIC LINK TS CONNECT WITH SYSDBA IDENTIFIED BY SYSDBA USING '10.0.4.11/6136';
二、DM-oracle
创建语句
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '127.0.0.1/orcl';
CREATE LINK LINK1 CONNECT 'ORACLE' WITH USER01 IDENTIFIED BY USER01PASSWD USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))';
三、DM-mysql
DM连接除oracle和DM之外的库全部采用ODBC
这里以mysql为示例
1、yum install unixODBC*
2、yum -y install mysql-connector-odbc
3、cat /etc/odbcinst.ini
4、 cat /etc/odbc.ini
[mysql]
Driver = MYSQL
Description = MyODBC 5 Driver
SERVER = 124.221.175.43
PORT = 3306
USER = test
Password = root
Database = test
5、创建dblink
CREATE PUBLIC LINK mts CONNECT 'ODBC' WITH "test" IDENTIFIED BY root USING 'mysql';
注意较于DM,mysql默认安装方式使大小写不敏感的。所以在查询表明和用户名是要加双引号
SQL> select * from "test"@mts;
行号 name
---------- ----
1 3333
已用时间: 22.752(毫秒). 执行号:800.
SQL>