0. 요약
1. unixODBC
+ 각 DB의 ODBC를 이용하여 이기종 데이터베이스에 연결을 한다.
2. Heterogeneous Service라는 가상의 인스턴스를 기동한다고 생각하면 된다.
※ Oracle SID 명이 대문자 이면 $ORACLE_HOME/dbs 밑에 initSID.ora 파일이 대소문자 구분 하듯이
※ HS SID명을 대 문자로 하면 $ORACLE_HOME/hs/admin 밑에 initHSSID.ora 파일도 대 소문자를 구분한다.
※ HS SID명은 listener.ora
파일에 SID 지정부분에서 지정해 준다.
(이번 테스트에서는 PG, MYSQL, MARIA 라는 이름을 사용한다.)
3. tnsname.ora --> listener -->
$ORACLE_HOME/hs/admin/initHSSID.ora
--> unixODBC --> 대상 DB로 접근이 이루어 진다.
4. 테스트에 사용 된 서버들 정보
|
Oracle 11g |
PG 11.6 |
MySQL 8.0 |
MariaDB 10.4 |
|
192.168.56.200 |
192.168.56.110 5411 DB : test USER : tester PASS : tester |
192.168.56.100 3380 DB : test USER : tester PASS : tester |
192.168.56.101 33104 DB : test USER : tester PASS : tester |
1. 각
DB의 ODBC 파일 다운 받아서 설치 하기
UnixODBC 설치
yum install unixODBC.x86_64
unixODBC-devel.x86_64 -y
PG ODBC
yum install postgresql-odbc.x86_64 -y
설치 된 ODBC 파일 : /usr/lib64/psqlodbc.so
MySQL ODBC 8.0
cd /tmp
tar xvfz
mysql-connector-odbc-8.0.20-linux-glibc2.12-x86-64bit.tar.gz
cp /tmp/mysql-connector-odbc-8.0.20-linux-glibc2.12-x86-64bit/bin/*
/usr/local/bin
cp /tmp/mysql-connector-odbc-8.0.20-linux-glibc2.12-x86-64bit/lib/*
/usr/local/lib
myodbc-installer -a
-d -n "MySQL ODBC 8.0 Driver" -t "Driver=/usr/local/lib/libmyodbc8w.so" <-- UNICODE용
myodbc-installer -a
-d -n "MySQL ODBC 8.0" -t "Driver=/usr/local/lib/libmyodbc8a.so"
<-- ANSI용
myodbc-installer -d
-l
PostgreSQL
MySQL
MySQL ODBC 8.0 Driver
MySQL ODBC 8.0
MariaDB ODBC Cent6:
tar -xvzf
mariadb-connector-odbc-3.1.7-ga-rhel6-x86_64.tar.gz
sudo install
lib64/libmaodbc.so /usr/lib64/
설치 된 ODBC 파일 :
/usr/lib64/libmaodbc.so
MariaDB ODBC Cent7:
tar -xvzf
mariadb-connector-odbc-3.1.7-ga-rhel7-x86_64.tar.gz
sudo install
lib64/libmaodbc.so /usr/lib64/
설치 된 ODBC 파일 :
/usr/lib64/libmaodbc.so
2. unixODBC 설치 확인
unixODBC 설정 파일 위치 파악
[root@oracle lib64]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
<-- 시스템 DSN
FILE DATA SOURCES..:
/etc/ODBCDataSources
USER DATA SOURCES..:
/root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
unixODBC 파일 위치 찾기
[root@oracle lib64]# find / -name libodbc.so
/usr/lib64/libodbc.so
※ $ORACLE_HOME/hs/admin/initHSSID.ora
파일의 HS_FDS_SHAREABLE_NAME 파라미터에 입력
설치 된 odbc 드라이버 확인
[root@oracle tmp]# myodbc-installer -d
-l
PostgreSQL
MySQL
MariaDB
MySQL ODBC 8.0 Driver
MySQL ODBC 8.0
3. Oracle 리스너 설정 (HS SID 설정)
$ORACLE_HOME/network/admin/listener.ora 파일을 열어서 다음의 내용
추가 .
|
LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1511)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(ORACLE_HOME = /engine/oracle11/product/11.2.0.4/db_1)
(SID_NAME = ora11g) ) (SID_DESC =
(ORACLE_HOME = /engine/oracle11/product/11.2.0.4/db_1)
(SID_NAME = PG) (PROGRAM
= dg4odbc) ) (SID_DESC =
(ORACLE_HOME = /engine/oracle11/product/11.2.0.4/db_1)
(SID_NAME = MYSQL) (PROGRAM
= dg4odbc) ) (SID_DESC = (ORACLE_HOME
= /engine/oracle11/product/11.2.0.4/db_1)
(SID_NAME = MARIA) (PROGRAM
= dg4odbc) ) ) |
[oracle11@oracle admin]$
lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 -
Production on 29-APR-2020 22:19:30
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1511)))
The command completed successfully
[oracle11@oracle admin]$
lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 -
Production on 29-APR-2020 22:19:35
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1511)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date
29-APR-2020 21:44:35
Uptime
0 days 0 hr. 34 min. 59 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/engine/oracle11/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File
/engine/oracle11/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.200)(PORT=1511)))
Services Summary...
Service "MARIA" has 1 instance(s).
Instance
"MARIA", status UNKNOWN, has 1 handler(s)
for this service...
Service "MYSQL" has 1 instance(s).
Instance
"MYSQL", status UNKNOWN, has 1 handler(s)
for this service...
Service "PG" has 1 instance(s).
Instance
"PG", status UNKNOWN, has 1 handler(s)
for this service...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this
service...
The command completed successfully
4. HS 인스턴스 접속 용 tnsnames.ora 파일 설정
$ORACLE_HOME/network/admin/tnsnames.ora 파일을 열어서 다음의 내용
추가 .
|
ora11g = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME
= ora11g) ) ) tnspg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME
= PG) ) (HS = OK) ) tnsmy = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME
= MYSQL) ) (HS = OK) ) tnsma = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME
= MARIA) ) (HS = OK) ) |
설정 확인
|
[oracle11@oracle admin]$ tnsping tnspg ...Used TNSNAMES adapter to
resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = PG)) (HS = OK)) OK (0 msec) [oracle11@oracle admin]$ tnsping tnsmy ...Used TNSNAMES adapter to
resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = MYSQL)) (HS = OK)) OK (10 msec) [oracle11@oracle admin]$ tnsping tnsma ...Used TNSNAMES adapter to
resolve the alias Attempting to contact
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.56.200)(PORT = 1511)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = MARIA)) (HS = OK)) OK (10 msec) |
이 부분이 이상이 있으면 리스너 설정 확인
5. unixODBC 설정
/etc/odbc.ini 파일을 생성 해서 설정.
vi /etc/odbc.ini
|
[PGODBC] Driver =
/usr/lib64/psqlodbc.so Servername = 192.168.56.110 Port =
5411 Database = test Username = tester Password = tester charset = utf8 [MYODBC] Driver =
/usr/local/lib/libmyodbc8w.so Server = 192.168.56.100 Port = 3380 Database = test Uid = tester Pwd = tester charset = utf8 [MAODBC] Driver = /usr/lib64/libmaodbc.so Server = 192.168.56.101 Port = 33104 Database = test Uid = tester Pwd = tester charset = utf8 |
odbc 설정 확인 (대소문자 안 가림)
[root@oracle tmp]# isql -v pgodbc
+---------------------------------------+
| Connected!
|
|
|
| sql-statement
|
| help [tablename] |
| quit
|
|
|
+---------------------------------------+
SQL> select version()
;
+--------------------------------------------------------------------------------------------------------------+
| version
|
+--------------------------------------------------------------------------------------------------------------+
| PostgreSQL 11.6 on x86_64-pc-linux-gnu,
compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
4.8.5-39), 64-bit
|
+--------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> select * from tab1
;
+------------+-----------+
| id
| name
|
+------------+-----------+
| 1
| postgres |
| 2
| postgres |
| 3
|
postgres |
+------------+-----------+
SQLRowCount returns 3
3 rows fetched
SQL>
[root@oracle tmp]#
[root@oracle tmp]# isql -v myodbc
+---------------------------------------+
| Connected!
|
|
|
| sql-statement
|
| help [tablename] |
| quit
|
|
|
+---------------------------------------+
SQL> select version()
;
+------------------+
| version() |
+------------------+
| 8.0.19-commercial|
+------------------+
SQLRowCount returns 1
1 rows fetched
SQL> select * from tab1
;
+-----------+-----------+
| id |
name |
+-----------+-----------+
| 1
| mysql |
| 2
| mysql |
| 3
| mysql |
+-----------+-----------+
SQLRowCount returns 3
3 rows fetched
SQL>
[root@oracle tmp]#
[root@oracle tmp]#
[root@oracle tmp]# isql -v maodbc
+---------------------------------------+
| Connected!
|
|
|
| sql-statement
|
| help [tablename] |
| quit
|
|
|
+---------------------------------------+
SQL> select version()
;
+----------------+
| version() |
+----------------+
| 10.4.12-MariaDB|
+----------------+
SQLRowCount returns 1
1 rows fetched
SQL> select * from tab1
;
+------------+-----------+
| id
| name
|
+------------+-----------+
| 1
| mariadb |
| 2
| mariadb |
| 3
| mariadb |
+------------+-----------+
SQLRowCount returns 3
3 rows fetched
이 부분이 이상 있으면, ODBC 드라이버 버전 확인 후 최신 버전으로 변경, 또는 odbc.ini 파일 설정 확인.
6. HS 파라미터 설정
$ORACLE_HOME/hs/admin
밑에
initdg4odbc.ora 파일을 복사해서 사용한다.
listener.ora 파일에 HS SID를 대문자로 설정해 주었으므로 여기서도 대문자로 HS SID를 써 준다.
|
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
(unixODBC 드라이버 지정) HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8 (Oracle Server 캐릭터 셋 지정) SQL> SELECT (SELECT VALUE FROM nls_database_parameters
WHERE PARAMETER = 'NLS_LANGUAGE') || '_' || (SELECT VALUE FROM nls_database_parameters
WHERE PARAMETER = 'NLS_TERRITORY') || '.' || (SELECT VALUE FROM nls_database_parameters
WHERE PARAMETER = 'NLS_CHARACTERSET') as characterset FROM DUAL ; CHARACTERSET ------------------------------------------------------------------------------------------------------- AMERICAN_AMERICA.AL32UTF8 |
[oracle11@oracle admin]$
cp initdg4odbc.ora initPG.ora
[oracle11@oracle admin]$
cp initdg4odbc.ora initMYSQL.ora
[oracle11@oracle admin]$
cp initdg4odbc.ora initMARIA.ora
|
initPG.ora |
HS_FDS_CONNECT_INFO
= PGODBC HS_FDS_SHAREABLE_NAME
= /usr/lib64/libodbc.so HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1 # 이 부분을 AMERICAN_AMERICA.AL32UTF8 로 하면 동작 안 함. (PostgreSQL만 특이 함) HS_FDS_TRACE_LEVEL = 255 HS_FDS_TIMESTAMP_MAPPING=DATE HS_NLS_NCHAR=UCS2 HS_FDS_SUPPORT_STATISTICS =
FALSE HS_RPC_FETCH_REBLOCKING =
OFF HS_KEEP_REMOTE_COLUMN_SIZE =
ALL HS_NLS_LENGTH_SEMANTICS =
VARCHAR set ODBCINI
= /etc/odbc.ini |
|
initMYSQL.ora |
HS_FDS_CONNECT_INFO
= MYODBC HS_FDS_SHAREABLE_NAME
= /usr/lib64/libodbc.so HS_LANGUAGE =
AMERICAN_AMERICA.AL32UTF8 HS_FDS_TRACE_LEVEL = 255 HS_FDS_TIMESTAMP_MAPPING=DATE HS_NLS_NCHAR=UCS2 HS_FDS_SUPPORT_STATISTICS =
FALSE HS_RPC_FETCH_REBLOCKING =
OFF HS_KEEP_REMOTE_COLUMN_SIZE =
ALL HS_NLS_LENGTH_SEMANTICS =
VARCHAR set ODBCINI = /etc/odbc.ini |
|
initMARIA.ora |
HS_FDS_CONNECT_INFO
= MAODBC HS_FDS_SHAREABLE_NAME
= /usr/lib64/libodbc.so HS_LANGUAGE =
AMERICAN_AMERICA.AL32UTF8 HS_FDS_TRACE_LEVEL = 255 HS_FDS_TIMESTAMP_MAPPING=DATE HS_NLS_NCHAR=UCS2 HS_FDS_SUPPORT_STATISTICS =
FALSE HS_RPC_FETCH_REBLOCKING =
OFF HS_KEEP_REMOTE_COLUMN_SIZE =
ALL HS_NLS_LENGTH_SEMANTICS =
VARCHAR set ODBCINI = /etc/odbc.ini |
7. Oracle DB Link 생성
|
PG |
SQL> create public
database link linkpg connect to "tester"
identified by "tester" using 'tnspg' ; SQL> select * from
"tab1"@linkpg ; id name ----------
------------------------------ 1 postgres 2 postgres 3 postgres SQL> insert into
"tab1"@linkpg values (4 , 'oracle') ; SQL> commit
; SQL> select * from
"tab1"@linkpg ; id name ---------- ---------- 1 postgres 2 postgres 3 postgres 4 oracle |
|
MYSQL |
SQL> create public
database link linkmy connect to "tester"
identified by "tester" using 'tnsmy' ; SQL> select * from
"tab1"@linkmy ; id name ----------
-------------------- 1 mysql 2 mysql 3 mysql SQL> insert into
"tab1"@linkmy values (4,'oracle') ; SQL> commit
; SQL> select * from
"tab1"@linkmy ; id name ---------- -------------------- 1 mysql 2 mysql 3 mysql 4 oracle |
|
MARIA |
SQL> create public
database link linkma connect to "tester"
identified by "tester" using 'tnsma' ; SQL> select * from
"tab1"@linkma ; id name ----------
-------------------- 1 mariadb 2 mariadb 3 mariadb SQL> insert into
"tab1"@linkma values (4,'oracle') ; SQL> commit
; SQL> select * from
"tab1"@linkma ; id name ----------
-------------------- 1 mariadb 2 mariadb 3 mariadb 4 oracle |