簡介
在之前的部落格中已經配置過了，可以參考：https://www.xmmup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html#Oracle_lian_jie_daoMySQL
Oracle使用DG4ODBC數據閘道器連線MySQL資料庫， 可以不用安裝Gateways閘道器 ，其原理圖如下：
從上圖可知，Oracle連線MySQL需要涉及到如下元件：DG4ODBC, ODBC Driver Manager, ODBC Driver。
環境準備
1-- oracle 21c
2docker rm -f ora21tomariadb
3docker run -d --name ora21tomariadb -h lhroracle21c \
4 -v /sys/fs/cgroup:/sys/fs/cgroup \
5--privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \
6 /usr/sbin/init
7
8-- 由於唯讀主目錄特性，21c中的spfile、pfile、密碼檔預設在$ORACLE_BASE/dbs目錄下，例如：/u01/app/oracle/dbs下。
9[oracle@lhroracle21c dbs]$ pwd
10/u01/app/oracle/dbs
11[oracle@lhroracle21c dbs]$ ll
12total 18308
13-rw-rw---- 1 oracle oinstall 1544 Mar 18 16:19 hc_LHRCDB.dat
14-rw-r----- 1 oracle oinstall 686 Mar 18 16:19 initLHRCDB.ora
15-rw-r----- 1 oracle oinstall 24 Aug 16 2021 lkLHRCDB
16-rw-r----- 1 oracle oinstall 2048 Aug 16 2021 orapwLHRCDB
17-rw-r----- 1 oracle oinstall 18726912 Aug 16 2021 snapcf_LHRCDB.f
18-rw-r----- 1 oracle oinstall 3584 Mar 18 17:01 spfileLHRCDB.ora
19[oracle@lhroracle21c dbs]$
20
21
22
23
24
25-- mysql 8.1
26mkdir -p /etc/mysql/ora2mysql81/
27cat > /etc/mysql/ora2mysql81/conf/my.cnf <<"EOF"
28[mysqld]
29default-time-zone = '+8:00'
30log_timestamps = SYSTEM
31skip-name-resolve
32log-bin
33server_id=813420
34character_set_server=utf8mb4
35default_authentication_plugin=mysql_native_password
36EOF
37
38
39docker run -d --name ora2mysql81 -h ora2mysql81 \
40 -v /etc/mysql/mysql81/conf:/etc/mysql/conf.d \
41 -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
42 mysql:8.1.0
43
44
45mysql -uroot -plhr -h192.92.0.54 -P3306 -e "selectnow(),@@hostname,@@version;"
46mysql -uroot -plhr -h192.92.0.54 -P3306 -e "createdatabase lhrdb;"
47mysql> use lhrdb;
48Database changed
49mysql> createtable t1(idint);
50Query OK, 0 rows affected (0.02 sec)
51
52mysql> insertinto t1 values(1);
53Query OK, 1 row affected (0.04 sec)
54
55
56
57
58
59[root@alldb ~]# mysql -uroot -plhr -h192.92.0.54 -P3306 -e "select now(),@@hostname,@@version;"
60mysql: [Warning] Using a password on the command line interface can be insecure.
61+---------------------+-------------+-----------+
62| now() | @@hostname | @@version |
63+---------------------+-------------+-----------+
64| 2024-03-18 16:23:35 | ora2mysql81 | 8.1.0 |
65+---------------------+-------------+-----------+
開始配置
1、以下內容均在Oracle的主機配置。
2、MySQL的主機為192.92.0.54
1-- root操作
2yum install -y unixODBC unixODBC-devel mysql-connector-odbc
3
4-- 升級 mysql-connector-odbc驅動
5rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7.rpm
6yum update -y unixODBC unixODBC-devel mysql-connector-odbc
7
8
9cat >/etc/odbc.ini <<"EOF"
10[myodbc8]
11#Driver = /usr/lib64/libmyodbc8w.so
12Driver = /usr/lib64/libmyodbc8w.so
13Description = Connector/ODBC 5.2 Driver DSN
14SERVER = 192.92.0.54
15PORT = 3306
16USER = root
17PASSWORD = lhr
18DATABASE = lhrdb
19OPTION = 0
20TRACE = OFF
21EOF
22
23
24export ODBCINI=/etc/odbc.ini
25isql myodbc8 -v
26showdatabases;
27
28[oracle@lhroracle21c admin]$ export ODBCINI=/etc/odbc.ini
29[oracle@lhroracle21c admin]$ isql myodbc8 -v
30+---------------------------------------+
31| Connected! |
32| |
33| sql-statement |
34| help [tablename] |
35| quit |
36| |
37+---------------------------------------+
38SQL> showdatabases;
39+-----------------------------------------------------------------+
40| Database |
41+-----------------------------------------------------------------+
42| information_schema |
43| lhrdb |
44| mysql |
45| performance_schema |
46| sys |
47+-----------------------------------------------------------------+
48SQLRowCount returns 5
495 rows fetched
50SQL>
51
52
53
54
55
56
57-- oracle操作
58cd /u01/app/oracle/product/21c/dbhome_1/network/admin
59cat >> tnsnames.ora <<"EOF"
60myodbc8 =
61 (DESCRIPTION=
62 (ADDRESS=
63 (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)
64 )
65 (CONNECT_DATA=
66 (SID=myodbc8)
67 )
68 (HS=OK)
69)
70EOF
71
72-- vi listener.ora
73SID_LIST_LISTENER=
74 (SID_LIST=
75 (SID_DESC=
76 (SID_NAME=myodbc8)
77 (ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1)
78 (PROGRAM=dg4odbc)
79 (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/21c/dbhome_1/lib)
80 )
81 )
82
83-- 註意Oracle 21c的位置
84cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc8.ora <<"EOF"
85HS_FDS_CONNECT_INFO=myodbc8
86HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
87HS_FDS_SUPPORT_STATISTICS=FALSE
88HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
89EOF
90
91
92
93lsnrctl reload
94lsnrctl status
95tnsping myodbc8
96
97[oracle@lhroracle21c admin]$ tnsping myodbc8
98
99TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 18-MAR-2024 16:33:15
100
101Copyright (c) 1997, 2021, Oracle. All rights reserved.
102
103Used parameter files:
104/u01/app/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora
105
106
107Used TNSNAMES adapter to resolve the alias
108Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8)) (HS=OK))
109OK (0 msec)
110[oracle@lhroracle21c admin]$
111
112
113
114
115
116[oracle@lhroracle21c admin]$ odbcinst -j
117unixODBC 2.3.1
118DRIVERS............: /etc/odbcinst.ini
119SYSTEM DATA SOURCES: /etc/odbc.ini
120FILE DATA SOURCES..: /etc/ODBCDataSources
121USER DATA SOURCES..: /etc/odbc.ini
122SQLULEN Size.......: 8
123SQLLEN Size........: 8
124SQLSETPOSIROW Size.: 8
125[oracle@lhroracle21c admin]$
126
127
128
129
130createpublicdatabaselink mysqltest connectto"root"identifiedby"lhr"using'myodbc8' ;
131selectcount(*) from"t1"@mysqltest;
132
133
134SYS@LHRCDB> selectcount(*) from"t1"@mysqltest;
135
136 COUNT(*)
137----------
138 1
總結
1、Oracle透過dblink連線mysql和PG不需要安裝Gateways透明閘道器軟體
2、若Oracle是21c版本，則由於Oracle 21c的預設唯讀主目錄特性，則在配置透明閘道器檔時，initmyodbc8.ora檔不應放在
$ORACLE_HOME/hs/admin
目錄下，而應該放在
$ORACLE_BASE/homes/OraDB21Home1/hs/admin
目錄下：
1cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc8.ora <<"EOF"
2HS_FDS_CONNECT_INFO=myodbc8
3HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
4HS_FDS_SUPPORT_STATISTICS=FALSE
5HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
6EOF
7
8
9-- 檢視是否開啟唯讀主目錄，Oracle 21c預設開啟
10cat $ORACLE_HOME/install/orabasetab
3、Oracle到mariadb也是可以的（10.4.24-MariaDB 已測試）
4、dg4odbc日誌檔在
hs/log/
目錄下。
參考
https://www.xmmup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html#Oracle_lian_jie_daoMySQL
https://www.xmmup.com/detailed-overview-of-connecting-oracle-to-mysql-using-dg4odbc-database-link-doc-id-1320645-1.html
