在Oracle中通过dblink访问MySQL数据库

-- oracle 21c
docker rm -f ora21tomariadb
docker run -d --name ora21tomariadb -h lhroracle21c \
  -v /sys/fs/cgroup:/sys/fs/cgroup \
  --privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \
  /usr/sbin/init

-- 由于只读主目录特性，21c中的spfile、pfile、密码文件默认在$ORACLE_BASE/dbs目录下，例如：/u01/app/oracle/dbs下。
[oracle@lhroracle21c dbs]$ pwd
/u01/app/oracle/dbs
[oracle@lhroracle21c dbs]$ ll
total 18308
-rw-rw---- 1 oracle oinstall     1544 Mar 18 16:19 hc_LHRCDB.dat
-rw-r----- 1 oracle oinstall      686 Mar 18 16:19 initLHRCDB.ora
-rw-r----- 1 oracle oinstall       24 Aug 16  2021 lkLHRCDB
-rw-r----- 1 oracle oinstall     2048 Aug 16  2021 orapwLHRCDB
-rw-r----- 1 oracle oinstall 18726912 Aug 16  2021 snapcf_LHRCDB.f
-rw-r----- 1 oracle oinstall     3584 Mar 18 17:01 spfileLHRCDB.ora
[oracle@lhroracle21c dbs]$ 





-- mysql 8.1
mkdir -p /etc/mysql/ora2mysql81/
cat >  /etc/mysql/ora2mysql81/conf/my.cnf <<"EOF"
[mysqld]
default-time-zone = '+8:00'
log_timestamps = SYSTEM
skip-name-resolve
log-bin
server_id=813420
character_set_server=utf8mb4
default_authentication_plugin=mysql_native_password
EOF


docker run -d --name ora2mysql81 -h ora2mysql81  \
  -v /etc/mysql/mysql81/conf:/etc/mysql/conf.d \
  -e MYSQL_ROOT_PASSWORD=lhr -e TZ=Asia/Shanghai \
  mysql:8.1.0


mysql -uroot -plhr -h192.92.0.54 -P3306 -e "select now(),@@hostname,@@version;" 
mysql -uroot -plhr -h192.92.0.54 -P3306 -e "create database lhrdb;" 
mysql> use lhrdb;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.04 sec)





[root@alldb ~]# mysql -uroot -plhr -h192.92.0.54 -P3306 -e "select now(),@@hostname,@@version;" 
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+-------------+-----------+
| now()               | @@hostname  | @@version |
+---------------------+-------------+-----------+
| 2024-03-18 16:23:35 | ora2mysql81 | 8.1.0     |
+---------------------+-------------+-----------+

-- root操作
yum install -y unixODBC unixODBC-devel mysql-connector-odbc

-- 升级 mysql-connector-odbc驱动
rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7.rpm
yum update -y unixODBC unixODBC-devel mysql-connector-odbc


cat >/etc/odbc.ini <<"EOF"
[myodbc8]
#Driver = /usr/lib64/libmyodbc8w.so
Driver = /usr/lib64/libmyodbc8w.so
Description = Connector/ODBC 5.2 Driver DSN  
SERVER = 192.92.0.54  
PORT = 3306  
USER = root  
PASSWORD = lhr  
DATABASE = lhrdb  
OPTION = 0  
TRACE = OFF
EOF


export ODBCINI=/etc/odbc.ini
isql myodbc8 -v
show databases;

[oracle@lhroracle21c admin]$ export ODBCINI=/etc/odbc.ini
[oracle@lhroracle21c admin]$  isql myodbc8 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database                                                        |
+-----------------------------------------------------------------+
| information_schema                                              |
| lhrdb                                                           |
| mysql                                                           |
| performance_schema                                              |
| sys                                                             |
+-----------------------------------------------------------------+
SQLRowCount returns 5
5 rows fetched
SQL> 






-- oracle操作
cd  /u01/app/oracle/product/21c/dbhome_1/network/admin
cat >> tnsnames.ora <<"EOF"
myodbc8 =   
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SID=myodbc8)  
    )  
    (HS=OK)  
)
EOF

-- vi listener.ora
SID_LIST_LISTENER=  
  (SID_LIST=  
    (SID_DESC=  
      (SID_NAME=myodbc8)  
      (ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1)  
      (PROGRAM=dg4odbc)  
      (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/21c/dbhome_1/lib)  
    )   
  )

-- 注意Oracle 21c的位置
cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc8.ora <<"EOF"
HS_FDS_CONNECT_INFO=myodbc8
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
EOF



lsnrctl reload
lsnrctl status
tnsping myodbc8

[oracle@lhroracle21c admin]$ tnsping myodbc8

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 18-MAR-2024 16:33:15

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8)) (HS=OK))
OK (0 msec)
[oracle@lhroracle21c admin]$ 





[oracle@lhroracle21c admin]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[oracle@lhroracle21c admin]$ 




create   public   database  link mysqltest  connect   to   "root"  identified  by   "lhr"  using  'myodbc8' ;  
select   count(*)  from  "t1"@mysqltest;


SYS@LHRCDB> select   count(*)  from  "t1"@mysqltest;

  COUNT(*)
----------
         1

cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc8.ora <<"EOF"
HS_FDS_CONNECT_INFO=myodbc8
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
EOF


-- 查看是否开启只读主目录，Oracle 21c默认开启
cat $ORACLE_HOME/install/orabasetab