DM-dblink使用

DM-dblink使用

测试环境

centos6.7 DM8.1.1.190

centos7.6 DM88.1.1.190

centos7.6 oracle 11g

1.DM->Oracle dblink

1.1使用sqlplus方式

1.1.1.在DM端使用dblink11g.tar.gz配置依赖

cp dblink11g.tar.gz /lib64
cd /lib64
tar -zxvf dblink11g.tar.gz
./run.sh
echo $LD_LIBRARY_PATH
/lib64::/home/dmdba/dmdbms/bin
#需要修改oracle的用户名、密码、ip、端口、sid
./sqlplus system/oracle@192.168.104.30:1521/ht

1.1.2.在DM上创建DBlink-oracle进行验证

CREATE OR REPLACE public LINK link_orac_scott CONNECT 'ORACLE' WITH SCOTT IDENTIFIED BY "123456"
USING '(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.142.243)(PORT =1521)))
(CONNECT_DATA = (SERVICE_NAME = orcl) )
)';
--用户名和密码区分大小写

select * from v$version@link_orac_scott;

1.2使用odbc方式

1.2.1.在DM端配置odbc-ORACLE与DM同机器配置成功，不同机器存在缺少库文件的问题

odbc安装略过...

oracle 官网 http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html 下载：

 oracle_instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
 oracle_instantclient11.2-odbc-11.2.0.3.0-1.x86_64.rpm
 oracle_instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
直接rpm -ivh 安装
3.  odbc 的配置文件 在/usr/local/etc路径,
#odbc.ini 文件
[ORACLE]
Driver = ORCL
DSN = ORACLE
ServerName = ORCL
UID = scott
PWD = 123456
PORT = 1521
#odbcinst.ini 文件
[ORCL]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /home/dmdba/instantclient_11_2/libsqora.so.11.1


--4. 如果在非orcle机器需要创建/etc/oracle 目录，添加tnsnames.ora 文件，配置如下：
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.142.243 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

#5.环境变量修改

export TNS_ADMIN="/etc/oracle"
#/home/dmdba/instantclient_11_2是解压包的路径，rpm安装未没有尝试过
export ORACLE_HOME="/home/dmdba/instantclient_11_2"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/home/dmdba/instantclient_11_2"

[dmdba@CentOS7_3 instantclient_11_2]$ ldd libsqora.so.11.1 
	linux-vdso.so.1 =>  (0x00007ffc089ca000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f902ecc5000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f902e9c3000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f902e7a7000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f902e58d000)
	libclntsh.so.11.1 (0x00007f902bc1e000)
	libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007f902ba0c000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f902b63e000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f902eec9000)
	libnnz11.so (0x00007f902b271000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007f902b06f000)
	libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f902ae65000)
6. isql -v ORACLE scott 123456

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit;

配置连接DM的信息

[dmdba@CentOS7_2 bin]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/dmdba/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

cat odbcinst.ini
[ORCL]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /opt/odbc/instantclient_11_2/libsqora.so.11.1
[DM8 ODBC DRIVER]
Description     = ODBC DRIVER FOR DM8
Driver          = /home/dmdba/dmdbms/bin/libdodbc.so

cat odbc.ini
[ORACLE]
Driver = ORCL
DSN = ORACLE
ServerName = ORCL
UID = scott
PWD = 123456
PORT = 1521
[DM8]
Description = DM ODBC DSND
Driver = DM8 ODBC DRIVER
ServerName = localhost
UID = SYSDBA
PWD = SYSDBA
PORT = 5236

1.2.2在DM上创建DBlink-odbc进行验证

create or replace public LINK link_mysql connect 'ODBC' with "scott" identified by "123456" using 'ORACLE';
--using “” 为unixODBC的链接名
select * from dept@LINK_MYSQL;

2.Oracle->DM dblink

2.1在oracle端配置连接DM的ODBC

安装unixODBC

#在odbcinst.ini中增加
[DM8 ODBC DRIVER]
Description     = ODBC DRIVER FOR DM8
Driver          = /home/dmdba/dmdbms/bin/libdodbc.so
#在odbc.ini中增加
[DM8]
Description = DM ODBC DSND
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = SYSDBA
PORT = 5236

2.2.验证odbc连接DM

isql -v dm8

2.3.在oracle端配置监听和tnsnames.ora

#注SERVICE_NAME与odbc中odbc.ini里配置的连接名保持一致
vi $ORACLE_HOME/network/admin/tnsnames.ora
DM8 =  
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SERVICE_NAME=DM8)  
    )  
    (HS=OK)  
)  
#注 SID_NAME与
vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER=  
  (SID_LIST=  
    (SID_DESC=  
      (SID_NAME=DM8)  
      (ORACLE_HOME=/opt/Oracle11g/oracle/product/11.2.0/db_1)  
      (PROGRAM=dg4odbc)  
      (ENVS=LD_LIBRARY_PATH=/home/dmdba/dmdbms/bin:/opt/Oracle11g/oracle/product/11.2.0/db_1/lib)  
    )   
  )

vi $ORACLE_HOME/network/admin/tnsnames.ora
DMDB01 =  
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SERVICE_NAME=DMDB01)  
    )  
    (HS=OK)  
)  
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=DMDB01)
      (ORACLE_HOME=/opt/Oracle11g/oracle/product/11.2.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/home/dmdba/dmdbms/bin:/opt/Oracle11g/oracle/product/11.2.0/db_1/lib)
    )
  )

2.4.配置hs

如果搭建过程中；

cd $ORACLE/hs/admin
#创建 initDM8.ora 并输入如下内容
vim initDM8.ora
HS_FDS_CONNECT_INFO = DM8

HS_FDS_TRACE_LEVEL = debug #可以在$ORACLE/hs/log查看信息，如果用于生产一定要设置为off

set ODBCINI=/etc/odbc.ini

HS_FDS_SHAREABLE_NAME =libodbc.so

2.5.重新加载监听

lsnrctl reload
lsnrctl status
tnsping DM8

2.6.ORACLE dblink验证

 grant create public database link to scott;
 create   public   database  link dblink_DM8  connect   to   "SYSDBA"  identified  by   "SYSDBA"  using  'DM8' ;  
select   *  from  TABLE_1@dblink_DM8;  

3.DM->mysql dblink

3.1安装mysql odbc

 rpm -ivh mysql-connector-odbc-5.3.14-1.el7.x86_64.rpm

3.2mysql创建远程用户与远程数据库

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| #mysql50#mysql-bin-obar |
| performance_schema      |
| sys                     |
+-------------------------+
5 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table test ( id int ,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test (id,name) values (1,'nihao');
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on test.* to 'root'@'%' IDENTIFIED BY 'njnu123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
连接测试
[root@mysqlre1 ~]# mysql -ukingle -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 747202
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql>

3.3配置unixodbc

msyql5.7.33

vim odbc.ini
[mysql]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 192.168.0.187
Port            = 3306      ----mysql 端口
User            = root   ----mysql连接用户
Password        = 123456 ---mysql 连接密码
Database        = test  ---mysql连接数据库
[mysql]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc5w.so
Server          = 192.168.142.243
Port            = 3306     
User            = root  
Password        = njnu123456 
Database        = test  
[mysqlodbc]
Driver = MySQL ODBC 5.3 Unicode Driver 
SERVER = 192.168.142.243
PORT = 3306
USER = root      
PASSWORD = njnu123456    
CHARSET= gbk      
DATABASE=  test 
OPTION = 3
TRACE = OFF
vim /etc/odbcinst.ini
[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1

创建语句

create or replace public LINK link_mysqlodbc connect 'ODBC' with "root" identified by "njnu123456" using 'mysqlodbc';
  select * from "test"."test"@LINK_MYSQLODBC;

insert into "test"."test"@LINK_MYSQLODBC  VALUES(2,'您好');

更多资讯请上达梦技术社区了解： https://eco.dameng.com