Linux 64bit | Oracle database 19c Gateway for ODBC 설치 (TIBERO <-> ORACLE DBLINK 연결)

1. 게이트웨이 파일 다운로드

다운로드 사이트

https://edelivery.oracle.com/osdc/faces/SoftwareDelivery

https://login.oracle.com:443/oam/server/obrareq.cgi?encquery%3DI4mVV99W%2BGdnKDvHN3%2Bs0DPdF2tfceCfF5Wc4AuOS8fKcxWE7s7LEZulF%2BcyD3x1TNOTK5Z%2FP%2FEKMm3TBkDhXZTbRgZRrFXyC9FkuutHNGZOmWDFf%2BIWirRXO7Fhoy2jBwdc4ZYV0HbsOqchy%2FdWi7BkhWOtCznEdB5qomtUliHSXOuInEm5oe%2F2%2FrfkaX7%2BGPeLG0g0OSNE9Y7w9mdwSnLo8L7jv%2FNtkaq7awCLc7ftvR5h4SpEAahu5zl9rIGwZjExliBjYKEQ2nJESlqcgBDxQCchjnt5bQf51rlpJdpFPZy2BZFV8JdnTP5IOh6Ty4GNmAcKaL%2B5AYrYrhhq8Yn7GitmHwUHQwNN4BLjUHs%3D%20agentid%3Dedelivery-extprod%20ver%3D1%20crmethod%3D2%26cksum%3Dffd9e828e469566f3fffc32fc995229fd6383a34&ECID-Context=1.005x2LcgVssFo2KimTXvWJ0003PZ003HAi%3BkXjE

 

Oracle 19c Gateway 검색 후 Linux version을 선택해 다운로드 (밑에거만)

Windows -> Linux scp로 다운받은 파일 전송

* 윈도우에 OpenSSH 클라이언트 앱이 설치되어 있어야 함 ~

// windows cmd

C:\Users\CAMI>cd C:\Users\CAMI\Downloads
C:\Users\CAMI\Downloads>scp -r C:\Users\CAMI\Downloads\LINUX.x64_193000_gateways.zip [linux계정]@[linux IP]:/u01/media/gw

oracle@[IP주소]'s password:
LINUX.x64_193000_gateways.zip

리눅스에서 확인

[TIBERO|oracle:/u01/media/gw]> ls -al
total 983552
drwxr-xr-x. 2 oracle dba         43 Feb  7 22:21 .
drwxrwxr-x. 9 oracle dba         91 Feb  7 21:58 ..
-rw-r--r--. 1 oracle dba 1007154302 Feb  7 22:22 LINUX.x64_193000_gateways.zip
[TIBERO|oracle:/u01/media/gw]> chmod 775 LINUX.x64_193000_gateways.zip 
[TIBERO|oracle:/u01/media/gw]> ls -al
total 983552
drwxr-xr-x. 2 oracle dba         43 Feb  7 22:21 .
drwxrwxr-x. 9 oracle dba         91 Feb  7 21:58 ..
-rwxrwxr-x. 1 oracle dba 1007154302 Feb  7 22:22 LINUX.x64_193000_gateways.zip

2. 오라클 .bash_profile 설정

export ORACLE_HOSTNAME=tibero
export ORACLE_UNQNAME=TIBERO
export ORACLE_DBNAME=TIBERO
export ORACLE_BASE=/u01/app/oracle
export DB_HOME=$ORACLE_BASE/product/19.17.0.0/dbhome_1
export ORACLE_HOME=$DB_HOME
export GRID_HOME=/u01/app/oracle/product/19.17.0.0/dbhome_1
export ORACLE_SID=TIBERO
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$GRID_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/jdk/bin:/usr/local/bin:/usr/sbin:.:$BASE_PATH:$HOME/.local/bin:$HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export TNS_ADMIN=$GRID_HOME/network/admin

3. Gateway 설치 (GUI)

[TIBERO|oracle:/u01/media/gw]> unzip LINUX.x64_193000_gateways.zip 

[TIBERO|oracle:/u01/media/gw]> ls -al
total 983552
drwxr-xr-x. 3 oracle dba         59 Feb  7 22:33 .
drwxrwxr-x. 9 oracle dba         91 Feb  7 21:58 ..
drwxr-xr-x. 5 oracle dba         90 Apr 17  2019 gateways
-rwxrwxr-x. 1 oracle dba 1007154302 Feb  7 22:22 LINUX.x64_193000_gateways.zip

[TIBERO|oracle:/u01/media/gw]> cd gateways/
[TIBERO|oracle:/u01/media/gw/gateways]> ls -al
total 24
drwxr-xr-x.  5 oracle dba   90 Apr 17  2019 .
drwxr-xr-x.  3 oracle dba   59 Feb  7 22:33 ..
drwxr-xr-x.  4 oracle dba 4096 Apr 17  2019 install
drwxrwxr-x.  2 oracle dba   37 Apr 17  2019 response
-rwxr-xr-x.  1 oracle dba 8850 Apr 17  2019 runInstaller
drwxr-xr-x. 16 oracle dba 4096 Apr 17  2019 stage
-rwxrwxr-x.  1 oracle dba  500 Feb  7  2013 welcome.html

[TIBERO|oracle:/u01/media/gw/gateways]> ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB.   Actual 9113 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8183 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2023-02-07_10-35-03PM. Please wait ...[TIBERO|oracle:/u01/media/gw/gateways]>

ORACLE_BASE, ORACLE_HOME DIRECTORY

Tibero를 위해 ODBC 선택

NEXT 누르면 검사 후다닥 지나가고 요약 창 > install

내 패치(19.17)에 영향을 미칠 경우 롤백할 수 있다고 함 -> 일단 yes

루트 계정으로 쉘을 돌리고 ok 클릭

설치완료

 /u01/app/oracle/product/19.17.0.0/dbhome_1/install/response/tg_2023-02-07_10-35-03PM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2023-02-07_10-35-03PM.log

ㅇㅋㅇㅋ

 

4. 리스너 설정추가

[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin]> vi listener.ora 

-----------------------------------------------------------------------------------------
// 추가
LTIBERO =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 리스너가 기동될 서버 ip)(PORT = 리스너 기동될 서버 port))
  )

SID_LIST_LTIBERO =
  (SID_LIST =
    (SID_DESC =
       (ORACLE_HOME = /u01/app/oracle/product/19.17.0.0/dbhome_1) ## ORACLE_HOME
       (SID_NAME = DBCAMI) ## tibero DB SID
       (PROGRAM = dg4odbc) ## ODBC Driver 명
    )
  )

리스너 기동

[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin]> lsnrctl start LTIBERO

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-FEB-2023 22:54:38

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

Starting /u01/app/oracle/product/19.17.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/DBCAMI/ltibero/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)))

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias                     LTIBERO
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                07-FEB-2023 22:54:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBCAMI/ltibero/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)))
Services Summary...
Service "DBCAMI" has 1 instance(s).
  Instance "DBCAMI", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

5. $ORACLE_HOME/hs/admin 에서 Gateway 접속 정보 설정

샘플 initdg4odbc.ora 를 복사하여 새로 생성 및 수정하여 환경 설정

** init$TB_SID.ora 로 만들어야 인식함

[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/hs/admin]> cp initdg4odbc.ora initDBCAMI.ora
[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/hs/admin]> vi inittibero.ora 

------------------------------------------------------------------------------------------
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = tbodbc
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /u016/tibero6/client/lib/libtbodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.KO16MSWIN949

#
# ODBC specific environment variables
#
set ODBCINI=/u01/media/odbc/etc/odbc.ini

6. $ORACLE_HOME/network/admin/tnsnames.ora 설정

TIBERODB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = DBCAMI)
    )
    (HS=OK)
  )

tnsping 확인

[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin]> tnsping TIBERODB

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-FEB-2023 23:08:08

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

Used parameter files:
/u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1522))) (CONNECT_DATA = (SID = DBCAMI)) (HS=OK))
OK (0 msec)

7. sql 접속하여 DBLINK 생성

[TIBERO|oracle:/u01/app/oracle/product/19.17.0.0/dbhome_1/network/admin]> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 7 23:09:03 2023
Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> create public database link "TIBERODB" connect to "SYS" identified by "tibero" using 'TIBERODB';

Database link created.

SQL> select name from v$database@TIBERODB ;

NAME
--------------------------------------------------------------------------------
DBCAMI

SQL>

 

## 참고 사항

$ORACLE_HOME/hs/admin/initDBCAMI.ora 파일에

HS_LANGUAGE = AMERICAN_AMERICA.KO16MSWIN949 가 없으면 

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

오류가 난다 .. 반드시 추가해줘야 함