OracleDB

Centos에서 Oracle 10G 설치

  1. 패키지 설치

    $> yum -y groupinstall "Desktop" "X Window System" "GNOME Desktop Environment" "Desktop Platform Development" "Desktop Platform"
    $> yum -y install glibc* libaio* compat-libstdc* compat-gcc* gcc* libXp* openmotif* compat-db* xorg-x11-deprecated-libs compat-lib*
    $> yum install kde-i18n-Korean -y
    $> yum install fonts-korean -y
    • Centos 6 64비트만
      $> yum install -y xorg-x11-xauth.x86_64 xorg-x11-apps.x86_64
      $> yum install -y libXp libXtst binutils compat-db compat-libstdc++-33 glibc glibc-devel glibc-headers gcc gcc-c++ libstdc++ cpp make libaio ksh elfutils-libelf sysstat libaio libaio-devel setarch  libXp.i686 libXtst-1.0.99.2-3.el6.i686 glibc-devel.i686 libgcc-4.4.4-13.el6.i686 compat-libstdc++* compat-libf2c* compat-gcc* compat-libgcc* libXt.i686 libXtst.i686
      $> yum install -y glibc-2.12-1.7.el6_0.5.i686
  2. 커널 파라미터 설정
    $> echo "kernel.shmmax = 536870912
    kernel.shmmni = 4096
    kernel.sem = 250 32000 100 128
    net.ipv4.ip_local_port_range = 1024 65000
    net.core.rmem_default=262144
    net.core.rmem_max=262144
    net.core.wmem_default=262144
    net.core.wmem_max=262144" >> /etc/sysctl.conf
    $> sysctl -p
  3. 계정설정
    $> echo "#Oracle setting
    *               soft    nproc   2047
    *               hard    nproc   16384
    *               soft    nofile  1024
    *               hard    nofile  65536" >> /etc/security/limits.conf
    $> echo "session    required     pam_limits.so" >> /etc/pam.d/login
    $> groupadd oinstall
    $> useradd -g oinstall oracle
    $> passwd oracle
    {{ 패스워드 입력 두번 }}
    $> mkdir /usr/local/oracle
    $> chown oracle.oinstall /usr/local/oracle
  4. 설치시 OS정보 변경 수행
    $> mv /etc/redhat-release /etc/redhat-release_ori
    $> echo "redhat-4" > /etc/redhat-release
  5. 시스템 설정 적용
    $> echo "
    # Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=$TMP; export TMPDIR
    ORACLE_BASE=/usr/local/oracle; export ORACLE_BASE
    ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
    ORACLE_SID=orcl; export ORACLE_SID
    ORACLE_TERM=xterm; export ORACLE_TERM
    PATH=/usr/sbin:$PATH; export PATH
    PATH=$ORACLE_HOME/bin:$PATH; export PATH
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
    
    if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
      else
        ulimit -u 16384 -n 65536
      fi
    fi" >> /etc/profile
  6. 시스템 기동시 Oracle 구동을 위한 적용
    echo "
    source /etc/profile
    su - oracle -c \"\$ORACLE_HOME/bin/lsnrctl start\"
    su - oracle -c \"\$ORACLE_HOME/bin/dbstart\"
    " >> /etc/rc.d/rc.local

Centos에서 oracle 11g 설치

  1. 패키지 설치하기

    $> yum -y groupinstall "Desktop" "X Window System" "GNOME Desktop Environment" "Desktop Platform Development" "Desktop Platform"
    $> yum -y install glibc* libaio* compat-libstdc* compat-gcc* gcc* libXp* openmotif* compat-db* xorg-x11-deprecated-libs compat-lib*
    $> yum install kde-i18n-Korean -y
    $> yum install fonts-korean -y
    $> yum install -y binutils-2.* compat-libstdc++-33* elfutils* gcc-4.* gcc-c++-4.* glibc-2.* glibc* ksh* libaio* libgomp* libgcc* libstdc++* unixODBC-2* unixODBC-devel* numactl-devel* sysstat* pdksh*
    • Centos6 64비트에서는 추가 패키지 설치
      $> yum install -y xorg-x11-xauth.x86_64 xorg-x11-apps.x86_64
      $> yum install -y libXp libXtst binutils compat-db compat-libstdc++-33 glibc glibc-devel glibc-headers gcc gcc-c++ libstdc++ cpp make libaio ksh elfutils-libelf sysstat libaio libaio-devel setarch  libXp.i686 libXtst-1.0.99.2-3.el6.i686 glibc-devel.i686 libgcc-4.4.4-13.el6.i686 compat-libstdc++* compat-libf2c* compat-gcc* compat-libgcc* libXt.i686 libXtst.i686
      $> yum install -y glibc-2.12-1.7.el6_0.5.i686
  2. 커널 파라미터 적용
    $> echo "fs.suid_dumpable = 1
    fs.aio-max-nr = 1048576
    fs.file-max = 6815744
    kernel.shmall = 2097152
    kernel.shmmax = 536870912
    kernel.shmmni = 4096
    # semaphores: semmsl, semmns, semopm, semmni
    kernel.sem = 250 32000 100 128
    net.ipv4.ip_local_port_range = 9000 65500
    net.core.rmem_default=4194304
    net.core.rmem_max=4194304
    net.core.wmem_default=262144
    net.core.wmem_max=1048586" >> /etc/sysctl.conf
    $> sysctl -p
  3. 계정 설정
    $> echo "oracle              soft    nproc   2047
    oracle              hard    nproc   16384
    oracle              soft    nofile  4096
    oracle              hard    nofile  65536
    oracle              soft    stack   10240" >> /etc/security/limits.conf
    $> groupadd oinstall
    $> useradd -g oinstall oracle
    $> passwd oracle
    {{ 패스워드 입력 2번 }}
    $> mkdir /usr/local/oracle
    $> chown oracle.oinstall /usr/local/oracle
  4. OS정보 수정
    mv /etc/redhat-release /etc/redhat-release_ori
    echo "redhat-4" > /etc/redhat-release
  5. 시스템 변수 설정
    $> echo "
    # Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=\$TMP; export TMPDIR
    ORACLE_HOSTNAME=orcl.localdomain; export ORACLE_HOSTNAME
    ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
    ORACLE_BASE=/usr/local/oracle; export ORACLE_BASE
    ORACLE_HOME=\$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
    ORACLE_SID=orcl; export ORACLE_SID
    PATH=/usr/sbin:\$PATH; export PATH
    PATH=\$ORACLE_HOME/bin:\$PATH; export PATH
    LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib; export CLASSPATH" >> /etc/profile
  6. 시스템 구동시 오라클 구동
    $> echo "
    source /etc/profile
    su - oracle -c \"\$ORACLE_HOME/bin/lsnrctl start\"
    su - oracle -c \"\$ORACLE_HOME/bin/dbstart\"
    " >> /etc/rc.d/rc.local

Centos에서 oracle 12c 설치하기

  1. 패키지 설치하기
    $> yum install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 gcc gcc-c++ glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libgcc -y
    $> yum install libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libaio libaio.i686 libaio-devel libaio-devel.i686 libXext libXext.i686 -y
    $> yum install libXtst libXtst.i686 libX11 libX11.i686 libXau libXau.i686 libxcb libxcb.i686 libXi libXi.i686 unixODBC unixODBC-devel -y
  2. 커널파라미터 수정
    $> echo "fs.file-max = 6815744
    kernel.sem = 250 32000 100 128
    kernel.shmmni = 4096
    kernel.shmall = 1073741824
    kernel.shmmax = 4398046511104
    net.core.rmem_default = 262144
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 1048576
    fs.aio-max-nr = 1048576
    net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
    $> sysctl -p
  3. 계정정보 설정
    $> echo "oracle   soft   nofile    1024
    oracle   hard   nofile    65536
    oracle   soft   nproc    16384
    oracle   hard   nproc    16384
    oracle   soft   stack    10240
    oracle   hard   stack    32768" >> /etc/security/limits.conf
    
    $> groupadd -g 54321 oinstall
    $> groupadd -g 54322 dba
    $> groupadd -g 54323 oper
    $> useradd -u 54321 -g oinstall -G dba,oper oracle
    $> passwd oracle
    {{ 패스워드 입력 2회 }}
    $> mkdir -p /usr/local/oracle/product/12.1.0/db_1
    $> mkdir /usr/local/oraInventory
    $> chown -R oracle:oinstall /usr/local/oracle
    $> chown oracle.oinstall /usr/local/oraInventory
    $> chmod -R 775 /usr/local/oracle
  4. 시스템 변수 설정
    $> echo "
    # Oracle Settings
    export TMP=/tmp
    export TMPDIR=\$TMP
    export ORACLE_HOSTNAME=orcl.localdomain
    export ORACLE_UNQNAME=orcl
    export ORACLE_BASE=/usr/local/oracle
    export ORACLE_HOME=\$ORACLE_BASE/product/12.1.0/db_1
    export ORACLE_SID=orcl
    export PATH=/usr/sbin:\$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" >> /home/oracle/.bash_profile
  5. 시스템 구동 후 DB실행
    $> echo "
    source /etc/profile
    su - oracle -c \"\$ORACLE_HOME/bin/lsnrctl start\"
    su - oracle -c \"\$ORACLE_HOME/bin/dbstart\"
    " >> /etc/rc.d/rc.local

oracle 10g 실행시 failed to auto-start… vikrkuma_new/oracle 메시지 출력할때

오라클 실행시 “Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr” 이런 메시지가 뜨는경우 조치방법(근데 오라클은 정상적으로 잘 된다!)

image-1654535510418.png

조치사항

$> vi $ORACLE_HOME/bin/dbstart
...
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
ORACLE_HOME_LISTNER=$ORACLE_HOME 으로 교체
...

 

 

reference

oracle 10g 아카이브 모드 설정

  1. 아카이브 모드 상태 확인
    SQL> archive log list
    \\Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     1
    Current log sequence           2
    \\\\SQL> show parameter spfile
    \\NAME                                 TYPE        VALUE
    ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- ~-~-~-~-~-~-~-~-~-~-- ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
    spfile                               string      /u01/app/oracle/product/10.2.0
                                                     /db_1/dbs/spfileTSH1.ora
  2. 아카이브 모드 설정
    SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/oradata/data reopen=60' scope=spfile;
    System altered.
    \\\\SQL> ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' scope=spfile;
    System altered.
  3. Oracle 종료
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
  4. Oracle 시작
    SQL> startup mount
    .ORACLE instance started.
    \\Total System Global Area  608174080 bytes
    Fixed Size                  1220844 bytes
    Variable Size             171970324 bytes
    Database Buffers          432013312 bytes
    Redo Buffers                2969600 bytes
    Database mounted.
  5. log모드 변경
    SQL> alter database archivelog;
    Database altered.
  6. 아카이브모드 설정내용 확인
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/oradata/data
    Oldest online log sequence     1
    Next log sequence to archive   2
    Current log sequence           2
  7. DB Open
    SQL> alter database open;
    Database altered.
  8. DB 셧다운 & Cold Backup 권장
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
  9. log파일 생성확인
    SQL> ALTER SYSTEM SWITCH LOGFILE;
    \\System altered.

oracle 10g dbstart에러 조치방법

DB구동시 에러메시지 출력

[oracle@localhost ~]$ /usr/local/oracle/product/10.2.0/db_1/bin/dbstart

Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr

Processing Database instance "orcl": log file /usr/local/oracle/product/10.2.0/db_1/startup.log

조치방법

$> vi /usr/local/oracle/product/10.2.0/db_1/bin/dbstart
#ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

ORACLE_HOME_LISTNER=$ORACLE_HOME

Oracle 몇가지 사용쿼리들

  1. 오라클 테이블 스페이스를 emp2로 설정해서 생성
    SQL> create tablespace testdb
    datafile '/home/oracle/oradata/TSH1/testdb.dbf' size 500m;
  2. test_user으로 계정 생성하고 테이블 스페이스는 testdb1으로 설정
    SQL> CREATE USER test_user IDENTIFIED BY userdata1 DEFAULT TABLESPACE testdb TEMPORARY TABLESPACE TEMP;
  3. 권한주기
    SQL> GRANT connect, resource, create session,create table TO test_user;
  4. testdb에 테이블 생성
    SQL> create table emp2( empno number, ename varchar2(20), deptno number);
  5. 사용자가 가지고 있는 테이블 확인
    SQL> select table_name from user_tables;
    TABLE_NAME
    ------------------------------
    EMP2
  6. 테이블 구조 확인
    SQL> desc emp2
    
     Name                                      Null?    Type
    
     ----------------------------------------- -------- ----------------------------
    
     EMPNO                                              NUMBER
    
     ENAME                                              VARCHAR2(20)
    
     DEPTNO                                             NUMBER
  7. 테이터 insert 해보기
    SQL> insert into emp2 values(123,'test_user',456);
    
    SQL> select * from emp2;
    
         EMPNO ENAME                    DEPTNO
    
    ---------- -------------------- ----------
    
          123 test_user                     456
  8. 데이터 업데이트 하기
    SQL> update emp2 set ename='wow' where ename='ggg';
    1 row updated.
    SQL> select * from emp2;
         EMPNO ENAME                    DEPTNO
    ---------- -------------------- ----------
          123 test_user                   456
          123 wow                         102

  9. 테이블 스페이스 확인
    SQL> select tablespace_name,file_name from dba_data_files;
  10. 계정 패스워드 변경
    SQL> show user;
    USER is "test_user"
    SQL> alter user iheart identified by pass2;
  11. 15분전 쿼리 했던 데이터 복구방법
    INSERT INTO A SELECT * FROM A AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '15' MINUTE);
  12. 데이터베이스명 확인
    SQL> select name, db_unique_name from v$database;
    NAME      DB_UNIQUE_NAME
    --------- ------------------------------
    ORCL      orcl
  13. SID 확인방법
    SQL> select instance from v$thread;
    INSTANCE
    --------------------------------------------------------------------------------
    orcl

Oracle 실행절차

작업 버전 : Oracle 10g

네트워크 포트 확인 (1521포트 LISTEN 확인)

웹 서비스 구동 web서비스 기본계정
사용자이름 : sys, 암호 : 설치할 때 입력한 암호
다음으로접속 : sysdba

Oracle10g업데이트 후 에러

  1. 오라클 10g 패치 후(10.2.0.2 -> 10.2.0.5) startup 명령어로 DB를 open 하려고 할 때,
    ORA-01092: ORACLE instance terminated. Disconnection forced 에러 발생
  2. 발생 원인
    오라클 엔진만 패치되고 DB에 패치가 제대로 적용되지 않았을 경우
  3. 해결방안1. DB데이터 업데이트

    SQL> sqlplus / as sysdba
    SQL> startup upgrade;
    SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql


    *** 시간 약 15~20분걸림

    1. DB종료 후 재기동
      SQL> shutdown immediate;
      SQL> startup;
      SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
      SQL> !
    2. $ORACLE_HOME/bin에 존재하는 파일에 권한 부여
      [oracle@localhost ~] cd $ORACLE_HOME/install
      [oracle@install ~] ./changePerm.sh  
      SQL> select * from v$version;

Oracle에서 사용자Lock풀기

Oracle에서 Lock걸린 사용자 해제 절차

  1. Oracle에서 사용자 정보 확인
    SQL> select username,account_status from dba_users;
    USERNAME                       ACCOUNT_STATUS
    ------------------------------ --------------------------------
    EXFSYS                         EXPIRED & LOCKED
    DMSYS                          EXPIRED & LOCKED
    WMSYS                          EXPIRED & LOCKED
    XDB                            EXPIRED & LOCKED
    ORDPLUGINS                     EXPIRED & LOCKED
    SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
    OLAPSYS                        EXPIRED & LOCKED
    MDDATA                         EXPIRED & LOCKED
    DIP                            EXPIRED & LOCKED
    SCOTT                          EXPIRED & LOCKED
    TSMSYS                         EXPIRED & LOCKED
    TEST                           LOCKED
  2. lock된 test계정정보 수정
    SQL> alter user test account unlock;
  3. unlock 정보 확인
    SQL> select username,account_status from dba_users;
    USERNAME                       ACCOUNT_STATUS
    ------------------------------ --------------------------------
    MGMT_VIEW                      OPEN
    SYS                            OPEN
    SYSTEM                         OPEN
    DBSNMP                         OPEN
    SYSMAN                         OPEN
    TEST                           OPEN
    SCOTT                          EXPIRED
    OUTLN                          EXPIRED & LOCKED
    MDSYS                          EXPIRED & LOCKED
    ORDSYS                         EXPIRED & LOCKED
    CTXSYS                         EXPIRED & LOCKED
  4. 계정 패스워드 설정
    SQL> ALTER USER test IDENTIFIED BY testuser!!!!;