해당방법은 ODBC프로그램을 이용하여 Oracle에서 Mysql로 DB Link를 생성하여 접속 후 확인할 수 있는 테스트입니다.
Source DB 와 Target DB는 동일 서버에 설치된 Oracle과 Mysql을 기준으로 작성하였습니다.
|
Source DB 192.168.56.58 Oracle 11.2.0.2 SID : XE
Target DB 192.168.56.58 Mysql 8.0.22 |
1.Oracle 서버에 ODBC 설치
root유저에서 진행
unixODBC rpm을 설치합니다. (yum을 활용하거나 rpm을 직접 다운 후 rpm -ivh 로 설치 진행)
# yum install -y unixODBC
Loaded plugins: langpacks, ulninfo
local | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================================
Installing:
unixODBC x86_64 2.3.1-11.el7 local 412 k
Transaction Summary
=================================================================================================================================================================
Install 1 Package
Total download size: 412 k
Installed size: 1.2 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
mysql-connector-odbc-8.0.23-1.el7.x86_64 has missing requires of libodbc.so.2()(64bit)
mysql-connector-odbc-8.0.23-1.el7.x86_64 has missing requires of libodbcinst.so.2()(64bit)
Installing : unixODBC-2.3.1-11.el7.x86_64 1/1
Verifying : unixODBC-2.3.1-11.el7.x86_64 1/1
Installed:
unixODBC.x86_64 0:2.3.1-11.el7
Complete!
2.확인
root유저에서 진행
명령어를 통해서 설치 확인 가능
odbcinst.int파일은 /etc/밑에 생기게 됩니다. ODBC Driver 정보가 들어 있습니다.
odbc.ini파일은 odbcinst.int정보를 가지고 와서 접속하려는 서버의 정보(DSN)가 들어 있습니다.(생성되지 않을 시 vi로 파일을 만들면 됩니다)
# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
# isql --version
unixODBC 2.3.1
드라이버 정보 확인
# cat /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[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
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
3.Mysql Server Driver 설치
root유저에서 진행
아래 사이트에서 OS 버전에 맞는 RPM 파일을 다운 받을 수 있습니다.
https://dev.mysql.com/downloads/connector/odbc/#downloads
MySQL :: Download Connector/ODBC
Select Operating System: Select Operating System… Microsoft Windows Ubuntu Linux Debian Linux SUSE Linux Enterprise Server Red Hat Enterprise Linux / Oracle Linux Fedora Linux - Generic Oracle Solaris macOS FreeBSD Source Code Select OS Version: All Wind
dev.mysql.com
# rpm -ivh mysql-connector-odbc-8.0.23-1.el7.x86_64.rpm
warning: mysql-connector-odbc-8.0.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
package mysql-connector-odbc-8.0.23-1.el7.x86_64 is already installed
4.구성
root유저에서 진행
odbcinst.ini 와 odbc.ini 파일을 수정합니다.
odbcinst.ini의 경우 파일 아래부분에 내용 추가
odbc.ini의 내용
[MYSQL] # ODBC DSN
Driver=/usr/lib64/libmyodbc8a.so #odbcinst.ini 에 명시하는 name 을 사용해도 무관
Trace=no
Server=192.168.56.58 # MySQL IP / domain name
port=3306 # MySQL Port , default port
Database=test # MySQL Database , 대소문자 구분
Threading=1
# cat /etc/odbcinst.ini
[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
# cat odbc.ini
[MYSQL]
Driver=/usr/lib64/libmyodbc8a.so
Trace=no
Server=192.168.56.58
port=3306
Database=test
Threading=1
5.unixODBC 접속 테스트
root유저에서 진행
아래 처럼 Connected!가 나오면 접속이 정상적으로 된 것입니다.
# isql -v mysql "test" "test"
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test |
+-----------------------------------------------------------------+
| EMP2 |
| EMP_TEST |
| child |
| imptest |
| professor |
| professor2 |
| student |
| test_dummy |
+-----------------------------------------------------------------+
SQLRowCount returns 8
8 rows fetched
6.Oracle HS 설정
Oracle유저에서 진행
Oracle DB Link 를 사용 할 경우 HS 설정을 합니다. 기본 initdg4odbc.ora 를 복사 하여 사용 합니다.
위치 $ORACLE_HOME/hs/admin
$ vi $ORACLE_HOME/hs/admin/initMYSQL.ora
#
HS_FDS_CONNECT_INFO = MYSQL
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini
7.Oracle 리스너에 새로운 리스너 등록
Mysql과 통신할 새로운 리스너를 등록합니다. listener.ora 아래에 추가합니다.
위치 $ORACLE_HOME/network/admin
LISTENER_MYSQL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.58)(PORT = 2000))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER_MYSQL =
(SID_LIST=
(SID_DESC=
( SID_NAME=MYSQL)
(ORACLE_HOME= /u01/app/oracle/product/11.2.0/xe)
(PROGRAM=dg4odbc)
)
)
8.새로운 리스너를 기동.
Oracle유저에서 진행
$ lsnrctl start LISTENER_MYSQL
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-APR-2021 22:25:55
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/xe//bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ysbae/listener_mysql/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysbae)(PORT=2000)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ysbae)(PORT=2000)))
STATUS of the LISTENER
------------------------
Alias LISTENER_MYSQL
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 11-APR-2021 22:25:55
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ysbae/listener_mysql/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ysbae)(PORT=2000)))
Services Summary...
Service "MYSQL" has 1 instance(s).
Instance "MYSQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$ tnsping MYSQL
9.tnsnames.ora에 MYSQL 접속 정보를 추가.
Oracle유저에서 진행
위치 $ORACLE_HOME/network/admin
MYSQL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.58)(PORT = 2000))
(CONNECT_DATA = (SID =MYSQL))
(HS = OK)
)
tnsping 테스트
$ tnsping MYSQL
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 11-APR-2021 22:27:18
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.58)(PORT = 2000)) (CONNECT_DATA = (SID =MYSQL)) (HS = OK))
OK (0 msec)
Mysql ODBC Driver 확인
$ /usr/bin/myodbc-installer -s -l -c2 n "mysql"
Name: mysql
Driver: /usr/lib64/libmyodbc8a.so
Server: 192.168.56.58
Database: test
Port: 3306
Options:
10 Oracle 접속 후 DB Link 생성
Oracle유저에서 진행
$sqlplus / as sysdba
SQL> create public database link MYSQL
connect to "test" identified by "test"
using 'MYSQL';
DB Link 테스트 - 에러 발생
SQL> select * from "professor"@mysql;
select * from "professor"@mysql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from MYSQL
해결방법
$ORACLE_HOME/hs/admin 의 initMYSQL.ora파일에 HS_LANGUAGE 파라미터 추가해주기
HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949
$ cat initMYSQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYSQL
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so
#HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8w.so
HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
11.테스트
Oracle유저에서 진행
SQL> select * from "imptest"@MYSQL;
id
----------
n
----------------------------------------------------------------------------------------------------
100
TT
101
TT
2 rows selected.
끝
Mysql -> Oracle의 경우에도 테스트를 해보려 했습니다만..
Mysql의 Federated Engine의 경우에는 Mysql만 되는 것으로 확인되네요.(공식문서에서 확인)
아래의 사이트에서 읽어보면 MySQL server가 remote 서버일 경우에만 사용된다고 나와 있습니다.
The remote server must be a MySQL server.
dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html
MySQL :: MySQL 8.0 Reference Manual :: 16.8.3 FEDERATED Storage Engine Notes and Tips
16.8.3 FEDERATED Storage Engine Notes and Tips You should be aware of the following points when using the FEDERATED storage engine: FEDERATED tables may be replicated to other replicas, but you must ensure that the replica servers are able to use the user
dev.mysql.com
MariaDB의 경우에는 10.0 버전부터 CONNECT engine이 새로 추가되었는데 이것을 사용하여 ODBC통신을 하여 타 RDBMS와 연결이 된다고 하네요
MariaDB - CONNECT
The CONNECT storage engine enables MariaDB to access external local or remote data (MED). This is done by defining tables based on different data types, in particular files in various formats, data extracted from other DBMS or products (such as Excel or MongoDB) via ODBC or JDBC, or data retrieved from the environment (for example DIR, WMI, and MAC tables)
This storage engine supports table partitioning, MariaDB virtual columns and permits defining special columns such as ROWID, FILEID, and SERVID.
No precise definition of maturity exists. Because CONNECT handles many table types, each type has a different maturity depending on whether it is old and well-tested, less well-tested or newly implemented. This will be indicated for all data types.
The remote server must be a MySQL server.
추후에 MariaDB로 테스트 해봐야 겠네요.
MariaDB - Oracle link 참고 글
[MySQL] CONNECT Storage Engine Oracle연동
CONNECT Storage Engine Oracle연동 실무중 많이 발생하는 이기종 DB간의 연결이다. 이 글에서는 MariaDB-Oracle DB 를 예로 설명한다. Oracle DB가 아닌 MSSQL , Postgre 등의 연결도 해당 DB에서 제공하는 Drive..
cirius.tistory.com
'Oracle > 이관' 카테고리의 다른 글
| EXP/IMP 데이터 이관 테스트 (case 캐릭터셋이 서로 다른 DB) (0) | 2021.11.26 |
|---|---|
| [이기종 DB Link 설정시 에러]ORA-28545: error diagnosed by Net8 when connecting to an agent (0) | 2021.11.22 |
| IMPDP 시 스키마에 이미 같은 이름의 Table이 존재한다면?(TABLE_EXISTS_ACTION) (0) | 2021.02.24 |
| Tibero to Oracle DBLINK 아키텍쳐(Oracle 리스너 방식) (0) | 2021.01.11 |
| Oracle to Tibero DB LINK 아키텍쳐(GateWay 방식) (0) | 2021.01.10 |
댓글