oracle如何连接mysql数据库

Primary Note for Oracle Gateway Products (Doc ID 1083703.1) Document  1351618.1 Installation Overview For Oracle Database Gateway Products https://docs.oracle.com/en/ Click on- - 'Database' option - All Oracle Database documentation - choose the version you require from the dropdown list - then 'Integrating Information' - Gateway documentation is under the 'Heterogeneous Connectivity' heading Oracle使用ODBC访问mysql [说明] Oracle数据库网关可以透明地访问其他数据库，Oracle HS Agent将SQL语句转换为非Oracle数据库（mysql）可以理解的SQL语句， 并通过ODBC 数据源发送该SQL语句，在mysql中执行后将结果返回Oracle数据库，本文介绍如何配置并访问mysql数据库。 [测试环境] Oracle 服务器 操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo) 软件版本：Oracle 11.2.0.4 主机地址：192.168.56.101 监听端口：1521 mysql 服务器 操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo) 软件版本：8.0.26-commercial MySQL Enterprise Server 主机地址：192.168.56.101 服务端口：3306 数据库名：t 用户密码：root/123456 [配置] 1  mysql 服务器 安装并创建相关数据库，使用远程测试，确保远程连接正常。 2  Oracle 服务器 2.1 安装相关包 安装 unixODBC 软件包 yum -y install unixODBC* 安装 mysql ODBC 驱动 yum install  mysql-connector*  --测试发现系统自带的版本较低触发了相关问题，无法解决，升级改包后问题解决 ++++++++++++++++++++++++++++++++++++++++++++报错信息++++++++++++++++++++++++++++++++++++++++++++++++++++++ select * from "t1"@msqlk                    * ERROR at line 1: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT =1521)))(CONNECT_DATA=(SID=mysql_test))) ORA-02063: preceding line from MSQLK Process ID: 9691 Session ID: 29 Serial number: 165 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm 下载新的驱动包 yum localinstall mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm 安装改应用包 2.2创建数据源 为驱动创建数据源 odbcinst -j odbc_config --odbcini --odbcinstini 2.3配置 mysql ODBC驱动库 /etc/odbcinst.ini 默认已经创建了[mysql]条目。 [PostgreSQL] Description=ODBC for PostgreSQL Driver=/usr/lib/psqlodbcw.so Setup=/usr/lib/libodbcpsqlS.so Driver64=/usr/lib64/psqlodbcw.so Setup64=/usr/lib64/libodbcpsqlS.so FileUsage=1 Trace=yes TraceFile=/usr/etc/sql01.log [MySQL] Description=ODBC for MySQL Driver=/usr/lib64/libmyodbc8w.so #Setup=/usr/lib/libodbcmyS.so Driver64=/usr/lib64/libmyodbc8w.so #Setup64=/usr/lib64/libodbcmyS.so FileUsage=1 [MySQL ODBC 8.0 Unicode Driver] Driver=/usr/lib64/libmyodbc8w.so UsageCount=1 [MySQL ODBC 8.0 ANSI Driver] Driver=/usr/lib64/libmyodbc8a.so UsageCount=1 2.4创建数据源 数据源名称为 mysql_test，可自定义其他名称，注意在后继配置中引用时保持一致。 mysql数据库名为 t, 服务器/端口为 192.168.56.101/3306, 用户名/密码为 root/123456 /etc/odbc.ini  [pgdsn] Driver = PostgreSQL Description = PostgreSQL ODBC Driver Database = runoobdb Servername = 192.168.56.101 Username = testuser Password = 123456 Port = 5432 UseDeclareFetch = 1 CommLog = /tmp/pgodbclink.log Debug = 1 LowerCaseIdentifier = 1 [mysql_test] Description     = ODBC for MySQL Driver          = /usr/lib64/libmyodbc8w.so Server          = 192.168.56.101 Port            = 3306 User            = root Password        = 123456 Database        = t  2.5 测试 ODBC 连接 [oracle@rhel7 admin]$ isql mysql_test +---------------------------------------+ | Connected!                            | |                                       | | sql-statement                         | | help [tablename]                      | | quit                                  | |                                       | +---------------------------------------+ SQL> select * from t1; +-----------+ | id        | +-----------+ | 30        | | 30        | +-----------+ SQLRowCount returns 2 2 rows fetched SQL>  2.6 创建 Oracle 数据库网关 在 $ORACLE_HOME/hs/admin 目录下创建 init<数据源名>.ora 文件，本测试为 initmysql_test.ora。 [oracle@rhel7 admin]$ cat initmysql_test.ora  HS_FDS_CONNECT_INFO = mysql_test #HS_FDS_TRACE_LEVEL = debug HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so #HS_FDS_SUPPORT_STATISTICS=FALSE HS_NLS_NCHAR=UCS2 HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 ##ODBC Configuration set ODBCINI=/etc/odbc.ini trace=0 2.7 调整 Oracle 监听配置 注意集群环境的监听文件位置为$ORACLE_HOME/network/admin/listener.ora ，rac环境为grid 下面的文件 SID_LIST_LISTENER =  (SID_LIST =     (SID_DESC=      (SID_NAME = pgdsn)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       (ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1")       (PROGRAM=dg4odbc)    )     (SID_DESC=      (SID_NAME = mysql_test)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       (ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1")       (PROGRAM=dg4odbc)    )  )  2.8 添加 Oracle TNS 配置 配置文件为 $ORACLE_HOME/network/admin/tnsnames.ora db_mysql =   (DESCRIPTION =     (ADDRESS_LIST =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)))     (CONNECT_DATA =(SID = mysql_test))     (HS = OK)   ) 2.9 重启监听 lsnrctl stop lsnrctl start 2.10 创建 Database Link SQL> create  PUBLIC DATABASE LINK msqlk connect to "root" identified by "123456" using 'db_mysql'; 2.11 访问测试 注意表名需要添加双引号。 SQL> select * from "t1"@msqlk; no rows selected SQL>  insert into "t1"@msqlk values(30); 1 row created. SQL> select * from "t1"@msqlk; id ---------- 30