[Migration] Oracle Gateway를 활용한 Oracle to Tibero DB Link

테스트 환경

 

	SourceDB	TargetDB
DBMS	Oracle	Tibero
OS	Centos 7.9	Centos 7.9
DB Version	SQL*Plus: Release 12.2.0.1.0 Production	tbSQL 6   (Build 186930)
SID	ORA12C	TB6
IP	192.168.100.100	192.168.100.50

 

1. Tibero 유저의 profile에 Oracle GateWay 환경 설정

    - 디렉토리를 생성하되 반드시 755 권한을 부여해야 설치 가능 

    - 755 권한을 부여하지 않을 경우 [INS-32012] 에러 발생

 

1. 디렉토리 생성
mkdir -p /ora_gateway/install --install 파일
mkdir -p /ora_gateway/app --ORACLE_HOME 

2. 권한 부여
chown -R tibero6. /ora_gateway
chmod -R 755 /ora_gateway

3. .bash_profile 설정 

#ORACLE to TIbero Setting
#GateWay install  route
export ORACLE_BASE=/ora_gateway
export ORACLE_HOME=$ORACLE_BASE/app
export PATH=$PATH:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

 

2. OracleGateway 다운로드

    (11g나 12c의 경우 Oracle Delivery를 통해서 다운로드 필요)

https://edelivery.oracle.com/

https://login.oracle.com:443/oam/server/obrareq.cgi?encquery%3De0Bat1qFIy8e51MXRBCKTziM3aaWHZMXGLko50BytddxRPvAsp5t5jAaVO7kIEC0Qwp8FU%2BysSgON%2FkKJ8bkr81r2dQTuSdwvPOaxJO4i7h%2FyCRwTlHaSa8F%2By4GbZRGMe%2BGNWxpLnZ5znrQCk61DuXCFYUcuecQL4edWDK9UujZHyMJmxm0mPxNc9agJ8rTCXKdiDqmWJsWEjRquAb8UIBEuJZ6%2BZvBApLoMP6gBYsumxEXK4GE5DVtWHsfEF25jcw5dAKqaWYAGvsjoD2keFuF77Gr4mzAulHqwf84ekqrF9VTyg5HAGqXy8UkUDD6mxz4Yq9NnPc4ZVFyjm%2FQ1iuyUvwfA3FXcPJEW%2FrpC48%3D%20agentid%3Dedelivery-extprod%20ver%3D1%20crmethod%3D2%26cksum%3D9205d1b0cc26ea57fef85891eabb9f0933b13775&ECID-Context=1.005xylcFvp3Fo2KimTXvWJ0005wN000pxN%3BkXjE

 

3. 다운로드 후 GateWay 설치

 

[tibero6@tiberovm:/tmp]$ cp V840026-01.zip /ora_gateway/install

[tibero6@tiberovm:/ora_gateway/install]$ unzip V840026-01.zip
Archive:  V840026-01.zip
   creating: gateways/
   creating: gateways/response/
  inflating: gateways/response/netca.rsp
  inflating: gateways/response/tg.rsp
   creating: gateways/install/
  inflating: gateways/install/detachHome.sh
 extracting: gateways/install/addLangs.sh
   creating: gateways/install/images/
  inflating: gateways/install/images/billboards.gif
...

[tibero6@tiberovm:/ora_gateway]$ ls
gateways

[tibero6@tiberovm:/ora_gateway/install]$ cd gateways/

[tibero6@tiberovm:/ora_gateway/install/gateways]$ ls
install  response  runInstaller  stage  welcome.html

[tibero6@tiberovm:/ora_gateway/install/gateways]$ ./runInstaller

Oracle Base와 Oracle Home 설정

oraInventory 경로 설정

Component 설정

GateWay를 설치하는 데 있어 필수 요구사항

--필수 패키지 설치

[root@tiberovm ~]# yum install -y libstdc* gcc* ksh* glibc* libaio*

[root@tiberovm ~]# /ora_gateway/oraInventory/orainstRoot.sh
Changing permissions of /ora_gateway/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /ora_gateway/oraInventory to dba.
The execution of the script is complete.
[root@tiberovm ~]# /ora_gateway/app/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= tibero6
    ORACLE_HOME=  /ora_gateway/app

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

 

4. ODBC 설치

     - Unix 계열의 경우 ODBC Manager가 존재하지 않으므로 iodbc 또는 UNIX ODBC를 별도로 설치해야한다

 

4-1 3.52.7 iodbc 설치

https://sourceforge.net/projects/iodbc/

iODBC

[tibero6@tiberovm:/home/tibero6]$ cp libiodbc-3.52.7.tar.gz /ora_gateway/install/
libiodbc-3.52.7.tar.gz  oraInventory  script  V840026-01.zip

[tibero6@tiberovm:/ora_gateway/install]$ tar -xvf libiodbc-3.52.7.tar.gz
libiodbc-3.52.7/
libiodbc-3.52.7/admin/
libiodbc-3.52.7/admin/gtk-2.0.m4
libiodbc-3.52.7/admin/gtk.m4
libiodbc-3.52.7/admin/libtool.m4
libiodbc-3.52.7/admin/ltoptions.m4
...

[tibero6@tiberovm:/ora_gateway/install]$ ls
gateways  libiodbc-3.52.7

[tibero6@tiberovm:/ora_gateway/install]$ cd libiodbc-3.52.7/

[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ ls
acinclude.m4  bin           configure.in  etc           iodbc      LICENSE.BSD   Makefile.in    README
aclocal.m4    bootstrap.sh  COPYING       IAFA-PACKAGE  iodbcadm   LICENSE.LGPL  man            README.CVS
admin         ChangeLog     debian        include       iodbcinst  mac           NEWS           README.MACOSX
AUTHORS       configure     drvproxy      INSTALL       LICENSE    Makefile.am   PORT.OpenLink  samples

--사전환경 점검
[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ ./configure --prefix=/ora_gateway/install/libiodbc-3.52.7/iodbc --disable-gui
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking whether to enable maintainer-specific portions of Makefiles... no
checking cached information... ok
creating config.nice
checking for iODBC installation layout... default
checking for gcc... gcc
checking for C compiler default output file name... a.out
...
iODBC Driver Manager 3.52.7 configuration summary
=================================================

Installation variables
  layout                  default
  prefix                  /ora_gateway/install/libiodbc-3.52.7/iodbc
  exec_prefix             ${prefix}

Installation paths
  programs                ${exec_prefix}/bin
  include files           ${prefix}/include
  libraries               ${exec_prefix}/lib
  manual pages            ${datarootdir}/man

Configuration files
  odbc.ini                /etc/odbc.ini
  odbcinst.ini            /etc/odbcinst.ini
  default FILEDSN path    /etc/ODBCDataSources

Extensions
  ODBC Version            3
  GUI Extensions          false
  ThreadSafe              true
  Install libodbc.so      true

--사전 환경 점검에 문제가 없을 경우 컴파일 수행
[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ make
Making all in admin
make[1]: Entering directory `/ora_gateway/install/libiodbc-3.52.7/admin'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/ora_gateway/install/libiodbc-3.52.7/admin'
...
/bin/sh ../libtool --tag=CC   --mode=compile gcc -std=gnu99 -DHAVE_CONFIG_H -I. -I../include -I../include -I../iodbc -I../iodbcinst -I../iodbcadm  -DWITH_PTHREADS -D_REENTRANT  -g -O2 -DODBCVER=0x0350 -DIODBC_BUILD=709090

--컴파일된 파일 설치
[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ make install
Making install in admin
make[1]: Entering directory `/ora_gateway/install/libiodbc-3.52.7/admin'
make[2]: Entering directory `/ora_gateway/install/libiodbc-3.52.7/admin'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/ora_gateway/install/libiodbc-3.52.7/iodbc/lib/pkgconfig" || /usr/bin/mkdir -p "/ora_gateway/install/libiodbc-3.52.7/iodbc/lib/pkgconfig"
 /usr/bin/install -c -m 644 libiodbc.pc '/ora_gateway/install/libiodbc-3.52.7/iodbc/lib/pkgconfig'
...

--정상적으로 설치 되어 있는지 확인, 만약 환경이 다를 경우 make clean을 통해 삭제 필요

[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ cd iodbc/bin/

[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7/iodbc/bin]$ file iodbctest
iodbctest: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=6958b9eaf2c314d40c4f00eddb23a583eb324011, not stripped

 

4-2 ODBC 설정

        - 접속할 DBMS DNS 정보를 설정하는 파일

        - .odbc.ini > /etc/odbc.ini 순으로 읽어드림

[ODBC Data Sources]
Tibero6 = Tibero6 ODBC driver

[ODBC]
Trace = 1
TraceFile = /ora_gateway/install/libiodbc-3.52.7/trace/odbc.trace

[Tibero6]

#Driver = /home/django/tibero7/client/lib/libtbodbc.so
Driver = /tibero_engine/tibero6/client/lib/libtbodbc.so
Description = Tibero ODBC driver for Tibero
server = 192.168.100.50
#server ip or hostname

port = 8629
#tibero port

database = TB6
#DB_NAME

User = sys
Password = tibero

--접속 테스트

[tibero6@tiberovm:/ora_gateway/install/libiodbc-3.52.7]$ iodbctest "DSN=Tibero6;UID=sys;PWD=tibero"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909
Driver: 06.00.0215 (libtbodbc.so)

SQL>select * from dual;

DUMMY
-----
X

 result set 1 returned 1 rows.

 

5. Oracle 서버에서 tnsnames.ora에 Tibero 접속 정보 등록

 

TB6 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.50)(PORT = 8729))
    (CONNECT_DATA = (SID =TB6))
   (HS = OK)
)

 

6. Tibero 서버에 리스너 등록

 

TB6 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.50)(PORT = 8729))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC8729))
    )
  )

SID_LIST_TB6 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = TB6)
      (ORACLE_HOME = /ora_gateway/app)
      (PROGRAM = dg4odbc) --10g의 경우 hsodbc
    )
  )

 

7. Tibero 서버 리스너 기동

 

[tibero6@tiberovm:/home/tibero6]$ lsnrctl start TB6

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-MAR-2023 11:23:21

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

Starting /ora_gateway/app/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /ora_gateway/app/network/admin/listener.ora
Log messages written to /ora_gateway/diag/tnslsnr/tiberovm/tb6/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tiberovm)(PORT=8729)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC8729)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tiberovm)(PORT=8729)))
STATUS of the LISTENER
------------------------
Alias                     TB6
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                29-MAR-2023 11:23:26
Uptime                    0 days 0 hr. 0 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora_gateway/app/network/admin/listener.ora
Listener Log File         /ora_gateway/diag/tnslsnr/tiberovm/tb6/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tiberovm)(PORT=8729)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC8729)))
Services Summary...
Service "TB6" has 1 instance(s).
  Instance "TB6", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 

8. Tibero 서버 init$TB_SID.ora 파일 생성 및 수정

 

[tibero6@tiberovm:/home/tibero6]$ cd $ORACLE_HOME/hs/admin

[tibero6@tiberovm:/ora_gateway/app/hs/admin]$ ls -al
total 16
drwxr-xr-x 2 tibero6 dba  102 Mar 29 10:52 .
drwxr-xr-x 5 tibero6 dba   41 Mar 29 10:52 ..
-rw-r--r-- 1 tibero6 dba 1170 Jan 26  2017 extproc.ora
-rw-r--r-- 1 tibero6 dba  489 Jan 26  2017 initdg4odbc.ora << 수정할 파일
-rw-r--r-- 1 tibero6 dba  388 Mar 29 10:52 listener.ora.sample
-rw-r--r-- 1 tibero6 dba  244 Mar 29 10:52 tnsnames.ora.sample

[tibero6@tiberovm:/ora_gateway/app/hs/admin]$ cp initdg4odbc.ora initTB6.ora

[tibero6@tiberovm:/ora_gateway/app/hs/admin]$ mv initdg4odbc.ora initdg4odbc.ora_bk
--ora파일이 2개일 경우 잘못 불러오는 경우가 있을 수 있어 기존에있는 init파일명 변경

[tibero6@tiberovm:/ora_gateway/app/hs/admin]$ vi initTB6.ora

# 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 = TB6
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /tibero_engine/tibero6/client/lib/libtbodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.KO16MSWIN949

#
# ODBC specific environment variables
#
set ODBCINI=/home/tibero6/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

 

9. ORACLE에서 DB LINK 생성 및 접속 테스트

 

SQL> CREATE DATABASE LINK tblk
CONNECT TO sys
IDENTIFIED BY tibero
USING 'TB6';

Database link created.

SQL> select *
  2  from tab@tblk;

TNAME
--------------------------------------------------------------------------------
TABTYPE
---------------------------------------------------------------------
AQ$_QUEUES
TABLE

AQ$_QUEUE_TABLES
TABLE

AQ$_SYS_NOTICE_Q_TABLE_H
TABLE

 

참고자료 및 파일

[기술노트 112회] 이기종 DB Link.pdf

1.53MB

ORACLE_TO_TIBERO_DBLINK.pdf

1.56MB

libiodbc-3.52.7.tar.gz

0.99MB