preface

The Mysql database cannot be accessed directly from Oracle. It needs to be accessed through DBLink after configuring a transparent gateway.

This afternoon, I made a transparent gateway configuration. The process is complex and there are some pits. I won't arrange the pits for you immediately. Maybe I can use them in the future!

Don't talk too much nonsense. Go straight to actual combat~

1, Environmental preparation

Three hosts are prepared for this environment, namely Oracle database, MySQL database and transparent gateway.

• ⭐ Linux host installation tutorial can refer to: Practical part: installing Linux system on VMware Workstation virtual machine
• ⭐ Oracle stand-alone installation tutorial can refer to: Practical part: Nanny level tutorial for installing Oracle 11GR2 database on Linux
• ⭐ For Oracle RAC installation tutorial, please refer to: Practical part: teach you how to install Oracle 11GR2 RAC for Linux 7 step by step
• ⭐ For Oracle script one click installation, please refer to: Open source project: Install Oracle Database By Scripts!
• ⭐ For MySQL installation tutorial, please refer to: Practical part: teach you how to install Mysql on Linux (detailed)

❤️ Oracle installation package collection and patch download address: Oracle Q3 2021 Patch Collection

Environmental information:

Why does the transparent gateway need to be configured with a separate host?

• It does not affect the normal operation of the database host
• Easy to install, configure and manage
• The transparent gateway does not need to be reconfigured during database migration
• In RAC environment, multiple nodes do not need to be installed and configured repeatedly

Note: the configuration of transparent gateway host does not need to be very high. There is no need to install Oracle database. Only install transparent gateway!

2, Actual combat demonstration

1. Media equipment

First, you need to download the GateWay and MySQL ODBC installation packages:

• mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm
• p13390677_112040_Linux-x86-64_5of7.zip

For your convenience, I will upload it directly to Baidu online disk for you to download:

link: https://pan.baidu.com/s/1ybJGu1JCbOgRbjCTqtl9TA 
Extraction code: l062

2. Install transparent gateway

2.1. Host environment configuration

Download the above installation media and upload it to the transparent gateway host:

Note: before installing the transparent gateway, you need to configure the environment and create users. You can refer to the environment configuration steps of installing stand-alone Oracle on Linux, which will not be detailed here!

Configure with Oracle one click installation script:

cd /soft
./OracleShellInstall.sh -i 10.211.55.102 -txh Y

Note: since minimum installation is selected during host installation, and the installation of transparent gateway needs to be graphical, add -txh Y installation graphical interface!

After a few minutes, the configuration can be completed. Since the DB installation package is not uploaded, this error is normal!

2.2 formal installation

After the host configuration is completed, start to install the transparent gateway:

## First, under the root user, authorize oracle to access the / soft directory
chown -R oracle:oinstall /soft
## Switch to oracle user
su - oracle
## Unzip the transparent gateway installation package
unzip -q p13390677_112040_Linux-x86-64_5of7.zip
## Configure vnc access graphical interface
vncserver

Connect using VNC client:

Start installation:

Confirm installation position:

Select for ODBC here:

Start the installation and wait for the end:

Execute root under root user SH script:

/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/db/root.sh

Configure listening:

At this point, the transparent gateway installation is completed!

2.3. Install MySQL connector

First, install the unixODBC dependency. Otherwise, an error is reported:

Install MySQL connector after installing unixODBC dependency:

yum install -y unixODBC*
rpm -ivh mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm

Note: the installation of the transparent gateway is over here, and the next step is the configuration part.

3. Transparent gateway configuration

After the transparent gateway is installed, it naturally needs to be configured before it can be used.

3.1. Configure ODBC ini

odbc. The. INI file is used to store MySQL database related information. It does not exist by default. You can manually create it under root:

cat<<EOF>/etc/odbc.ini
[lucifer]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.101
Port            = 3306
User            = lucifer
Password        = lucifer
Database        = lucifer 
EOF

The configuration information in the configuration file is as follows:

• [lucifer] can be understood as a SID name, which can be customized. It needs to be used for later configuration;
• Server refers to the IP address of MySQL database host;
• Port refers to the port of MySQL database;
• User refers to the user name that Oracle needs to access;
• Password refers to the login password of the accessed user;
• Database refers to the DB name that MySQL database needs to be accessed;

Note: if multiple MySQL databases need to be configured, you can fill in multiple databases!

[lucifer]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.101
Port            = 3306
User            = lucifer
Password        = lucifer
Database        = lucifer 

[lucifer1]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 10.211.55.105
Port            = 3306
User            = lucifer1
Password        = lucifer1
Database        = lucifer1

3.2. Configure init [Sid] ora

Switch to Oracle user and enter $oracle_ Create a new init [Sid] in the home / HS / Admin directory Ora file:

cat<<EOF>$ORACLE_HOME/hs/admin/initlucifer.ora
##HS Configuration
HS_FDS_CONNECT_INFO = lucifer
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
##ODBC Configuration
set ODBCINI=/etc/odbc.ini
EOF

Here is initsid SID name in ora and HS in file_ FDS_ CONNECT_ The info parameter value is the value of ODBC we configured in the previous step [lucifer] name in INI file.

Note: if multiple MySQL databases need to be configured, create multiple init [Sid] Ora file is OK!

3.3. Configure monitoring

Under oracle user, enter $TNS_ADMIN configure listener Ora file:

cat<<EOF>>$TNS_ADMIN/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC=
  (SID_NAME=lucifer)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
)
EOF

Note: if multiple MySQL databases need to be configured, write them in the following format!

cat<<EOF>>$TNS_ADMIN/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (SID_NAME = lucifer)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
  (SID_DESC =
  (SID_NAME = lucifer1)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
  (PROGRAM=dg4odbc)
  )
)
EOF

Restart listening:

lsnrctl stop
lsnrctl start
lsnrctl status

After the configuration is completed, test whether you can connect to the MySQL database:

isql lucifer

The MySQL database is successfully connected, and the transparent gateway configuration is completed!

4. Oracle database configuration

After the transparent gateway is configured, you only need to configure the Oracle database to connect to the transparent gateway.

4.1. Configure TNS

Connect the transparent gateway by configuring TNS and enter the TNS configuration file directory $TNS_ADMIN configure TNS:

cat<<EOF>>$TNS_ADMIN/tnsnames.ora
lucifer =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = lucifer)
    )
   (HS = OK)
  )
EOF

After passing the tnspin test, it is found that there is no problem with access!

4.2. Configure DBLink to connect to MySQL database

Next, just create your DBLink to connect to the MySQL database:

create public database link lucifer connect to "lucifer" identified by "lucifer" using 'lucifer';

Test connection operation MySQL database:

select * from "lucifer"@lucifer order by "id";
delete from "lucifer"@lucifer where "id"=1;

So far, the whole Oracle configuration transparent gateway to access MySQL is completed!

3, Final summary

If you follow my steps, the whole process should go smoothly. Here are some error reports I encountered during my operation:

1,init[sid]. HS of ora profile_ FDS_ SHAREABLE_ NAME = /usr/Lib64/libodbc. So is incorrect. It should be an ODBC Lib package:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DLK

2,init[sid]. HS configured in ora_ LANGUAGE=AMERICAN_ AMERICA. ZHS16GBK character set is incorrect or not configured. It should be Oracle database character set:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[

3. When performing query operations, the table name needs to be enclosed in double quotation marks, because MySQL's default table name is case sensitive, while Oracle is case insensitive:

ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table 'test.T1' doesn't exist
{42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from DLK

4. When configuring TNS for Oracle database, it does not add (HS = OK):

ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-02063: preceding line from LUCIFER

Some contents of this article refer to the following documents: Oracle transparent gateway to access MySQL database