이번 주 나의 오라클 데이터베이스 스터디 주제는 오라클 데이터베이스 11g XE 설치이다.

  • 설치 환경 : CentOS 6.3
  • 설치 파일 : oracle-xe-11.2.0-1.0.x86_64.rpm.zip (오라클 홈페이지에서 다운로드)

오라클 홈페이지에서 다운로드 한 파일을 확인해 보자.

# ls -l
합계 308492
-rw-r--r-- 1 oracle oracle 315891481 2016-06-21 19:35 oracle-xe-11.2.0-1.0.x86_64.rpm.zip

unzip 명령어로 zip 압축을 푼다.

# unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
Archive:  oracle-xe-11.2.0-1.0.x86_64.rpm.zip
   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

실제 파일은 Disk1 안에 들어있는 것 같다.

# cd Disk1
# ls -l
합계 309896
-rw-rw-r-- 1 root root 317320273 2011-08-29 14:57 oracle-xe-11.2.0-1.0.x86_64.rpm
drwxr-xr-x 2 root root      4096 2011-08-29 14:57 response
drwxrwxr-x 2 root root      4096 2011-08-29 14:57 upgrade

본격적으로 rpm 설치를 진행해 보자.

# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
오류: Failed dependencies:
libaio >= 0.3.104 is needed by oracle-xe-11.2.0-1.0.x86_64

아.. 디펜던시가 걸린 패키지가 있는 것 같다. libaio를 설치하고 간다.

# yum install libaio
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.oasis.onnetcorp.com
 * extras: mirror.oasis.onnetcorp.com
 * updates: mirror.oasis.onnetcorp.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package libaio.x86_64 0:0.3.107-10.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================
 Package             Arch                Version                       Repository         Size
===============================================================================================
Installing:
 libaio              x86_64              0.3.107-10.el6                base               21 k

Transaction Summary
===============================================================================================
Install       1 Package(s)

Total download size: 21 k
Installed size: 34 k
Is this ok [y/N]: y
Downloading Packages:
libaio-0.3.107-10.el6.x86_64.rpm                                        |  21 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : libaio-0.3.107-10.el6.x86_64                                                1/1
  Verifying  : libaio-0.3.107-10.el6.x86_64                                                1/1

Installed:
  libaio.x86_64 0:0.3.107-10.el6

Complete!

libaio 설치 완료. 다시 rpm 설치를 수행한다.

# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
준비 중...               ########################################### [100%]
/var/tmp/rpm-tmp.TCRVXo: line 186: bc: command not found
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
/var/tmp/rpm-tmp.ddKna3: line 76: bc: command not found
/var/tmp/rpm-tmp.ddKna3: line 77: bc: command not found
/var/tmp/rpm-tmp.ddKna3: line 78: [: -gt: unary operator expected
/var/tmp/rpm-tmp.ddKna3: line 82: bc: command not found
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.

bc? 관련 에러가 났지만 튕기지 않고 완료되었다. (아마.. 잘 된 것 같다) 시키는대로 계속 해보자.

# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press  to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:9080

Specify a port that will be used for the database listener [1521]:2521

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done
Configuring database...
Database Configuration failed.  Look into /u01/app/oracle/product/11.2.0/xe/config/log for details

 

Database Configuration failed.  Look into /u01/app/oracle/product/11.2.0/xe/config/log for details 에러 발생.

참고로 log 디렉토리 안에 로그 내용들은 대략 다음과 같다.

Session ID: 0 Serial number: 0

create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' FROM pfile='/u01/app/oracle/product/11.2.0/xe/config/scripts/init.ora'
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00116: syntax error at 'sessions' following '='

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

BEGIN utl_recomp.recomp_serial(); END;

*
ERROR at line 1:
ORA-01034: ORACLE not available

 

여기부터가 고난기이다. 정말 몇시간을 구글링했는데, 대체적으로 아래와 같은 해결책들이 제시되었다.

  1.   swap 메모리를 추가하라.
  2.   /etc/hosts에 호스트 정보를 넣어라.
  3.   bc 패키지를 깔아라.

다 했다. 다 안된다. swap도 계속 늘려가면서 해봤지만 안된다.

아래는 swap 추가이다.

# mkdir /swap
# dd if=/dev/zero of=/swap/swapfile bs=1024 count=2097152
2097152+0 records in
2097152+0 records out
2147483648 bytes (2.1 GB) copied, 127.844 s, 16.8 MB/s
# cd /swap
# mkswap swapfile
mkswap: swapfile: warning: don't erase bootbits sectors
        on whole disk. Use -f to force.
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=2c6a1277-a5c3-4f48-b64c-7010dd161c72
# swapon swapfile

 

아래는 bc 설치 화면이다.

# yum install bc
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.oasis.onnetcorp.com
 * extras: mirror.oasis.onnetcorp.com
 * updates: mirror.oasis.onnetcorp.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package bc.x86_64 0:1.06.95-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================
 Package          Arch                 Version                        Repository          Size
===============================================================================================
Installing:
 bc               x86_64               1.06.95-1.el6                  base               110 k

Transaction Summary
===============================================================================================
Install       1 Package(s)

Total download size: 110 k
Installed size: 212 k
Is this ok [y/N]: y
Downloading Packages:
bc-1.06.95-1.el6.x86_64.rpm                                             | 110 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : bc-1.06.95-1.el6.x86_64                                                     1/1
  Verifying  : bc-1.06.95-1.el6.x86_64                                                     1/1

Installed:
  bc.x86_64 0:1.06.95-1.el6

Complete!

 

그런데 어느 구글링 결과에서, "bc 패키지 설치 후에 다시 rpm 설치를 해야 정상적으로 설치된다"라는 말이 있었다.

문득 스쳐지나가는 것은 아까 rpm 설치 시에 이상한 bc 관련 에러가 났었던 것. 다시 보고 가자.

Executing post-install steps...
/var/tmp/rpm-tmp.ddKna3: line 76: bc: command not found
/var/tmp/rpm-tmp.ddKna3: line 77: bc: command not found
/var/tmp/rpm-tmp.ddKna3: line 78: [: -gt: unary operator expected
/var/tmp/rpm-tmp.ddKna3: line 82: bc: command not found

그래서 rpm -e oracle-xe-11.2.0-1.0.x86_64로 삭제한 후, 다시 rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm로 설치, 그리고 다시 /etc/init.d/oracle-xe configure하여, 아래와 같이 configure 과정이 성공적으로 완료되었다!

# /etc/init.d/oracle-xe configure

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press  to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:9080

Specify a port that will be used for the database listener [1521]:2521

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

 

이제 DB에 접속해 보자.

$ cd /u01/app/oracle/product/11.2.0/xe/bin
$ . ./oracle_env.sh
$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 22 08:51:27 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

아래는 Oracle 11g XE GUI 원격 접속을 위한 조치이다.

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL procedure successfully completed.

이미 생성되어 있는 계정들을 살펴볼까?

SQL> SELECT USERNAME, USER_ID, DEFAULT_TABLESPACE, CREATED FROM DBA_USERS;

USERNAME                USER_ID DEFAULT_TABLESPACE             CREATED
-------------------- ---------- ------------------------------ ------------------
SYS                           0 SYSTEM                         28-AUG-11
SYSTEM                        5 SYSTEM                         28-AUG-11
ANONYMOUS                    35 SYSAUX                         28-AUG-11
APEX_PUBLIC_USER             45 SYSTEM                         28-AUG-11
APEX_040000                  47 SYSAUX                         28-AUG-11
XS$NULL              2147483638 SYSTEM                         28-AUG-11
OUTLN                         9 SYSTEM                         28-AUG-11
FLOWS_FILES                  44 SYSAUX                         28-AUG-11
MDSYS                        42 SYSAUX                         28-AUG-11
CTXSYS                       32 SYSAUX                         28-AUG-11
XDB                          34 SYSAUX                         28-AUG-11
HR                           43 USERS                          28-AUG-11

12 rows selected.

Tablespace는 뭐가 있을까?

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

마지막으로 Datafile이다.

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------------------
/u01/app/oracle/oradata/XE/users.dbf     USERS
/u01/app/oracle/oradata/XE/sysaux.dbf    SYSAUX
/u01/app/oracle/oradata/XE/undotbs1.dbf  UNDOTBS1
/u01/app/oracle/oradata/XE/system.dbf    SYSTEM