CONNECT Storage Engine Oracle연동
실무중 많이 발생하는 이기종 DB간의 연결이다.
이 글에서는 MariaDB-Oracle DB 를 예로 설명한다.
Oracle DB가 아닌 MSSQL , Postgre 등의 연결도 해당 DB에서 제공하는 Driver 들을 설치 설정하면 크게 다르지 않다.
예를 들어
가입자(회원) 정보는 Oracle DB에 있고 실제 단위 서비스에 대해서는 MariaDB or MySQL를 사용하는 경우
회원 정보 1,2명의 데이터를 처리가 아니라 수백 수천만명의 데이터를 처리 해야 하는 경우...
이럴때..
MariaDB(MySQL)에서 제공하는 CONNECT Storage Engine을 통해서
MariaDB(MySQL) --> Oracle DB로 직접 억세스 해보자.
Connect engine 구성 방법 및 순서
- 연결하고자 하는 대상 DB의 ODBC Driver 설치
- ODBC 설치및 설정
- 연결테스트(ODBC-Oracle)
- MariaDB(MySQL) Connected Engine Plugin 설치 및 설정
- 연동 테스트(MariaDB or MySQL - Oracle)
1. Oracle DB ODBC Driver 설치 (Oracle client 설치)
Oracle db ODBC Driver 설치를위해 OracleClient 를 설치 한다.
Oracle Client(http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html) 에서 OS 및 적절한 버전의 client를 다운받는다
요즘은 이런 드라이버 하나 받으려는데 로그인이 필요하다. 불편해 죽겠다...
rpm -ivh oracleclient/oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracleclient/oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
rpm -ivh oracleclient/oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm
단순하게 rpm Package를 받아 설치 한다. (우리가 필요한것은 basic, odbc 이지만 혹시 모르니 모두 설치 한다.)
참고로 zip 파일로 내려 받아 압축을 풀어 사용 할 수도 있다.
기본 설치시 Oracle client 는 /usr/lib/oracle/{version}/client64/lib/ 에 설치된다.
현재 최신 버전은 12.1 이다. 테스트 한 바로는 12.1버전을 사용시, 11 버전과 호환에 큰 문제는 없었다.
일단, 설치가 되었다면 오라클 클라이언트 Path 를 환경 변수에 잡아주자
$ vi ~/.bash_profile
$ export ORACLE_HOME=/usr/lib/oracle/12.1/client64
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
// 저장후 이를 적용 하기 위하여
$ source ~/.bash_profile
//환경변수 확인
$ env
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/:/usr/lib/oracle/12.1/client64/lib/:
2. ODBC 설치및 설정(연결테스트)
위의 그림에도 나와 있듯이 MariaDB(MySQL)에서 제공하는 CONNECT Storage Engine 은 ODBC를 사용 하므로 Odbc를 설치 한다. (ubuntu 는 당연히 apt-get install)
[root@TESTDB ~] yum install unixODBC
[root@TESTDB ~] yum install unixODBC-devel
[수동 설치]
unixODBC 소스를 내려 받아 압축을 풀고 compile 하여 설치
$ ./configure
$ make
$ make install
설치시 기본 설치 경로는 /usr/local 이며 별도의 설치 경로를 주고자 하는 경우 컴파일시 다음 조건을 준다
예) ./configure --prefix=/usr/local/unixODBC
ODBC설정 파일(odbc.ini, odbcinst.ini)의 경로는 {prefix}/etc 에 존재 하며,
이를 변경 하고자 하는 경우 --sysconfdir 옵션을 이용 할 수 있다.
예) ./configure --sysconfdir=/etc
odbc 설정
odbc설정은 2개의 파일을 편집하여 설정 합니다.(odbc.ini, odbcinst.ini)
yum or apt-get을 이용하여 설치 한 경우 odbc.ini, odbcinst.ini 파일은 /etc 디렉토리에 있습니다.
[odbcinst.ini편집]
$ sudo vi /etc/odbcinst.ini
[Oracle]
Description = Oracle ODBC driver for Oracle
Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Driver = oracle odbc client driver path
Driver = 오라클 client가 인스톨된 위치의 정확한 파일명을 주어야 합니다.
[odbc.ini편집]
$ sudo vi /etc/odbc.ini
[Oracle]
Driver = Oracle-12.1
ServerName = //192.168.0.2:1521/ORCL
UserName = system
Password = manager
Trace = yes
TraceFile = /tmp/odbc_oracle.log
Driver = Oracle (odbcinst.ini 에서 동일한 이름 사용)
ServerName = //192.168.0.2:1521/ORCL
serverName 은 oracle DB 의 정보를 정확하게 기입 합니다.
3. 연결테스트(ODBC-Oracle)
odbc 에서 제공 하는 isql툴을 이용해서 접속을 확인 합니다.
$ isql Oracle
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
SQL> select * from emp;
+-------+-----------+----------+-------+--------------------+----------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL
+-------+-----------+----------+-------+--------------------+----------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800
…
+-------+-----------+----------+-------+--------------------+----------
SQLRowCount returns -1
15 rows fetched
SQL>
테이블 및 데이터 테스트 쿼리 사용 작동 확인 중 에러가 발생 하는 경우 isql –v Oracle 으로 에러메세지를 출력 하여 확인
4. MariaDB(MySQL) Connected Engine Plugin 설치 및 설정
mariadb connected engine 설치 및 활성화 Connected Engine은 mariaDB에서 plug in 형태로 제공되며, 버전 10.2 이상 상위 버전에서 지원 합니다. (10.0 이하 버전은 지원이 중단) root database 접속 후 플러그인 되어 있는 엔진 중 connect engine이 설치 되어 있고 활성화 되어 있는지 확인
$ mysql -uroot -p
MariaDB [(none)]>show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |
CONNECT Engine plug-in이 설치 되어 있는지 확인한다.
CONNECT Engine plug-in이 설치 되어 있지 않을 경우 connect-engine을 설치 합니다.
//connect-engine 인스톨 후, 엔진 추가 및 확인
$ yum install mariadb-connect-engine
$ mysql -uroot -p
MariaDB [(none)]> INSTALL SONAME 'ha_connect';
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | NULL | NULL | NULL |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | NO | Archive storage engine | NULL | NULL | NULL |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.01 sec)
7. 연동 테스트(MariaDB or MySQL - Oracle)
MariaDB [testdb]> CREATE TABLE emp_oracle ENGINE=CONNECT CONNECTION='DSN=ORCL;UID=system;PWD=manager' `TABLE_TYPE`=ODBC `tabname`='emp' ;
Query OK, 0 rows affected (0.04 sec)
MariaDB [testdb]> select * from emp_oracle;
+-------+-----------+----------+-------+--------------------+----------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL
+-------+-----------+----------+-------+--------------------+----------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800
…
+-------+-----------+----------+-------+--------------------+----------
15 rows in set (0.11 sec)
MariaDB [testdb]>
위 처럼 oracle DB의 emp Table을 직접 조회 할수 있다. 접속 유저의 권한이 있다면 Insert, Update 역시 가능하다.
한계점은 당욘히 존재 한다.
Blob/Text 등의 data Type은 지원하지 못한다.
또한, Oracle, MySQL등에서 지원하는 LinkDB , LinkedServer처럼 한번 연결로 모든 테이블 억세스는 되지 않는다. 테이블당 링크 테이블을 생성해 이용해야 한다. (이 부분은 필요한 Oracle에 view를 생성하고 이 View Table를 이용하는 편법을 이용할 수 있다)
[참고]
테스트를 하면서 LD_LIBRARY 문제가 발생했다. 이유는 MariaDB(MySQL) 의 구동이 MySQL Damon에 의해서 실행되는데 MySQL User에 LD_LIBRARY Path 설정이 되지 않아서 발생하는 오류 였다.
이럴경우 아래와 같이 LD_LIBRARY 를 추가 할 수 있다.
[Oracle library 추가 방법]
/etc/ld.so.conf.d/ 디렉토리에 파일(*.conf)을 만들어 추가
cd /etc/ld.so.conf.d/
vi oracle.conf
/usr/lib/oracle/12.2/client64/lib
프로젝트 중
고객에게 문자 발송 데이터를 만들어 문자 발송 시스템으로 데이터를 넣어 주어야 하는 프로젝트가 있었다,
고객 정보는 Oracle DB에 있었고,
문자 발송 대상 고객리스트는 엑셀로 전달되었다.
여러가지 문제로 해당 시스템은 MariaDB로 구성되어 있어서 엑셀로 전달되는 대상 고객 정보를 고객 DB(Oracle)에서 얻어와야 하는데 한번에 전달되는 엑셀의 고객수가 100만건이 넘었다.
고객정보 소스는 엑셀이고, 고객 ID를 이용해서 필요한 고객정보(Oracle DB)를 얻어와야 하는데 100만건이 넘는 데이터를 프로그램에서 loop 를 돌면서 처리 하기에는 속도의 문제가 있었으나 Connected engine을 이용해 query 한번으로 처리 할수 있었다...
'IT 관련 > Database' 카테고리의 다른 글
| [MySQL] Join을 이용한 Update, Delete (0) | 2017.12.29 |
|---|---|
| [MySQL]트랜잭션(transaction) 처리#1 (0) | 2017.12.28 |
| [MySQL] 트랜잭션(transaction) 처리 (0) | 2017.12.28 |
| Primary Key vs Unique Index (0) | 2017.12.27 |
| [MySQL] 요일 구하기 함수 weekday() 주의 (0) | 2017.12.25 |
| [MySQL] csv 파일을 직접 테이블로 Import (0) | 2017.12.25 |
| [Data 모델링] 엔티티(Entity), 애트리뷰(Attribute) (0) | 2017.12.18 |
| [MySQL] last_insert_id() 사용시 주의 (8) | 2017.12.17 |
댓글