Yi

This paper (HOWTO) describes step-by-step installation of Oracle 9i database software on Fedora Core 2, 3, 4, 5 and 6.

This paper covers following steps:

• Pre-Instalation Tasks
• Download & Install
• Post-Instalation Tasks
• Common Installation Errors

Pre-Instalation Tasks

1. Create oracle User Account

Login as root and create te user oracle which belongs to dba group.
2. Setting System parameters
Edit the /etc/sysctl.conf and add following lines: Note: You need execute "sysctl -p" or reboot system to apply above settings.

Edit the /etc/security/limits.conf file and add following lines:
3. Setting Oracle Enviroment
Edit the /home/oracle/.bash_profile file and add following lines:

Settings for Fedora Core 2,3,4 only. For Fedora Core 5 and 6 use the next settings.
Settings for Fedora Core 5 and 6 only. For Fedora Core 2,3,4 use the above settings. Note setting of LD_ASSUME_KERNEL will cause incorrect functionality of FC5 and FC6.
Save the .bash_profile and execute following commands for load new enviroment:
4. Create base directory for Oracle

Login as root and create base directory for Oracle ($ORACLE_BASE).

Download & Install

1. Download and install required .rpm packages

Some additional packages are required for succesful instalation of Oracle software. To check whether required packages are installed on your operating system use following command: Note: You don't need glibc-kernheaders installed on FC6 as this package is not shipped with FC6.
If some package is not installed download it from Fedora project website or direct from Core 2, Core 3, Core 4, Core 5, Core 6 locations.

This step is required for Fedora Core 3 and 4 only. Fedora Core 3 is shipped with compat-gcc-8.3.3.4. Fedora Core 4 is shipped with compat-gcc-32-3.2.3. These package are GCC 3.x compiler which are not useful for succesful inatallation. Check whether compat-gcc-8.3.3.4 or compat-gcc-32-3.2.3 is installed. If so then uninstall it using folowing command:

rpm -e compat-gcc

Download the compat-gcc-7.3-2.96.126.i386.rpm package from Core 2 location or from here.

Install the required packages using the rpm command (Note version of packages may change in newer release, so versions for all packages (except compat-gcc package) used in next command are just for example.):

For Fedora Core 4, 5 and 6 only:Install the required additional packages using the rpm command:
If all required packages were installed succesfuly then login as root and switch the GCC3 compiler binary with GCC2 compiler binary as following:
2. Download the Java Runtime Enviroment (j2re-1_3_1_15-linux-i586.bin) from the Sun website. Keep in mind you need to download j2re1.3.1_11 or higher (Note: Install JRE 1.3.1_1x version only).

Login as root and make the the file executable and then execute it. When the JRE is exracted move the "jre1.3.1_15" di rectory to "/opt" directory.
3. Download the Oracle 9i (9.2.0.4) software from Oracle website.
Extract the files using following command:
When all archives were extracted you've got three directories Disk1, Disk2 and Disk3.
Edit the Disk1/install/linux/oraparam.ini and modify JRE_LOCATION variable and set path to our JRE installation from Step 2.

JRE_LOCATION=/opt/jre1.3.1_15

4. Start the Oracle software installation process.

Now the system is prepared for Oracle software installation. To start the installation process execute the following commands:
When network configuration assistant and database configuration assistant has failed during st artup then do following steps:

Post-Instalation Tasks

1. Switch back the GCC binaries
2. Change of JRE path in Oracle Universal Installer

Edit the $ORACLE_BASE/oui/oraparam.ini file and modify the value of JRE_LOCATION to /opt/jre1.3.1_15

3. (Optional) You may consider to use rlwrap for comfortable work with sqlplus. RPM package for Fedora Core (x86) distribution you can download here.

Common Installation Errors

Unable to load native library: /tmp/OraInstall2005-01-08_11-11-34AM/jre/lib/i386/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference
Solution: Install new JRE 1.3.1 version. Edit the Disk1/install/linux/oraparam.ini and set path to new JRE for JRE_LOCATION variable. For more information see Download & Install section.

/tmp/OraInstall2005-04-26_11-38-19AM/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
Solution: Install the xorg-x11-deprecated-libs package (for >=FC4 versions). For FC5 and later install libXp package.

/tmp/OraInstall2005-06-14_03-46-25PM/jre/bin/i386/native_threads/java: error while loading shared libraries: /tmp/OraInstall2005-06-14_03-46-25PM/jre/lib/i386/native_threads/libhpi.so: cannot restore segment prot after reloc: Permission denied
Solution: Modify /etc/selinux/config and change value of SELINUX to "disabled" and reboot computer.

Starting Oracle Intelligent Agent.../opt/oracle/920/bin/dbsnmpwd: line 156: 1393 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/opt/oracle/920/bin/dbsnmpwd: line 156: 1405 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/opt/oracle/920/bin/dbsnmpwd: line 156: 1416 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
/opt/oracle/920/bin/dbsnmpwd: line 156: 1427 Segmentation fault nohup $ORACLE_HOME/bin/dbsnmp $* >>$DBSNMP_WDLOGFILE 2>&1
Solution: Download and apply patch nr.: 3238244 from http://metalink.oracle.com.

Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using 'localhost:0.0' as the value of the DISPLAY variable.
Solution: Execute "export DISPLAY=:0.0" (as oracle user) and "xhost +" as user who has opened X session (for example logged in KDE, GNOME, etc.). If the value is other than 127.0.0.1 or localhost you should "xhost +" on client machine.

Error in invoking target install of makefile /opt/oracle/920/ctx/lib/ins_ctx.mk
Error in invoking target install of makefile /opt/oracle/920/precomp/lib/ins_precomp.mk
Error in invoking target install of makefile /opt/oracle/920/plsql/lib/ins_plsql.mk
Error in invoking ntcontab.o of makfile /opt/oracle/920/network/lib/ins_net_client.mk
Solution: Install the compat-gcc-7.3-2.96.126.i386.rpm (gcc 2.96) package and change the GCC binaries. For more information see Download & Install section. In case of Fedora Core 5 take look at next error message.

sed: -e expression #1, char 7: unterminated `s' command error in $ORACLE_HOME/install/make.log
Solution: There is probably bug in bash (FC5 only). Download and install bash package from FC4. I have prepared this package (builded from FC4 src.rpm) for FC5 - Download.
Install it using "rpm -ivh bash-3.0-31.i386.rpm --force".

Initializing Java Virtual Machine from /tmp/OraInstall2006-10-25_01-43-30PM/jre/bin/java. Please wait... /tmp/OraInstall2006-10-25_01-43-30PM/jre/bin/i386/native_threads/java: error while loading shared libraries: libstdc++-libc6.1-1.so.2: cannot open shared object file: No such file or directory - after ./runInstaller execution.
Solution:
su -
# cd /usr/lib
# ln -s libstdc++-3-libc6.2-2-2.10.0.so libstdc++-libc6.1-1.so.2

Start ./runInstaller again.


Last update: 25-10-2006. Comments, suggestions, questions, errors (also grammatical :) )? Feel

The Index_DESC Hint
The INDEX_DESC hint causes indexs to be scanned in descending order (of their indexed value or order), which is the opposite of the INDEX and INDEX_ASC hints. This hint is overridden when the query has multiple tables, because the index needs to be used in the normal ascending order to be joined to the other table in the query. Some restrictions for this include that it does not work for bitmap indexes or for descending indexes, that it causes the index to be scanned in ascending order, and that it does not work across partitioned index boundaries but performs a descending index scan of each partition.

Syntax
select /*+ INDEX_DESC (table index1, index2...) * column1, ...

Example
select /*+ INDEX_DESC(emp deptno_idx) */ empno, ename, deptno
from emp
where deptno = 1;

The INDEX_DESC processes an index in descending order of how it was built. This hint will not be used if more than one table exists in the query.

 

-----------------------------------------------------------------------------------------

출처는 잘모르겠습니다~

문제시 삭제처리하겠습니다^^

개발을 하다보면 SQL문을 자주 사용하게 되는데...
최소한 Toad등에서 Query에 대해 Plan한번만 떠 보세요... 그러면 그 쿼리가 어느 정도의 성능을 내는지는 대충 짐작하실 수 있습니다. 그런 다음 테이블의 인덱스를 적절히 사용하는지 확인만 하면 최소한 나쁜 쿼리는 작성하지 않게 됩니다. (인덱스를 사용하는 경우가 최선의 성능을 보장 하지는 않습니다)

아래 내용을 참고하시고 사용하는 DB가 오라클이라면 반드시 힌트에 대해 관심을 가지시기 바라구요 옵티마이저 수행 경로가 정상적인 경우와 비정상인 경우 성능 차이는 장난 아닙니다. 심지어 10배 이상 나는 경우도 있구요,,,
(힌트는 Oracle Optiomizer의 수행경로를 변경 시키는 역할을 합니다.)

인덱스 힌트를 사용하게 되면 오라클 옵티마이저는 기술된 인덱스를 써야 한다는 것을 알게되며 이 경우 Table Full Scan을 하지 않게 되죠... 물론 사용되는 인덱스에 대해 COST는 계산하지 않고 존재하는 인덱스라면 사용을 한다는 이야깁니다...

만약 인덱스 힌트가 사용되지 않았지만 인덱스를 사용할 수 있는 경우라면 여러 인덱스중 COST가 적게 드는 인덱스를 하나 선택하여 사용하게 되구요...

사용 형식은 아래와 같구요...

/*+ INDEX ( table [index [index]...] ) */

table은 테이블 명이나 Alias 명 입니다.
index에는 사용되는 인덱스명을 쓰시면 되구요,

예)
select /*+ INDEX(idx_emp_job) */
       ename,
       job
from emp
where job= '프로그래머';

DELETE /*+ INDEX(idx_emp_status)*/
FROM emp_status
WHERE status = '휴직';

*** INDEX_ASC : INDEX 힌트와 거의 유사하며 단지 다른 부분이라면 인덱스 영역의 검색되는 데이터가 오름차순으로 Scan 되는 것이죠...

INDEX_ASC
/*+ INDEX_ASC(table index[index .. index]) */

*** INDEX_DESC : 이 힌트 역시 INDEX힌트와 유사하며 단지 인덱스 영역의 검색되는 데이터가 내림차순으로 Scan 되는 것이죠...

/*+ INDEX_DESC (table index[index .. index]) */

위 INDEX_ASC나 INDEX_DESC를 적절히 사용하시면 ORDER BY 구문을 사용하지 않아도 되는 경우가 대부분 입니다.(ORDER BY를 많은 건의 데이터에 대해 사용하시면 성능에 악 영향을 줍니다)

emp 테이블에 ename으로 인덱스가 있다면...

select ename, sal
from emp
order by ename

==>

select /*+ index_asc(emp) */
         ename, sal
from emp
where ename > '가'  -- 한글 이름을 사용한다고 했을 때
(이 경우 order by를 사용하지 않았지만 데이터는 ename으로 오름차순 정렬되어 나옵니다)

*** FULL : 인덱스 사용하지 않고 Table을 FULL Scan하라는 힌트
만약 성별처럼 "M" 아니면 "F"만 가지는 경우 분포도가 50%이므로 인덱스를 사용하는 것보다 인덱스를 사용하지 않는 경우가 성능에 효율적일 수 있습니다. 이러한 경우 사용하는 힌트죠...

사용 형식
/*+ FULL (table) */

select /*+ FULL(emp) */ ename, sal
from emp
where sex = 'M'

*** NO_INDEX : 기술된 테이블에 대해서는 인덱스를 사용하지 말라는 의미

select /*+ NO_INDEX(emp) */ ename, sal
from emp where sex = 'M';

//root 로 로그인
#su - 

 /*커널 매개변수 확인 및 설정(변수 값이 클경우 안바꿔도 상관없음)
 #/sbin/sysctl -p
 #/sbin/sysctl -a | grep shm
 #/sbin/sysctl -a | grep sem
 #/sbin/sysctl -a | grep file-max
 #/sbin/sysctl -a | grep ip_local_port_range
 */

 //매개변수 설정
#vi /etc/sysctl.conf 

 #kernel.shmall = 2097152
 #kernel.shmmax = 2147483648
 kernel.shmmni = 4096
 # semaphores: semmsl, semmns, semopm, semmni
 kernel.sem = 250 32000 100 128
 #fs.file-max = 65536
 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

//매개변수 적용
#/sbin/sysctl -p 

//제한값 설정
#vi /etc/security/limits.conf  

 *               soft    nproc   2047
 *               hard    nproc   16384
 *               soft    nofile  1024
 *               hard    nofile  65536

//설정
#vi /etc/pam.d/login 

 session    required     /lib/security/pam_limits.so

#SELINUX=disabled
 

//필수 패키지 설치
#yum install setarch-2* make-3* glibc-2* libaio-0* compat-libstdc++-33-3* compat-gcc-34-3*
compat-gcc-34-c++-3* gcc-4* libXp-1* openmotif-2* compat-db-4*

//오라클 그룹 생성
#groupadd dba

//오라클 유저 생성
#useradd -g dba oracle
#passwd oracle  <--패스워드는 각자알아서

//설치디렉토리 생성
#mkdir -p /u01/app/oracle/product/10.2.0/db_1

//권한 설정
#chown -R oracle:dba /u01

//네트워크 확인
#xhost +127.0.0.1

//release 명 변경
#vi /etc/redhat-release
 centOS 삭제후 -> redhat-4 추가

//login to ORACLE

//oracle 환경 변수 설정
$vi /home/oracle/.bash_profile

 # Oracle Settings
 TMP=/tmp; export TMP
 TMPDIR=$TMP; export TMPDIR

 ORACLE_BASE=/u01/app/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
 NLS_LANG=American_America.AL32UTF8; export NLS_LANG 

 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

//oracle 설치파일 download

http://www.oracle.com/technology/software/products/database/index.html

10201_database_linux32.zip

압축풀고 압축푼 폴더에가서
 

//oracle 설치 시작
$./runInstaller