1 Gateway 개요
n 이기종 Database 간의 통신을 위해서 오라클에서 제공하는 제품이다.
n 이기종 시스템 간의 SQL 변환, 데이터의 원활한 상호 운영성을 제공한다
n Oracle 9i 버전부터 CD에 포함되어 제공되고 있으며 Gateway 서비스를 제공 받으려면 별도의 라이선스를 구매해야 한다
2 Gateway 원리
- 이기종간 서비스(Heterogeneous Connectivity)에 의해 Oracle SQL 을 Oracle 이외의 시스템의 적절한 언어로 변환하는 기능과 Oracle 이외의 시스템의 메타데이타를 로컬 서식에서 나타내는데이터·딕셔너리 변환의 2 종류의 변환이 제공된다. 변환을 사용할 수 없는 경우에는 pass-through 기능을 사용해 Oracle 이외의 시스템에 시스템 고유의 SQL문을 실행할 수 있다.
![clip_image001[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0014.gif "clip_image001[4]"){kind=small}
![clip_image003[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0034.jpg "clip_image003[4]")
3 T
1 TEST 목적
- Oracle Gateway 제품을 이용하여 이기종 DB를 연동시킨다
- Gateway로 연결된 상호 DB간에 데이터 조회(select )
- Gateway로 연결된 상호 DB간에 데이터 이행 (migration)
- 본 문서에는 Oracle – DB2 , Oracle – Sybase 두가지 케이스를 기술하였다
2 TEST 환경구성
2.1 Oracle DB 정보
n VMware Workstation 8.0
n O/S : OEL 5 64bit (Mem 2GB)
n Host Name : park32 (192.168.1.14)
| SQL> select * from v$version;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production |
2.2 DB2 DB 정보
n VMware Workstation 8.0
n O/S : OEL 5 64bit (Mem 2GB)
n Host Name : unione (192.168.1.9)
| [db2inst1@unione ~]$ db2level
DB21085I This instance or install (instance name, where applicable: “db2inst1”) uses “64” bits and DB2 code release “SQL10012” with level identifier “0203010E”. Informational tokens are “DB2 v10.1.0.2”, “s121127”, “IP23394”, and Fix Pack “2”. Product is installed at “/opt/ibm/db2/V10.1”. |
2.3 Sybase DB 정보
n VMware Workstation 8.0
n O/S : OEL 5 64bit (Mem 2GB)
n Host Name : unione (192.168.1.9)
| [/sybase] $isql -Usa -Psybase -Sunione
1> select @@version 2>go ———————————————————————————————————————————- Adaptive Server Enterprise/15.7.0/EBF 20373 SMP ESD#02 /P/x86_64/Enterprise Linux/ase157esd2/3109/64-bit/FBO/Sat Jul 7 05:36:19 2012 (1 row affected) |
3 Oracle Gateway 설치
n Oracle Database 가 설치되어 있는 서버에 Gateway 제품을 설치한다
n 설치 파일 : p10404530_112030_Linux-x86-64_5of7.zip
| [test1:/oracle]$ cd media/gateways
[test1:/oracle/media/gateways]$ ls -la total 68 drwxr-xr-x 7 oracle dba 4096 Sep 22 2011 . drwxr-xr-x 3 oracle dba 4096 May 24 13:30 .. drwxr-xr-x 6 oracle dba 4096 Sep 22 2011 doc drwxr-xr-x 4 oracle dba 4096 Sep 22 2011 install drwxr-xr-x 4 oracle dba 4096 Sep 22 2011 legacy -rwxr-xr-x 1 oracle dba 28122 Sep 22 2011 readme.html drwxr-xr-x 2 oracle dba 4096 Sep 22 2011 response -rwxr-xr-x 1 oracle dba 3226 Sep 22 2011 runInstaller drwxr-xr-x 14 oracle dba 4096 Sep 22 2011 stage -rwxr-xr-x 1 oracle dba 6525 Aug 23 2011 welcome.html [test1:/oracle/media/gateways]$ ./runInstaller |
n GUI 모드 설치 화면
![clip_image005[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0054.gif "clip_image005[4]")
![clip_image007[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0074.gif "clip_image007[4]")
![clip_image009[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0094.gif "clip_image009[4]")
![clip_image011[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0114.gif "clip_image011[4]")
![clip_image013[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0134.gif "clip_image013[4]")
![clip_image015[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0154.gif "clip_image015[4]")
![clip_image017[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0174.gif "clip_image017[4]")
![clip_image019[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0194.gif "clip_image019[4]")
![clip_image021[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0214.gif "clip_image021[4]")
![clip_image023[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0234.gif "clip_image023[4]")
![clip_image025[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0254.gif "clip_image025[4]")
![clip_image027[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0274.gif "clip_image027[4]")
1 Gateway 설정
1.1 DB2 Gateway 파라메터 설정
- $ORACLE_HOME/dg4db2/admin/initdg4db2.ora 파일 수정
- Target DB Port Number : 50000
- Target DB Instance Name : sample
| [test1:/oracle/product/11g/db/dg4db2/admin]$ cat initdg4db2.ora
# This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for DB2 # HS init parameters HS_FDS_CONNECT_INFO=[192.168.1.9]:50000/sample,LUW HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER HS_TRANSACTION_MODEL=COMMIT_CONFIRM HS_FDS_TRANSACTION_ISOLATION=READ_COMMITTED HS_FDS_SUPPORT_STATISTICS=TRUE HS_FDS_RESULTSET_SUPPORT=FALSE HS_FDS_PACKAGE_COLLID=ORACLEGTW HS_IDLE_TIMEOUT=0 |
1.2 SYBASE Gateway 파라메터 설정
- $ORACLE_HOME/dg4sybs/admin/initdg4sybs.ora 파일 수정
- Target DB Port Number : 5000
- Target DB Instance Name : pubs2
| [test1:/oracle/product/11g/db/dg4sybs/admin]$ cat initdg4sybs.ora
# This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Sybase # HS init parameters HS_FDS_CONNECT_INFO=[192.168.1.9]:5000/pubs2 HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER |
2 Oracle LISTENER 설정
2.1 listener.ora 파일 설정
- $ORACLE_HOME/network/admin/listener.ora
| [test1:/oracle/product/11g/db/network/admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/product/11g/db/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = ß 오라클 기본 리스너 (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = test1) (ORACLE_HOME = /oracle/product/11g/db) ) )
LISTENER_GW = ß DB2 Gateway 용 리스너 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1522)) ß Oracle Server IP ) SID_LIST_LISTENER_GW = (SID_LIST = (SID_DESC = (SID_NAME = dg4db2) ß tnsnames.ora 에 설정 된 alias (ORACLE_HOME = /oracle/product/11g/db) (ENV=”LD_LIBRARY_PATH=/oracle/product/11g/db/dg4db2/driver/lib;/oracle/product/11g/db/lib”) (PROGRAM = dg4db2) ) )
LISTENER_SB = ß Sybase Gateway 용 리스너 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1523)) ß Oracle Server IP ) SID_LIST_LISTENER_SB = (SID_LIST = (SID_DESC = (SID_NAME = dg4sybs) ß tnsnames.ora 에 설정 된 alias (ORACLE_HOME = /oracle/product/11g/db) (ENV=LD_LIBRARY_PATH=/oracle/product/11g/db/dg4sybs/driver/lib;/oracle/product/11g/db/lib”) (PROGRAM = dg4sybs) ) )
ADR_BASE_LISTENER = /oracle |
2.2 tnsnames.ora 파일 설정
- $ORACLE_HOME/network/admin/tnsnames.ora
| [test1:/oracle/product/11g/db/network/admin]$ cat tnsnames.ora
TEST1 = ß Oracle TNS 명 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST1) ) )
dg4db2 = ß DB2 TNS 명 (description = (address = (protocol = tcp)(host = 192.168.1.14)(port = 1522)) ß Oracle Server IP (connect_data = (sid = dg4db2)) (HS = OK) ß 이기종 DB 간 연결을 허용 ) dg4sybs = ß Sybase TNS 명 (description = (address = (protocol = tcp)(host = 192.168.1.14)(port = 1523)) ß Oracle Server IP (connect_data = (sid = dg4sybs)) (HS = OK) ß 이기종 DB 간 연결을 허용 ) |
2.3 LISTENER 시작
- DB2 Gateway 용 리스너 기동
| [test1:/oracle]$ lsnrctl start LISTENER_GW
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 14-JUN-2013 16:59:21 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /oracle/product/11g/db/bin/tnslsnr: please wait…
(중략)
Service “dg4db2” has 1 instance(s). Instance “dg4db2”, status UNKNOWN, has 1 handler(s) for this service… The command completed successfully |
- SYBASE Gateway 용 리스너 기동
| [test1:/oracle]$ lsnrctl start LISTENER_SB
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 14-JUN-2013 17:02:31 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /oracle/product/11g/db/bin/tnslsnr: please wait…
(중략)
Service “dg4sybs” has 1 instance(s). Instance “dg4sybs”, status UNKNOWN, has 1 handler(s) for this service… The command completed successfully |
3 DB Link 생성
- Gateway 제품 설치 및 이기종 DB 연동을 위한 모든 설정이 완료 되었다
- 이제 오라클에서 DB Link 를 만들어 기존 Oracle – Oracle 과 똑같은 방식으로 링크
연결하여 사용하면 된다.
- DB Link 생성 SQL
| SQL> create public database link 링크명 connect to 유저명 identified by 패스워드
using ‘TNSNAMES.ORA에서 지정한 서비스명‘; |
3.1 DB2 연결을 위한 DB Link 생성
- DB2 접속 User Name : db2inst1
- DB2 유저 패스워드 : db2inst1
| SQL> create public database link DB2_LINK connect to db2inst1 identified by db2inst1 using ‘dg4db2’;
Database link created. |
3.2 SYBASE 연결을 위한 DB Link 생성
- DB2 접속 User Name : sa
- DB2 유저 패스워드 : sybase
- SYBASE는 대/소문자를 엄격하게 구별한다. 반드시 “-“ 따옴표로 묶어주어야 한다
| SQL> create public database link sb_link connect to “sa” identified by “sybase” using ‘dg4sybs’;
Database link created. |
3.3 DB Link 생성 확인
| SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED ———- ———— ———— ———— ——————- PUBLIC DB2_LINK DB2INST1 dg4db2 2013-05-24:13:48:31 PUBLIC SB_LINK sa dg4sybs 2013-05-24:13:58:24 |
4 Gateway 데이터 조회 (select)
4.1 Oracle – DB2 데이터 조회
- DB2 Databae 에서 ‘staff’ 테이블 조회(Instance Name : sample)
![clip_image028[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0284.gif "clip_image028[4]"){kind=small}
![clip_image030[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0304.jpg "clip_image030[4]")
![clip_image032[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0324.jpg "clip_image032[4]")
n Oracle Databae 에서 dblink 를 통해‘staff’ 테이블 조회 – 성공
![clip_image033[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0334.gif "clip_image033[4]"){kind=small}
![clip_image035[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0354.jpg "clip_image035[4]")
![clip_image037[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0374.jpg "clip_image037[4]")
9.2 Oracle – Sybase 데이터 조회
n ![clip_image038[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0384.gif "clip_image038[4]"){kind=small}
Sybase Databae 에서 ‘sales’ 테이블 조회(Instance Name : pubs2)
![clip_image039[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0394.gif "clip_image039[4]"){kind=small}
![clip_image041[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0414.jpg "clip_image041[4]")
![clip_image043[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0434.jpg "clip_image043[4]")
n Oracle Databae 에서 dblink 를 통해 ‘sales’ 테이블 조회 – 실패
![clip_image044[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0444.gif "clip_image044[4]"){kind=small}
![clip_image046[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0464.jpg "clip_image046[4]")
n 중요) 위에서도 언급 했듯이 Sybase 에서는 대/소문자를 엄격하게 구별한다
따라서 테이블 명을 비롯한 모든 필드명에 반드시 “-“ 따옴표를 붙여 구별해 주어야 한다
n Oracle Databae 에서 dblink 를 통해 “sales” 테이블 조회 – 성공
![clip_image047[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0474.gif "clip_image047[4]"){kind=small}
![clip_image049[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0494.jpg "clip_image049[4]")
10 Gateway 데이터 이행 (migration)
n create table ~ as ~ select 명령문을 사용하여 테이블 및 데이터를 가져올 수 있다
|
SQL> create table table_name as (select * from target_table_name@db_link);
|
10.1 Oracle – DB2 데이터 이행
n Oracle Databae 에서 dblink 를 통해 DB2 의 ‘staff’ 테이블 생성 – 성공
|
SQL> create table staff as Table created. |
n Oracle 에서 생성된 ‘staff’ 테이블 조회 확인
![clip_image051[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0514.jpg "clip_image051[4]")
![clip_image053[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0534.jpg "clip_image053[4]")
10.2 Oracle – Sybase 데이터 이행
n Oracle Databae 에서 dblink 를 통해 Sybase 의 ‘sales’ 테이블 생성 – 성공
|
SQL> create table sales as
Table created.
|
n Oracle 에서 생성된 ‘sales’ 테이블 조회 확인
![clip_image055[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0554.jpg "clip_image055[4]")
I was studying some of your articles on this website and I think this website is rattling instructive! Keep on putting up.