1. 2018.07.30 오라클에서 ODBC를 이용한 MySQL으로 DB LINK 설정 2
  2. 2014.06.09 DB Link

오라클에서 ODBC를 이용한 MySQL으로 DB LINK 설정 2

오라클에서 ODBC를 이용한 MySQL으로 DB LINK 설정 2


MySQL 서버에 Gateway를 설치하지 않고 ODBC 드라이버만 이용하는 경우



테스트 환경



오라클 DB 서버


Oracle Linux 6.9

Oracle 11gR2 - 11.2.0.4

ODBC Driver 8.0.11 (홈페이지에서 받아서 설치, https://dev.mysql.com/downloads/connector/odbc/)

캐릭터셋 - KO16MSWIN949

ip - 192.168.0.55



MySQL DB 서버


CentOS 7.2

MySQL 5.7 (yum 설치)

캐릭터셋 - euckr

ip - 192.168.0.44

Database - MYSQL



오라클 서버에서 설정



버전에 맞는 ODBC 드라이버를 받아서 yum으로 설치하면 /etc/odbc.ini, /etc/odbcinst.ini 파일이 보인다.


vi로 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



이 것이 MySQL 연결에 사용될 드라이버 인데, 주의할점은 각 DB의 캐릭터 셋에 맞춰 드라이버를 설정해줘야 한다.

UTF8를 사용할 것이라면 Unicode 드라이버를, EUCKR을 사용할 것 이라면 ANSI를 선택해야 한다.



# vi /etc/odbc.ini


[MYSQL]

Driver = /usr/lib64/libmyodbc8a.so

trace = off

port = 3306

server = 192.168.0.44

database = MYSQL

option = 2

user = DEMO

password = PASSWORD

charset = euckr



$ vi tnsnames.ora


MYSQL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.55)(PORT = 1521))

      (CONNECT_DATA = (SID = MYSQL))

      (HS = OK)

  )


$ isql -v MYSQL 

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>


이렇게 나오면 일단 MySQL DB로 원격접속이 되는것이다.


※ 사전에 MySQL 서버의 방화벽을 내린다거나, MySQL 계정에 원격접속 가능한 권한을 줘야 한다.


오라클 자체 적으로 HS 파라미터를 설정해야 한다.


$ cd $ORACLE_HOME/hs/admin

$ vi initMYSQL.ora (initXXXX.ora)    <-- [아래 HS_FDS_CONNECT_INFO = XXXX 의 XXXX 부분과 동일 하게 설정]


# 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      <-- [해당부분은 접속 하고자하는 MySQL DB 명 입력]

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so      <-- [사용하고자 하는 ODBC 드라이버]

HS_FDS_TRACE_LEVEL = OFF                <--[접속은 되는데 결과값이 안나온다면 255 로 설정해 trace 확인]

HS_FDS_SUPPORT_STATISTICS = FALSE

HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949     <--[Oracle 서버쪽 DB의 캐릭터셋]

HS_RPC_FETCH_REBLOCKING = OFF

HS_KEEP_REMOTE_COLUMN_SIZE = ALL

HS_NLS_LENGTH_SEMANTICS = VARCHAR                  <-- [VARCHAR or CHAR 등으로 환경에 맞게 설정 가]

HS_FDS_TIMESTAMP_MAPPING = DATE

HS_NLS_NCHAR=UCS2


#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini


#

# Environment variables required for the non-Oracle system

#


오라클 리스너에 HS 리스너를 추가해준다.

$ vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: oracle/product/11.2.0/tg_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME=MYSQL)

     (ORACLE_HOME=/oracle/product/11.2.0/db_1)

     (PROGRAM=/oracle/product/11.2.0/db_1/bin/dg4odbc)

     (ENVS=LD_LIBRARY_PATH = /usr/lib:/usr/lib64:/oracle/product/11.2.0/db_1/lib:/oracle/product/11.2.0/db_1/hs/lib)

   )

 )

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))

     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

   )

 )


ADR_BASE_LISTENER = /oracle/product/11.2.0/db_1


$ lsnrctl start


LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-JUL-2018 11:35:58


Copyright (c) 1991, 2013, Oracle.  All rights reserved.


Starting /oracle/product/11.2.0/tg_1/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /oracle/product/11.2.0/tg_1/network/admin/listener.ora

Log messages written to /oracle/product/11.2.0/tg_1/diag/tnslsnr/MYSQL/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1192.168.0.44)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.44)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                27-JUL-2018 11:35:58

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/product/11.2.0/tg_1/network/admin/listener.ora

Listener Log File         /oracle/product/11.2.0/tg_1/diag/tnslsnr/MYSQL/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.44)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "MYSQL" has 1 instance(s).

  Instance "MYSQL", status UNKNOWN, has 1 handler(s) for this service...

Service "ora11g" has 1 instance(s).

  Instance "ora11g", status READY, has 1 handler(s) for this service...

Service "ora11gXDB" has 1 instance(s).

  Instance "ora11g", status READY, has 1 handler(s) for this service...

The command completed successfully



오라클 서버에서 tnsping을 실행 했을때



$ tnsping mysql


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-JUL-2018 11:19:40

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias


Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.55)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS = OK))

OK (0 msec)


나오면 접속 준비가 완료



ORACLE DB의 SCOTT 계정에서 DB LINK TEST


DB LINK 생성 


※ DB Link를 생성하고자하는 계정에 create database link 권한을 주고 작업한다.


$ sqlplus scott/tiger



SQL> create database link mysql

connect to DEMO

IDENTIFIED BY "<password>"

USING 'MYSQL';


MySQL DB에 미리 만들어 놓은 DEMO 테이블을 조회 해본다.


※ 테스트용 계정과 테이블은 알아서 만드시길... DEMO 유저의 DEMO 테이블을 조회 했다.


SQL> select * from "demo"@mysql_test;


      col1 col2       col3 col4     col5

---------- ---------- ---------- ---------- --------------------

1 24-JUL-18  0123456789 0123456789 30313233343536373839

2 25-JUL-18  0123456788 0123456788 30313233343536373838


결과 값이 나오면 성공

DB Link

DB Link 란?


데이터베이스 링크는 클라이언트 또는 현재의 데이터베이스에서 네트워크상의 다른 데이터베이스에 접속하기 위한 접속 설정을 정의하는 오라클 객체 이다.

 - 우선 고려되어야 사항은 ORACLE INSTANCE가 두 개 이상이고, 각각의 HOST NAME과 ORACLE_SID는 다르고 NLS_CHARACTER_SET은 동일하게 되어 있어야 한다.
 - 만약 같은 MECHINE에서 INSTANCE의 ORACLE_SID가 같다면 TNS ERROR가 발생한다.
 - 미래를 위해 다른 MECHINE이라 할지라도 ORACLE_SID는 규칙에 의해 다르게 가져가는 것이 좋다.
 - NLS_CHARACTER_SET이 동일하게 되어있지 않으면 DATA 입출력시 ?????로 나타난다.
 - 데이터베이스 링크로 연결되는 서버에 Listener가 꼭 구동 되어있어야 한다.



1. 작업전 확인 사항


  SERVER HOST NAME : LOCAL - REMOTE
  ORACLE SID: ORA_LOCAL - ORA_REMOTE
  예로 서버의 세팅이 위와 같이 되어 있고 LOCAL에서 REMOTE의 DB LINK 생성을 한다고 가정하고 생성 예를 들어 보겠다.


 1) 두 DBMS 간 character set이 같아야 된다.

  ※ character set 확인 방법

SQL> select * from v$nls_parameters
where PARAMETER = 'NLS_CHARACTERSET';


2) 오라클 Listener 기동 확인.
  원격 DB의 오라클 Listener 가 기동되어 있어야 한다.


2. 1번이 다 되어 있다면 tnsnames.ora 에 Service Name 등록.

 로컬 DB의 $ORACLE_HOME/network/admin/tnsnames.ora 을 수정해서 링크하고자 하는 오라클 서버를 등록한다.

 예제>

  ORA_REMOTE =
      (DESCRIPTION_LIST =
          (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xxx)(PORT = 1521))
              (CONNECT_DATA =
                  (SERVICE_NAME = ORA_REMOTE)
              )
          )
      )


위와같이 링크할 DB의 SID를 등록한다.

 ※ DB 접속 정보가 올바르게 입력되었는지 확인 :


  tnsping 명령으로 확인할 수 있다. (hosts 파일에 hostname과 ip가 등록이 되어 있다면)

   shell> tnsping REMOTE


  tnsnames.ora에 서비스명으로 접속 테스트

   shell> sqlplus 사용계정/사용패스워드@SID로 접속이 된다면 SID등록 완료.


3. DB 링크 생성
 
   1) 먼저 SQLPlus로 계정/패스워드(ORA_LOCAL계정)로 Login한다.
   2) create로 db 링크를 생성한다.


 ※ DB Link 설정 명령


CREATE [PUBLIC] DATABASE LINK [DB LINK 이름]
CONNECT TO [유저명]
IDENTIFIED BY [비밀번호]
USING ['서비스 이름'];


 ※ PUBLIC - 오라클 시노님과 마찬가지로 PUBLIC 옵션을 사용하면 공용 데이터베이스 링크를 생성 할 수 있다.

                   PUBLIC 옵션을 사용하지 않으면 링크를 생성한 자신만 사용 할 수 있다.


 ※ DB Link 설정하기 위해서는 권한이 필요. 만약 권한이 없다면 SYS 계정으로 접속해서 DB Link를 사용할 계정에 권한 설정을 해준다.
   

  GRANT CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK TO [user_id];


예제>

SQL> create public database link LK_REMOTE
connect to scott
identified by tiger
using 'ORA_REMOTE';

※ SQL*NET이 V1이면 아래 방법을 사용.
SQL> create public database link LK_REMOTE
connect to scott 
identified by tiger
using 't:REMOTE:ORA_REMOTE';


4. DB Link의 사용


원격 DB에 scott 계정에 test라는 Table이 존재 한다는 가정하에 다음 명령을 로컬에서 입력

SQL> select * from test@ORA_REMOTE;

결과가 나오면 DB링크가 성공적으로 된것이다.
 

5. DB Link 삭제


DROP DATABASE LINK [DB LINK 이름]


예제>

SQL> drop database link LK_REMOTE;



6. 간편하게 사용하기

tablename@SID 를 식별자로 사용하면 보기도 지저분하고 불편하다.
아래와 같이 view나 synonym을 생성해서 사용하면 좀더 편하게 사용할수 있다.

 1) View 생성   

SQL> create view test_view
as select * from test@ORA_REMOTE;

 2) Synonym 생성

SQL> create synonym test for test@ORA_REMOTE;
SQL> select * from test;


7. tnsnames.ora 파일에 등록 없이 사용하기


 tnsnames.ora 에 원격지 'ORA_REMOTE' DB 정보 추가 없이

 아래와 같이 CREATE DATABASE LINK 명령에 직접 기술함으로 추가할 수도 있다.

예제>

CREATE DATABASE LINK LK_REMOTE
CONNECT TO SCOTT
IDENTIFIED BY TIGER
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xxx)(PORT = 1521))
(CONNECT_DATA =
(SID = ORA_REMOTE))))';



'ORACLE > Operation & Admin' 카테고리의 다른 글

Redo Log 관리하기  (0) 2014.06.12
Control file 관리하기  (0) 2014.06.11
DB Link  (0) 2014.06.09
SGA, PGA 설정 하는 방법  (1) 2014.04.09
audit 설정 없이 DB 접속 로그 남기기  (0) 2014.03.24
SQL Prompt 변경  (0) 2014.03.10