Memo
BW Server
ISM
BWMAgent

Oracle Database 설치 및 환경 구성, 자동 시작 스크립트

Install

a. 오라클 설치 계정 생성 및 설치 환경 구성

    - Create Oracle User and Groups
        # /usr/sbin/groupadd -g 54322 dba
        # /usr/sbin/groupadd -g 54323 oper
        # /usr/sbin/groupadd -g 54324 backupdba
        # /usr/sbin/groupadd -g 54325 dgdba
        # /usr/sbin/groupadd -g 54326 kmdba
        # /usr/sbin/groupadd -g 54327 asmdba
        # /usr/sbin/groupadd -g 54328 asmoper
        # /usr/sbin/groupadd -g 54330 racdba        
        # /usr/sbin/useradd -u 54321 -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba oracle

    - Software Requirements
        # yum install bc binutils elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libXrender libX11 libXau libXi libXtst libgcc libnsl librdmacm libstdc++ libstdc++-devel libxcb libibverbs make smartmontools sysstat

    - Network Settings
        # sysctl -a |grep -e net.ipv4.tcp_[rw]mem
            net.ipv4.tcp_rmem = 4096        87380   1056768
            net.ipv4.tcp_wmem = 4096        16384   1056768
        /etc/sysctl.conf
            net.ipv4.tcp_rmem = 4096        87380   4194304
            net.ipv4.tcp_wmem = 4096        16384   4194304
        # sysctl -p
        # /etc/rc.d/init.d/network restart

    - User limits Settings
        /etc/security/limits.conf
            oracle  soft    nproc   2048
            oracle  hard    nproc   16384
            oracle  soft    nofile   1024
            oracle  hard    nofile   65536
            oracle  soft    stack   1024
            oracle  hard    stack   32768

    - Create Install Directory
        # mkdir -p /u01/app/oracle
        # mkdir -p /u01/app/oraInventory
        # chown -R oracle:oinstall /u01/app/oracle
        # chown -R oracle:oinstall /u01/app/oraInventory
        # chmod -R 775 /u01/app

    - Oracle Enviorments 
        $ ORACLE_SID=orcl
        $ export ORACLE_SID
        $ ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
        $ export ORACLE_HOME   

    - Install Envirmetns
        $ umask 022
        $ TMP=/mount_point/tmp
        $ TMPDIR=/mount_point/tmp
        $ export TMP TMPDIR

    - Install Oracle (db_home.zip - Oracle Database installation image)
        $ mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
        $ cd /u01/app/oracle/product/19.0.0/dbhome_1
        $ unzip -q /tmp/db_home.zip
        $ cd /u01/app/oracle/product/19.0.0/dbhome_1
        $ ./runInstaller

b.  Set max process
    alter system set processes=300 scope=spfile;
    alter system set sessions=335 scope=spfile;
    SESSIONS = (PROCESSES*1.1 + 5)

c. Oracle12c Common User Setting
    alter sessin set "_ORACLE_SCRIPT"=true;
    alter profile default limit password_life_time unlimited;        

d. Audit Settings
    - initSID.ora
	    AUDIT_TRAIL = [DB,OS,NONE]

e. Login case sensitive check
    show parameter sec_case;
    alter system set sec_case_sensitive_logon=false;

f. XA Init
    - Configure XA
        @$ORACLE_HOME/javavm/install/initxa.sql
    - Create XA View
        @$ORACLE_HOME/rdbms/admin/xaview.sql

g. Nvarchar2 Java option
    -Doracle.jdbc.defaultNChar=true -Dfile.encoding=UTF8

Change CharacterSet

    update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';
    update sys.props$ set value$='AL32UTF8' where name='NLS_NCHAR_CHARACTERSET';
    update sys.props$ set value$='KOREAN_KOREA.AL32UTF8' where name='NLS_LANGUAGE';
    
    alter database character set AL32UTF8;

Automating Database Startup and Shutdown on Linux

a. change /etc/oratab
    orcl:/app/oracle/product/19.0.0/dbhome_1:Y

b. create script directory
    $ mkdir /home/oracle/scripts

c. create Oracle setEnv sh
    $ vi /home/oracle/scripts/setEnv.sh
    # Oracle Settings
    export TMP=/tmp
    export TMPDIR=\$TMP
    export ORACLE_HOSTNAME=ol7-122.localdomain
    export ORACLE_UNQNAME=cdb1
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=\$ORACLE_BASE/product/12.2.0.1/db_1
    export ORACLE_SID=cdb1
    export PATH=/usr/sbin:/usr/local/bin:\$PATH
    export PATH=\$ORACLE_HOME/bin:\$PATH
    export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
    export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib

d. set setEnv.sh in .bash_profile
    $ echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile

e. create oracle start,stop shell
    $ vi /home/oracle/scripts/start_all.sh
        #!/bin/bash
        . /home/oracle/scripts/setEnv.sh
        export ORAENV_ASK=NO
        . oraenv
        export ORAENV_ASK=YES
        dbstart \$ORACLE_HOME

    $ vi /home/oracle/scripts/stop_all.sh
        #!/bin/bash
        . /home/oracle/scripts/setEnv.sh
        export ORAENV_ASK=NO
        . oraenv
        export ORAENV_ASK=YES
        dbshut \$ORACLE_HOME
        $ chown -R oracle.oinstall /home/oracle/scripts
        $ chmod u+x /home/oracle/scripts/*.sh

f. create /etc/init.d/dbora script & register dbora in init.d 
    # vi /etc/init.d/dbora
    #!/bin/sh
    # chkconfig: 345 99 10
    # description: Oracle auto start-stop script.
    #
    # Set ORA_OWNER to the user id of the owner of the 
    # Oracle database software.

    ORA_OWNER=oracle
    case "$1" in
        'start')
            # Start the Oracle databases:
            # The following command assumes that the oracle login 
            # will not prompt the user for any values
            # Remove "&" if you don't want startup as a background process.
            su $ORA_OWNER -c "/home/oracle/scripts/start_all.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1" &
            touch /var/lock/subsys/dbora
            ;;
        'stop')
            # Stop the Oracle databases:
            # The following command assumes that the oracle login 
            # will not prompt the user for any values
            su $ORA_OWNER -c "/home/oracle/scripts/stop_all.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
            rm -f /var/lock/subsys/dbora
            ;;
    esac

    # chmod 750 /etc/init.d/dbora
    # chkconfig --add dbora
    
g. start,stop dbora
    # service dbora start
    # service dbora stop