Install Oracle Express Edition 11g on Oracle Linux 6.x - Step by Step

1. Introduction

This post show how to install Oracle Express 11g on Oracle Linux 6.x

2. Step-by-Step

This step-by-step is divided into 3 parts:

• Part I - Download Oracle Express binnary
• Part II - Check network configurations
• Part III - Install binnary files downloaded
• Part IV - Configure Oracle Database
• Part V - Advanced configuration and customization

2.1. Part I - Download Oracle Express binnary

• Download Oracle Express 11g (.zip) install from  Oracle Site http://www.oracle.com/us/sitemaps/index.html

2.2. Part II -Check network configurations

To avoid problems during execution I recomend you to check your network configurations before executing install script.

a) Step #1: Hostname and DomainName must be resolved

• Oracle uses HOSTNAME into configurations, so HOSTNAME must be accessible for ping (if not correct it!)

# cat /etc/sysconfig/network | grep 'HOSTNAME' | awk -F"=" '{ print $2 }' | xargs ping $1

If output of this command is "ping unknown host ..." Then you will get errors continuing !!! Go back and configure network parameters until your hostname respond ping from this command:

• You can get hostname by command "hostname" or "cat /proc/sys/kernel/hostname"
• You configure hostname editing file "vim /etc/sysconfig/network". This needs reboot after configuration
• You can edit /etc/hosts and add exactly your "hostname" on entry for 127.0.0.1

# hostname
oracle-linux64.localdomain
# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain oracle-linux64 oracle-linux64.localdomain

2.3. Part III - Install binnary files downloaded

• Step#1: Unzip Oracle XE installer 

# cd /root
# unzip oracle-xe-11.2.0-1.0.x86_64.rpm

• Step#2: Install and RPM packagres 

# cd ./Disk1
# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database

• Step#3: Checking installation

# du /u01 --max-depth 5
584248  /u01/app/oracle/product/11.2.0/xe
584248  /u01/app/oracle/product/11.2.0
584248  /u01/app/oracle/product
584248  /u01/app/oracle
584248  /u01/app
584248  /u01
# cd /u01/app/oracle/product/11.2.0/xe/config/log
# ls -la
drwxr-xr-x.   3 root root 4096 Sep  5 11:51 .

dr-xr-xr-x.  26 root root 4096 Sep  5 11:51 .

• Step#4: Execute post install script. Accept all default configurations (HTTP Port, Listener Port). Enter a valid password for SYS and SYSTEM. Enter default 'y'es to automatically starts. And WAIT +/- 5 minutes

# /etc/init.d/oracle-xe configure
   :
A valid HTTP port for the Oracle Application Express [8080]
A valid port for the Oracle database listener [1521]
A password for the SYS and SYSTEM administrative user accounts
Confirm password for SYS and SYSTEM administrative user accounts
Whether you want the database to start automatically when the computer starts (y/n) [y]

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

• Step#5: Check database process are up and running after install

# ps -ef | grep 'oracle'
oracle   17257     1  0 12:14 ?        00:00:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit
oracle   26612     1  0 12:20 ?        00:00:00 xe_pmon_XE
oracle   26614     1  0 12:20 ?        00:00:00 xe_psp0_XE
oracle   26616     1  0 12:20 ?        00:00:00 xe_vktm_XE
oracle   26620     1  0 12:20 ?        00:00:00 xe_gen0_XE
oracle   26622     1  0 12:20 ?        00:00:00 xe_diag_XE
oracle   26624     1  0 12:20 ?        00:00:00 xe_dbrm_XE
oracle   26626     1  0 12:20 ?        00:00:00 xe_dia0_XE
oracle   26628     1  0 12:20 ?        00:00:00 xe_mman_XE
oracle   26630     1  0 12:20 ?        00:00:00 xe_dbw0_XE
oracle   26632     1  0 12:20 ?        00:00:00 xe_lgwr_XE
oracle   26634     1  0 12:20 ?        00:00:00 xe_ckpt_XE
oracle   26636     1  0 12:20 ?        00:00:00 xe_smon_XE
oracle   26638     1  0 12:20 ?        00:00:00 xe_reco_XE
oracle   26640     1  0 12:20 ?        00:00:00 xe_mmon_XE
oracle   26642     1  0 12:20 ?        00:00:00 xe_mmnl_XE
oracle   26644     1  0 12:20 ?        00:00:00 xe_d000_XE
oracle   26646     1  0 12:20 ?        00:00:00 xe_s000_XE
oracle   26648     1  0 12:20 ?        00:00:00 xe_s001_XE
oracle   26650     1  0 12:20 ?        00:00:00 xe_s002_XE
oracle   26652     1  0 12:20 ?        00:00:00 xe_s003_XE
oracle   26733     1  0 12:20 ?        00:00:01 xe_vkrm_XE
oracle   26735     1  0 12:20 ?        00:00:00 xe_qmnc_XE
oracle   26749     1  0 12:20 ?        00:00:00 xe_cjq0_XE
oracle   26783     1  0 12:20 ?        00:00:00 xe_q000_XE
oracle   26785     1  0 12:20 ?        00:00:00 xe_q001_XE
root     26840 26816  0 12:28 pts/0    00:00:00 grep oracle

• Step#6: Reboot and check if Oracle XE instance started up automatically

# /etc/init.d/oracle-xe stop
Shutting down Oracle Database 11g Express Edition instance.
Stopping Oracle Net Listener.

# reboot

login as: root

root@10.3.0.9's password:

Last login: Fri Sep  5 12:25:19 2014 from 10.3.0.67
#

# ps -ef | grep 'oracle'

oracle    1500     1  0 12:38 ?        00:00:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit

oracle    1564     1  0 12:39 ?        00:00:00 xe_pmon_XE

   :        :      :  :   :                :        :

2.4. Part IV - Configure Oracle Database

• Step#1: Setting the Oracle Database XE Environment Variables (oracle_env.sh )

login as: root
root@10.3.0.9's password:
Last login: Fri Sep  5 14:14:33 2014 from 10.3.0.67
# . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
# sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 5 14:23:52 2014

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

Enter user-name: SYS/******** AS SYSDBA

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

SQL> select tablespace_name from dba_tablespaces;

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

SQL> 

• Step#2: Configure manually NLS_LANGUAGE on oracle_env.sh 

# cd /u01/app/oracle/product/11.2.0/xe/bin
# cp oracle_env.sh oracle_env.sh.bkp.2014-09-05
# vim oracle_env.sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

export ORACLE_SID=XE

#export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

export PATH=$ORACLE_HOME/bin:$PATH

echo ""

echo "Oracle Environment Variables Set ..."

echo ""

echo "ORACLE_HOME=$ORACLE_HOME"

echo "ORACLE_SID=$ORACLE_SID"

echo "NLS_LANG=$NLS_LANG"

echo "PATH=$PATH"

echo ""

• Step#3: Configure 'oracle' .bash_profile to automatically set environment variables 

login as: root

root@10.3.0.9's password:

Last login: Fri Sep  5 14:14:33 2014 from 10.3.0.67

# 
# su - oracle

# vim ~/.bash_profile
# .bash_profile

source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

• Step#4: Making Oracle Database XE Available to Remote Clients

a) Enable Oracle XE to listen for remote clients

# sqlplus '/ as sysdba'
SQL> 
SQL> exec dbms_xdb.setListenerLocalAccess(false)

PL/SQL procedure successfully completed.

SQL> commit;
SQL> 


b) Check if Iptables/Firewall accept remote Oracle Connections

# iptables -nvL --line-numbers
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1      588 39946 ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2        0     0 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0
3       56  3922 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0
4        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5      355 51380 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1        0     0 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT 462 packets, 92468 bytes)
num   pkts bytes target     prot opt in     out     source               destination
[root@oracle-linux64 ~]#

As we can see, there is a INPUT rule #1 that accept all sources/destination but only in some states. And ther is a INPUT rule #5 that reject everything. So, we have to disable firewall or add iptable rule to accept Oracle port 1521.

• Option #1: To disable Iptable do this:

# service iptables stop

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Unloading modules:                               [  OK  ]

# chkconfig iptables off

• Option #2: To keep Iptables running and add a new rule to accept Oracle Connection, HTTP 8080 do this:

# iptables -I INPUT 1 -p tcp --dport 1521 -j ACCEPT

# iptables -I INPUT 2 -p tcp --dport 8080 -j ACCEPT

# service iptables save

iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]

# service iptables restart

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Unloading modules:                               [  OK  ]

iptables: Applying firewall rules:                         [  OK  ]

• Step#4: Enable Oracle Application Express 

SQL>
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

SQL> COMMIT;
SQL> SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
                      
DBMS_XDB.GETHTTPPORT()
----------------------
                  8080
                      
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL>

2.5. Part V - Advanced configuration and customization of Oracle XE

I've added this part because when I tried to restore my database, I'd problems with Character Set. So I decided to add this advanced configuration topic.

2.5.1. How to force Oracle XE to "reset" configuration and reconfigure

It is an Oracle XE script premisse that the file "/etc/sysconfig/oracle-xe" exists then database is configured. So, let's  use a little cheat trick to hide file from scripts and reset configurations.

• Step#1: Stop Service 

# echo "Stop service ..."

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

#

# echo "Try to configure, but it's already configured :-( ..."

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

Oracle Database 11g Express Edition is already configured

# echo "Force reconfigure ..."

# mv /etc/sysconfig/oracle-xe /etc/sysconfig/oracle-xe.bkp.2014-09-11

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

• Step#2: How to customize configurations script

There are majors scripts used during installation:

• /etc/init.d/oracle-xe: Here you can change configurations like ORACLE_SID=XE
• $ORACLE_HOME/config/scripts/XE.sh: creates "/u01/app/oracle/admin/XE/' directory structures. Here you can change path.
• $ORACLE_HOME/config/scripts/XE.sql: Call "CloneRmanRestore.sql", rmanRestoreDatafiles.sql", "cloneDBCreation.sql", "postScripts.sql" and "postDBCreation.sql"
• $ORACLE_HOME/config/scripts/init.ora: Default Pfile used during database clone

Oracle XE installation scripts use template database restored by RMAN. This backup brings some schema ( 'HR', 'CTXSYS', 'OUTLN', 'MDSYS', 'FLOWS_FILES') used as demo. Owners users comes locked by default.

2.5.2. How to create Oracle XE database manually

• Step#1: Stop Service

#

# echo "Logged as 'root' shutdown database (if running) ..."

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

Shutting down Oracle Database 11g Express Edition instance.

Stopping Oracle Net Listener.

• Step#2: Backup 'init.ora' and 'old datafiles' from existing database (if exists )

# echo "Login or sudo as 'oracle' ..."

# su - oracle

$

$ echo "Backup init.ora and old datafiles before custom changes ..."

$ 

$ ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

$ cp $ORACLE_HOME/config/scripts/init.ora $ORACLE_HOME/config/scripts/init.ora.bkp.2014-09-12

$ mv /u01/app/oracle/oradata/XE /u01/app/oracle/oradata/XE.bkp.2014-09-12

$ mkdir /u01/app/oracle/oradata/XE

• Step#3: Customize 'init.ora'

$

$ echo "Customize init.ora (if necessary) ..."

$ vim $ORACLE_HOME/config/scripts/init.ora

  :

  :

###########################################

# Database Identification

###########################################

db_name=XE

  :
#memory_target=414187520
memory_target=780824576

  :

• Step#4: Create a new database instance

$

$ echo "Setting Oracle Environment variables ..."

$ source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

$

$ echo "Run SqlPlus but do *not* connect because database will be recreated ..."

$

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 12 16:24:21 2014

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

SQL> CONN / AS SYSDBA

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/xe/config/scripts/init.ora';

ORACLE instance started.

Total System Global Area  780824576 bytes

Fixed Size                  2230568 bytes

Variable Size             461375192 bytes

Database Buffers          314572800 bytes

Redo Buffers                2646016 bytes

SQL>

SQL> SPOOL /u01/app/oracle/product/11.2.0/xe/config/log/CreateManuallyDB.log

SQL>

SQL> CREATE DATABASE XE

USER SYS IDENTIFIED BY oracle123

USER SYSTEM IDENTIFIED BY oracle123

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/XE/redo01.log') SIZE 100M,

        GROUP 2 ('/u01/app/oracle/oradata/XE/redo02.log') SIZE 100M,

        GROUP 3 ('/u01/app/oracle/oradata/XE/redo03.log') SIZE 100M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET WE8ISO8859P15

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE        '/u01/app/oracle/oradata/XE/system01.dbf' SIZE 1G REUSE

SYSAUX DATAFILE '/u01/app/oracle/oradata/XE/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TABLESPACE users

   DATAFILE '/u01/app/oracle/oradata/XE/users01.dbf'

   SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

   TEMPFILE '/u01/app/oracle/oradata/XE/temp01.dbf'

   SIZE 500M REUSE

UNDO TABLESPACE UNDOTBS1

   DATAFILE '/u01/app/oracle/oradata/XE/undotbs01.dbf'

   SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

• Step#5: Run scripts to build data dictionary views

SQL> CONN / AS SYSDBA

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> 
SQL> CONN SYSTEM/*******

SQL> 
SQL> @?/sqlplus/admin/pupbld.sql

• Step#6: Restart database to check everything

SQL> CONN / AS SYSDBA

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> spool off
#
# exit # exit from 'oracle' to 'root'
#

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

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

2.5.3. How to solve "ORA-39213: Metadata processing is not available" and "ORA-31609: error loading file "kualter.xsl" from file system directory ... SYS.DBMS_METADATA_UTIL"

• Step#1: Read technical post

https://community.oracle.com/message/9911695

• Step#2: Copy files from another Oracle Standard or Oracle Enterprise instalation in same SO version 

$ tar -cvf /tmp/xsl.tar  $ORACLE_HOME/rdbms/xml/xsl

• Step#3: Paste files from another Oracle Standard or Oracle Enterprise instalation in same SO version 

$ cd $ORACLE_HOME/rdbms/xml
$ mkdir xsl
$ tar -xvf  /tmp/xsl.tar ./xsl

• Step#4: Paste files from another Oracle Standard or Oracle Enterprise instalation in same SO version 

$ sqlplus "'/ as sysdba'"
SQL> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

SQL>

3. References

• http://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm#ADMIN11080
• http://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082
• http://docs.oracle.com/cd/E17781_01/appdev.112/e18147/tdddg_globalization.htm#TDDDG61200
• https://community.oracle.com/thread/351610
• https://community.oracle.com/thread/2356262?tstart=0
• http://www.dbforums.com/oracle/1605421-creating-new-db-express-edition.html
• http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm
• http://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm
• http://nedoboi.wordpress.com/2011/09/08/install-oracle-express-xe-11gr2-on-oracle-linux-6-1-x64-no-bitmap-index-conversion-on-the-fly/
• https://community.oracle.com/message/9911695