'Database/Tibero'에 해당되는 글 2건

** 설치 운영환경은 CentOS 7.5로 테스트 진행 하였고 운영하고 있습니다.

/*------------------------------*/

/* Linux 환경 확인 및 구성                         */

/*------------------------------*/

** 패키지 설치여부 확인

[root@gampol ~]# rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' \

    gcc \

    gcc-c++ \

    libgcc \

    libstdc++ \

    libstdc++-devel \

    compat-libstdc++ \

    libaio \

    libaio-devel

** 패키지 설치

[root@gampol ~]# yum install -y \

    gcc \

    gcc-c++ \

    libgcc \

    libstdc++ \

    libstdc++-devel \

    compat-libstdc++ \

    libaio \

    libaio-devel

** 커널 파라미터 확인

[root@gampol ~]# sysctl -a

** 커널 파라미터 변경하거나 추가함

[root@gampol ~]# vi /etc/sysctl.conf

    kernel.shmmni = 4096

    kernel.shmmax = 4398046511104

    kernel.shmall = 4294967296

    kernel.sem = 10000 32000 10000 10000

    kernel.msgmni = 512

    kernel.sysrq = 0

    kernel.core_uses_pid = 1

    kernel.msgmnb = 65536

    kernel.msgmax = 65536

    fs.aio-max-nr = 1048576

    fs.file-max = 6815744

    net.ipv4.ip_local_port_range = 1024 65500

    net.core.rmem_default = 262144

    net.core.rmem_max = 4194304

    net.core.wmem_default = 262144

    net.core.wmem_max = 1048586

** 커널 파라미터 적용

[root@gampol ~]# sysctl -p

** 계정 자원제한 설정(아래쪽에 추가함)

[root@gampol ~]# vi /etc/security/limits.conf

    * soft nproc 131072

    * hard nproc 131072

    * soft nofile 131072

    * hard nofile 131072

    * soft core unlimited

    * hard core unlimited

    * soft memlock 50000000

    * hard memlock 50000000

** 계정 인증 보안 설정(session 항목 첫라인에 추가함)

[root@gampol ~]# vi /etc/pam.d/login

    session     required    pam_limits.so

** SELinux 설정 해제(변경처리)

[root@gampol ~]# setenforce 0

[root@gampol ~]# vi /etc/selinux/config

    SELINUX=disabled

** hostname 확인 및 변경

[root@gampol ~]# hostname

    localhost.localdomain

[root@gampol ~]# hostnamectl set-hostname gampol

[root@gampol ~]# echo gampol > /proc/sys/kernel/hostname

[root@gampol ~]# hostname

    gampol

/*------------------------------*/

/* Tibero 계정생성                                    */

/*------------------------------*/

[root@gampol ~]# groupadd dba

[root@gampol ~]# useradd tibero -g dba -G dba

/*------------------------------*/

/* 환경변수 추가                                       */

/*------------------------------*/

[root@gampol ~]# su - tibero

[tibero@gampol ~]$ vi .bash_profile

    ### Java Env

    export JAVA_HOME=/usr/local/jdk

    export JDK_HOME=$JAVA_HOME

    export PATH=$JAVA_HOME/bin:$PATH

    ### Tibero Env

    export TB_BASE=/home/tibero

    export TB_HOME=$TB_BASE/tibero6

    export TBGW_HOME=$TB_HOME/client/gateway

    export TB_SID=TDBMS

    export NLS_LANG=KOREAN_KOREA.KO16MSWIN949

    #export NLS_LANG=AMERICAN_AMERICA.UTF8

    #export NLS_LANG=MSWIN949

    export TB_PROF_DIR=$TB_HOME/bin/prof

    export ORACLE_HOME=$TB_HOME/instantclient_11_2

    export LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$ORACLE_HOME:$LD_LIBRARY_PATH

    export SHLIB_PATH=$LD_LIBRARY_PATH

    export LIBPATH=$LD_LIBRARY_PATH

    export PATH=$TB_HOME/bin:$TB_HOME/client/bin:$ORACLE_HOME:$PATH

    ### TBinary Env

    export TBINARY_PATH=$TB_BASE/tbinary

    export PATH=$TBINARY_PATH/monitor:$PATH

    export LANG=ko_KR.eucKR

    ### Tibero Alias

    alias tbbase="cd $TB_BASE"

    alias tbhome="cd $TB_HOME"

    alias tbcfg="cd $TB_HOME/config"

    alias tbcli="cd $TB_HOME/client"

    alias tbscr="cd $TB_HOME/scripts"

    alias tbcfgv="vi $TB_HOME/config/$TB_SID.tip"

    alias tbcliv="vi $TB_HOME/client/config/tbdsn.tbr"

    alias tblog="cd $TB_BASE/log/$TB_SID"

    alias tbi='cd $TB_BASE/tbinary/'

    alias tm='cd $TB_BASE/tbinary/monitor;monitor;cd -'

    PS1='\e[0m[\e[1;31m\h\e[0m:\e[1;32m\u\e[0m@\e[1;33m$TB_SID\e[0m:\e[1;32m$NLS_LANG\e[0m \w]\n\$ '

/*------------------------------*/

/* Tibero 설치 작업                                   */

/*------------------------------*/

** 현재위치 확인

[tibero@gampol ~]$ cd $TB_BASE

[tibero@gampol ~]$ pwd

    /home/tibero

** $TB_HOME 압축 풀기

[tibero@gampol ~]$ tar xvzf tibero6-bin-FS06-linux64-152596-opt-20180306165737.tar.gz

** 확인

[tibero@gampol ~]$ ls -l $TB_HOME

    합계 12

    drwxr-xr-x.  3 tibero dba 4096  7월 26 12:54 bin/

    drwxr-xr-x. 10 tibero dba  105  7월 26 12:54 client/

    drwxr-xr-x.  2 tibero dba 4096  7월 26 12:54 config/

    drwxr-xr-x.  3 tibero dba  249   7월 26 12:55 lib/

    drwxr-xr-x.  3 tibero dba   26   7월 26 12:55 license/

    drwxr-xr-x.  3 tibero dba   22   7월 26 12:55 nls/

    drwxr-xr-x.  3 tibero dba 4096  7월 26 12:55 scripts/

** 기본 디렉토리 생성

[tibero@gampol ~]$ mkdir -p $TB_HOME/data/$TB_SID

[tibero@gampol ~]$ mkdir -p $TBGW_HOME

** 초기 환경파일 생성

[tibero@gampol ~]$ cd $TB_HOME/config

[tibero@gampol ~]$ ./gen_tip.sh

    Using TB_SID "TDBMS"

    /home/tibero/tibero6/config/TDBMS.tip generated

    /home/tibero/tibero6/config/psm_commands generated

    /home/tibero/tibero6/client/config/tbdsn.tbr generated.

    Running client/config/gen_esql_cfg.sh

    Done.

** Tibero 파라미터 수정

[tibero@gampol ~]$ vi $TB_HOME/config/$TB_SID.tip

    DB_NAME=TDBMS

    LISTENER_PORT=8629

    CONTROL_FILES="/home/tibero/tibero6/tbdata/TDBMS/c1.ctl","/home/tibero/tibero6/tbdata/TDBMS/c2.ctl"

    DB_CREATE_FILE_DEST="/data1/tibero6/data/TDBMS"

    #CERTIFICATE_FILE="/home/tibero/tibero6/config/svr_wallet/TDBMS.crt"

    #PRIVKEY_FILE="/home/tibero/tibero6/config/svr_wallet/TDBMS.key"

    #WALLET_FILE="/home/tibero/tibero6/config/svr_wallet/WALLET"

    #ILOG_MAP="/home/tibero/tibero6/config/ilog.map"

    MAX_SESSION_COUNT=200

    TOTAL_SHM_SIZE=4G

    MEMORY_TARGET=8G

[tibero@gampol ~]$ tbboot nomount

      *************************************************************************

      * ERROR: Can't open the license file!!

      * (1) Check the license file - /home/tibero/tibero6/license/license.xml

      *************************************************************************

    (라이선스가 없다면 https://technet.tmaxsoft.com/ 데모라이선스 신청하고 받은 라이선스 파일은 $TB_HOME/license 위치에 복사하도록 한다.)

[tibero@gampol ~]$ tbboot nomount

    Change core dump dir to /home/tibero/tibero6/bin/prof.

    Listener port = 8629

    Tibero 6

    TmaxData Corporation Copyright (c) 2008-. All rights reserved.

    Tibero instance started up (NOMOUNT mode).

[tibero@gampol ~]$ tbsql sys/tibero

# 기본 시스템 데이터 베이스 생성(서버 설치 PATH 확인후 실행한다.)

SQL> CREATE DATABASE "TDBMS"

        USER SYS IDENTIFIED BY tibero

        MAXINSTANCES 8

        MAXDATAFILES 100

        CHARACTER SET MSWIN949

        LOGFILE GROUP 1 '/data1/tibero6/data/TDBMS/log001.log' SIZE 500M,

                GROUP 2 '/data1/tibero6/data/TDBMS/log002.log' SIZE 500M,

                GROUP 3 '/data1/tibero6/data/TDBMS/log003.log' SIZE 500M

        MAXLOGGROUPS 255

        MAXLOGMEMBERS 8

        NOARCHIVELOG

               DATAFILE '/data1/tibero6/data/TDBMS/system001.dtf' SIZE 100M  AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED  DEFAULT TEMPORARY TABLESPACE TEMP

               TEMPFILE '/data1/tibero6/data/TDBMS/temp001.dtf'   SIZE 100M  AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL AUTOALLOCATE

        UNDO TABLESPACE UNDO

               DATAFILE '/data1/tibero6/data/TDBMS/undo001.dtf'   SIZE 100M  AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> exit;

[tibero@gampol ~]$ tbdown

    tbdown failed. proc info file is deleted.

    Hint: Please check if the tbsvr instance was already stopped.

[tibero@gampol ~]$ tbboot

    Change core dump dir to /home/tibero/tibero6/bin/prof.

    Listener port = 8629

    Tibero 6

    TmaxData Corporation Copyright (c) 2008-. All rights reserved.

    Tibero instance started up (NORMAL mode).

[tibero@gampol ~]$ $TB_HOME/scripts/system.sh

    Enter SYS password:

    tibero

    Enter SYSCAT password:

    syscat

    Dropping agent table...

    Creating text packages table ...

    Creating the role DBA...

    Create default system users & roles?(Y/N):

    y

    Creating system users & roles...

    Creating example users...

    Creating virtual tables(1)...

    Creating virtual tables(2)...

    Granting public access to _VT_DUAL...

    Creating the system generated sequences...

    Creating internal dynamic performance views...

    Creating outline table...

/*------------------------------*/

/* Tibero 일반 계정 생성                            */

/*------------------------------*/

** 시스템 계정 접속

[tibero@gampol ~]$ tbsql sys/tibero

/* 기존 유저 삭제 */

DROP USER gampol CASCADE;

/* 기존 작업 테이블 스페이스 삭제 */

DROP TABLESPACE ts_gampol INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

/* 테이블 스페이스 생성 */

CREATE TABLESPACE ts_gampol

DATAFILE '/data1/tibero6/data/TDBMS/ts_gampol.dtf' SIZE 500M

AUTOEXTEND ON NEXT 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

/* 유저 생성 */

CREATE USER gampol IDENTIFIED BY gampol

DEFAULT TABLESPACE ts_gampol

TEMPORARY TABLESPACE TEMP

ACCOUNT UNLOCK;

GRANT CONNECT

     ,RESOURCE

     ,SELECT ANY DICTIONARY

     ,SELECT ANY TABLE

     ,ALTER ANY PROCEDURE

     ,ALTER SESSION

     ,CREATE ANY SYNONYM

     ,CREATE VIEW

     ,CREATE DATABASE LINK

     ,CREATE SEQUENCE

     ,CREATE SESSION

     ,CREATE SYNONYM

     ,CREATE TABLE

     ,CREATE ANY TYPE

     ,DELETE ANY TABLE

     ,DROP ANY INDEX

     ,DROP ANY PROCEDURE

     ,DROP ANY SYNONYM

     ,DROP ANY TABLE

     ,DROP ANY TRIGGER

     ,DROP ANY VIEW

     ,DROP ANY TYPE

TO gampol;

/*--------------------------------------*/

/* Oracle Client 설치(최초 설치시)                             */

/*--------------------------------------*/

** Oracle 클라이언트 다운로드

https://www.oracle.com/database/technologies/instant-client/downloads.html

http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

** 오라클 클라이언트 최소 패키지 설치(오라클 서버나 별도 클라이언트가 설치 되지 않았다면) 

[tibero@gampol ~]$ unzip instantclient-basic-linux.x64-11.2.0.4.0.zip

[tibero@gampol ~]$ mv instantclient_11_2 $TB_HOME

[tibero@gampol ~]$ ls -l $TB_HOME

[tibero@gampol ~]$ mkdir -p $ORACLE_HOME/network/admin

[tibero@gampol ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

    #---- 테스트 ----#

    TDBMS_10g = (

       DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP) (HOST = 오라클서버 IP) (PORT = 1521))

           (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl10))

    )

** tbdsn.tbr 추가

[tibero@gampol ~]$ vi $TB_HOME/client/config/tbdsn.tbr

    #---- 테스트 ----#

    TDBMS_10g=(

        (GATEWAY=

            (LISTENER= (HOST=127.0.0.1) (PORT=9999))

            (TARGET=TDBMS_10g)

            (TX_MODE=GLOBAL)

        )

    )

** tbgw.cfg 생성

[tibero@gampol ~]$ vi $TB_HOME/client/config/tbgw.cfg

    LOG_DIR=$TBGW_HOME/oracle/log

    LOG_LVL=2

    LSNR_PORT=9999

    MAX_LOG_SIZE=1000

    MAX_LOG_CNT=5

    FETCH_SIZE=32000

/*--------------------------------*/

/* gw4orcl_XXg  서비스 등록                        */

/*--------------------------------*/

** Gateway 바이러리 복사

[tibero@gampol ~]$ cp $TB_HOME/client/bin/gw4orcl_11g $TBGW_HOME/gw4orcl

[tibero@gampol ~]$ ldd $TBGW_HOME/gw4orcl  (Not found 여부 확인)

[tibero@gampol ~]$ $TBGW_HOME/gw4orcl

[tibero@gampol ~]$ tbsql ssoview/ssolink@SSO

SQL> exit;

[tibero@gampol ~]$ tbsql piview_eval/piview_eval@INSA

SQL> exit;

[tibero@gampol ~]$ tbsql lftpmapp/ghktjd0906@EHOZO

SQL> exit;

** db-link 생성(gampol Tibero 계정 접속)

[tibero@gampol ~]$ tbsql gampol/gampol

SQL> CREATE DATABASE LINK TDBMS_10g CONNECT TO '오라클 계정명'     IDENTIFIED BY '계정 비밀번호'  USING 'TDBMS_10g';

SQL> SELECT COUNT(*) FROM 테스트테이블명@TDBMS_10g ;

/*--------------------------------------------------*/

/* Tibero Java 패키지 생성작업(PSM, PL/SQL)                                 */

/*--------------------------------------------------*/

** 암호화 모듈은 kisa에서 제공하는 lib를 활용하여 별도로 만들었으니
    필요한 java lib를 적용코자 한다면 설치 방법만 참고하시기 바랍니다.

** 자바 lib 저장 디렉토리 생성

[tibero@gampol ~]$ mkdir -p $TB_HOME/instance/$TB_SID/java

** 자바 lib 복사

[tibero@gampol ~]$ cd $TB_HOME/instance/$TB_SID/java

[tibero@gampol ~]$ ls -l

    commons-codec-1.10.jar

    kisaOraLib.jar

** 자바 패키지 적용 환경설정 추가

[tibero@gampol ~]$ vi $TB_HOME/config/$TB_SID.tip

        _PSM_BOOT_JEPA=Y

        JAVA_CLASS_PATH=/home/tibero/tibero6/instance/TDBMS/java

** JEPA 연결정보 추가

[tibero@gampol ~]$ vi $TB_HOME/client/config/tbdsn.tbr

        epa=(

                (

                    EXTPROC=(LANG=JAVA)

                    (

                        LISTENER=(HOST=localhost)

                        (PORT=9390)

                    )

                )

        )

** JEPA 환경설정 수정(기본정보 확인)

[tibero@gampol ~]$ cat $TB_HOME/client/epa/java/config/epa.cfg

** psmjava 클래스패스 추가

[tibero@gampol ~]$ vi $TB_HOME/bin/psmjavac

    codec=$TB_HOME/instance/TDBMS/java/commons-codec-1.10.jar

    kisa=$TB_HOME/instance/TDBMS/java/kisaOraLib.jar

    .......:${codec}:${kisa}:${classpath} ${src}

** tbjavaepa 클래스패스 추가

[tibero@gampol ~]$ vi $TB_HOME/client/bin/tbjavaepa

        :

    codec=$TB_HOME/instance/TDBMS/java/commons-codec-1.10.jar

    kisa=$TB_HOME/instance/TDBMS/java/kisaOraLib.jar

        :

    echo "....:$config:$codec:$kisa $mainclass ...."

        :

    echo "....:$config:$codec:$kisa $mainclass ...."

** 패키지 생성 및 테스트

    CREATE OR REPLACE PACKAGE crypto

        AS

            FUNCTION encrypt(str VARCHAR2) RETURN VARCHAR2;

            FUNCTION decrypt(str VARCHAR2) RETURN VARCHAR2;

            FUNCTION sha256(str  VARCHAR2) RETURN VARCHAR2;

        END;

        /

    CREATE OR REPLACE PACKAGE BODY crypto

        AS

            FUNCTION encrypt(str VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'kr.re.nsr.cbc.SeedCBC.encrypt(java.lang.String) return java.lang.String';

            FUNCTION decrypt(str VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'kr.re.nsr.cbc.SeedCBC.decrypt(java.lang.String) return java.lang.String';

            FUNCTION sha256(str  VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'kr.re.nsr.sha.SHA256.encrypt(java.lang.String) return java.lang.String';

        END;

        /

    SELECT crypto.encrypt('암호화 테스트!!') "TestEnc"

              ,crypto.decrypt('8mVH5wJNP9FHE0AmWsgkVQ==') "TestDec"

              ,crypto.sha256('암호화 테스트!!') "TestSHA256"

    FROM DUAL;

선행 작업으로 Tibero가 설치된 서버에 Oracle Client 설치필요(같은 서버에 이미 오라클이 설치 되어 있으면 불필요)

1. Tibero 계정 .bash_profile파일 내용에 Oracle Client 환경변수 추가

   export TB_HOME=/home/tibero/tibero6

   export TB_SID=nextbsc

   export LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib

   export LANG=ko_KR.UTF-8

   export TBGW_HOME=$TB_HOME/client/gateway

   export ORACLE_BASE=/oracle11

   export ORACLE_HOME=/oracle11/opt

   export ORACLE_SID=orcl949

   export ORACLE_OWNER=oracle11

   export ORA_NLS10=$ORACLE_HOME/nls/data

   export TMPDIR=$ORACLE_BASE/tmp

   export NLS_DATE_FORMAT=YYYYMMDD

   export TMP=$ORACLE_BASE/tmp

   export TNS_ADMIN=$ORACLE_HOME/network/admin

   export NLS_LANG=KOREAN_KOREA.KO16MSWIN949

   export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib

   export SHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib

   export PATH=$PATH:$TB_HOME/bin:$TB_HOME/client/bin:$ORACLE_HOME/bin:/usr/sbin:$PATH

   alias tblog='cd ${TB_HOME}/instance/$TB_SID/log'

   alias tbcfg='cd $TB_HOME/config'

   alias tbcfgv='vi $TB_HOME/config/$TB_SID.tip'

   alias tbi='cd ~/tbinary'

2. Oracle Client 권한변경

   chmod o+rx $ORACLE_HOME

   chmod o+rx $ORACLE_HOME/lib

   chmod o+r $ORACLE_HOME/lib/*

   chmod o+x $ORACLE_HOME/network

   chmod o+x $ORACLE_HOME/network/admin

   chmod o+r $ORACLE_HOME/network/admin/tnsnames.ora

   chmod -R o+rx $ORACLE_HOME/nls

   chmod -R o+rx $ORACLE_HOME/oracore

   chmod o+x $ORACLE_HOME/sqlplus

   chmod o+x $ORACLE_HOME/sqlplus/mesg

   chmod o+rx $ORACLE_HOME/sqlplus/mesg/*

3. Gateway Binary 복사

  cp $TB_HOME/client/bin/gw4orcl_11g $TBGW_HOME/gw4orcl

  (Oracle Client가 10g면 gw4orcl_10g)

  ldd $TBGW_HOME/gw4orcl

  (Not found 여부 확인)

4. Network Alias 설정

   vi $TB_HOME/client/config/tbdsn.tbr

   orcl10=(

            (GATEWAY=

               (LISTENER=

                  (HOST=192.168.0.242) # Tibero서버 IP(Host명 불가, 꼭 IP만 기록해야됨)

                  (PORT=9999) # 로그 기록포트

               )

               (TARGET=NEXTBSC_10g) # Oracle Client에 tnsnames.ora 접속하고자 하는 타켓명(Alias명)하고 같아야함

               (TX_MODE=GLOBAL)

            )

   )

5. Gateway 환경설정

   vi ${TB_HOME}/client/config/tbgw.cfg

   LOG_DIR=$TBGW_HOME/oracle/log

   LOG_LVL=2

   LSNR_PORT=9999 # 위 4번에서 설정한 같은 port

   MAX_LOG_SIZE=1000

   MAX_LOG_CNT=5

   FETCH_SIZE=32000

6. Database Link 생성 및 Test

   CREATE DATABASE LINK <DB LINK명> CONNECT TO <USER명> IDENTIFIED BY '<PASSWORD>' USING '<NETWORK ALIAS명>';

   SELECT COUNT(*) FROM NG02_MEMBER_INFO@<DB LINK명>;