728x90
반응형

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

wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.20-linux-glibc2.12-x86-64bit.tar.gz

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:

wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.7/mariadb-connector-odbc-3.1.7-ga-rhel6-x86_64.tar.gz

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:

wget https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.7/mariadb-connector-odbc-3.1.7-ga-rhel7-x86_64.tar.gz

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

 

728x90
반응형
  1. 익명 2022.01.25 15:40

    비밀댓글입니다

    • ★깜장별★ 2022.02.05 21:28 신고

      이제야 질문을 봤습니다. --;

      오라클 서버에 Unix ODBC랑 연결하고자 하시는 MySQL ODBC 가 같이 설치 되어 있어야 합니다.

      리스너에서는 Unix ODBC를 참조 하고,
      UnixODBC의 설정에서 MySQL서버에 대한 설정을 하게 되어 있습니다.

  2. ★깜장별★ 2022.04.28 15:48 신고

    오라클이 win949 마리아가 utf8 일때
    HS_LANGUAGE = AMERICAN_AMERICA.KO16MSWIN949로 하면 마리아 조회시에 ORA-29275: partial multibyte character 에러가 난다고 합니다.

    HS_LANGUAGE = AMERICAN_AMERICA.UTF8 로 변경하면 정상 동작한다고 합니다. 참고 하세요 ~

+ Recent posts