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
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2P5Cwagv-1623844170883)(DM-dblink使用.assets/1619145926543.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fY0L7R48-1623844170888)(DM-dblink使用.assets/1619143674880.png)]
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,'您好');