OracleDB

• Centos에서 Oracle 10G 설치
• Centos에서 oracle 11g 설치
• Centos에서 oracle 12c 설치하기
• oracle 10g 실행시 failed to auto-start… vikrkuma_new/oracle 메시지 출력할때
• oracle 10g 아카이브 모드 설정
• oracle 10g dbstart에러 조치방법
• Oracle 몇가지 사용쿼리들
• Oracle 실행절차
• Oracle10g업데이트 후 에러
• Oracle에서 사용자Lock풀기

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” 이런 메시지가 뜨는경우 조치방법(근데 오라클은 정상적으로 잘 된다!)

조치사항

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

 

 

reference

• http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php

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!!!!;