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 설치
-
패키지 설치
$> 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
- Centos 6 64비트만
- 커널 파라미터 설정
$> 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
- 계정설정
$> 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
- 설치시 OS정보 변경 수행
$> mv /etc/redhat-release /etc/redhat-release_ori $> echo "redhat-4" > /etc/redhat-release
- 시스템 설정 적용
$> 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
- 시스템 기동시 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 설치
-
패키지 설치하기
$> 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
- Centos6 64비트에서는 추가 패키지 설치
- 커널 파라미터 적용
$> 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
- 계정 설정
$> 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
- OS정보 수정
mv /etc/redhat-release /etc/redhat-release_ori echo "redhat-4" > /etc/redhat-release
- 시스템 변수 설정
$> 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
- 시스템 구동시 오라클 구동
$> 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 설치하기
- 패키지 설치하기
$> 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
- 커널파라미터 수정
$> 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
- 계정정보 설정
$> 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
- 시스템 변수 설정
$> 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
- 시스템 구동 후 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
oracle 10g 아카이브 모드 설정
- 아카이브 모드 상태 확인
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
- 아카이브 모드 설정
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.
- Oracle 종료
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
- 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.
- log모드 변경
SQL> alter database archivelog; Database altered.
- 아카이브모드 설정내용 확인
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
- DB Open
SQL> alter database open; Database altered.
- DB 셧다운 & Cold Backup 권장
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
- 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 몇가지 사용쿼리들
- 오라클 테이블 스페이스를 emp2로 설정해서 생성
SQL> create tablespace testdb datafile '/home/oracle/oradata/TSH1/testdb.dbf' size 500m;
- test_user으로 계정 생성하고 테이블 스페이스는 testdb1으로 설정
SQL> CREATE USER test_user IDENTIFIED BY userdata1 DEFAULT TABLESPACE testdb TEMPORARY TABLESPACE TEMP;
- 권한주기
SQL> GRANT connect, resource, create session,create table TO test_user;
- testdb에 테이블 생성
SQL> create table emp2( empno number, ename varchar2(20), deptno number);
- 사용자가 가지고 있는 테이블 확인
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ EMP2
- 테이블 구조 확인
SQL> desc emp2 Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER ENAME VARCHAR2(20) DEPTNO NUMBER
- 테이터 insert 해보기
SQL> insert into emp2 values(123,'test_user',456); SQL> select * from emp2; EMPNO ENAME DEPTNO ---------- -------------------- ---------- 123 test_user 456
- 데이터 업데이트 하기
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
- 테이블 스페이스 확인
SQL> select tablespace_name,file_name from dba_data_files;
- 계정 패스워드 변경
SQL> show user; USER is "test_user" SQL> alter user iheart identified by pass2;
- 15분전 쿼리 했던 데이터 복구방법
INSERT INTO A SELECT * FROM A AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '15' MINUTE);
- 데이터베이스명 확인
SQL> select name, db_unique_name from v$database; NAME DB_UNIQUE_NAME --------- ------------------------------ ORCL orcl
- SID 확인방법
SQL> select instance from v$thread; INSTANCE -------------------------------------------------------------------------------- orcl
Oracle 실행절차
작업 버전 : Oracle 10g
네트워크 포트 확인 (1521포트 LISTEN 확인)
웹 서비스 구동 web서비스 기본계정
사용자이름 : sys, 암호 : 설치할 때 입력한 암호
다음으로접속 : sysdba
Oracle10g업데이트 후 에러
- 오라클 10g 패치 후(10.2.0.2 -> 10.2.0.5) startup 명령어로 DB를 open 하려고 할 때,
ORA-01092: ORACLE instance terminated. Disconnection forced 에러 발생 - 발생 원인
오라클 엔진만 패치되고 DB에 패치가 제대로 적용되지 않았을 경우 -
해결방안1. DB데이터 업데이트
SQL> sqlplus / as sysdba SQL> startup upgrade; SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
*** 시간 약 15~20분걸림- DB종료 후 재기동
SQL> shutdown immediate; SQL> startup; SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SQL> !
- $ORACLE_HOME/bin에 존재하는 파일에 권한 부여
[oracle@localhost ~] cd $ORACLE_HOME/install [oracle@install ~] ./changePerm.sh SQL> select * from v$version;
- DB종료 후 재기동
Oracle에서 사용자Lock풀기
Oracle에서 Lock걸린 사용자 해제 절차
- 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
- lock된 test계정정보 수정
SQL> alter user test account unlock;
- 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
- 계정 패스워드 설정
SQL> ALTER USER test IDENTIFIED BY testuser!!!!;