Install Oracle XE express edition

Oracle XE 11g (Express edition) installation steps in CentOS Linux

This article will show you how to install and configure Oracle XE 11g in CentOS Linux step by step.

Setup hosts file
echo "127.0.0.1   "`hostname` >> /etc/hosts

Install required packages
yum install libaio bc flex

Download Oracle XE 11g R2 installation package:
You can choose from enterprise, standard or express:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index-092322.html
I chose express for our project.

Download page:
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Direct link (accept licence):
http://download.oracle.com/otn/linux/oracle11g/xe/oracle-xe-11.2.0-1.0.x86_64.rpm.zip

Install Oracle XE
unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
cd Disk1
rpm -i oracle-xe-11.2.0-1.0.x86_64.rpm


Auto configuration (optional):
ORACLE_LISTENER_PORT=1521
ORACLE_HTTP_PORT=8080 # or 6060
ORACLE_PASSWORD=enter_admin_password_here
ORACLE_CONFIRM_PASSWORD=enter_admin_password_here
ORACLE_DBENABLE=y
export ORACLE_LISTENER_PORT ORACLE_HTTP_PORT ORACLE_PASSWORD \
ORACLE_CONFIRM_PASSWORD ORACLE_DBENABLE

Start configuration:
/etc/init.d/oracle-xe configure

Configuration options:
If you have not set up auto install, you can change following settings:
http port 8080 (changed to 6060)
database port 1521
credentials: SYS and SYSTEM
pass: enter_admin_password_here
start database when system starts: yes


Add oracle to path
Add following command to global /etc/profile for all users or to .bashrc or .bash_profile for current user:
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

Change password for unix user oracle:
passwd oracle
enter_admin_password_here

Login as oracle:
su oracle

Start sqlplus
su oracle
sqlplus / as sysdba

Connect using role sysdba (optional)

# you can start and connect in two following steps:

su oracle

sqlplus /nolog

connect / as sysdba

Enter SQL commands
-- just FYI:
SELECT * FROM V$LOGFILE;
show parameter DB_RECOVERY_FILE_DEST;
show parameters sessions;
show parameters processes;

Setup Oracle params:
-- Enable remote access:
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
-- max sessions:
alter system set sessions=250 scope=spfile;
shutdown immediate
startup
show parameters sessions;
-- max processes:
alter system set processes=200 scope=spfile;
shutdown immediate
startup
show parameters processes;
shutdown immediate
startup mount
-- archivelog:
alter database archivelog;
alter database open;
SELECT LOG_MODE FROM SYS.V$DATABASE;
-- Oracle replies
--  LOG_MODE
--  ------------
--  ARCHIVELOG
-- exit SQLplus:
EXIT

Setup password for APEX admin console (optional):
sqlplus / as sysdba
@/u01/app/oracle/product/11.2.0/xe/apex/apxchpwd.sql
-- enter password for admin user
-- exit SQLplus:
EXIT

Open APEX web admin:
http://localhost:8080/apex/f?p=4550:1
Note: I am currently using port 6060:
http://localhost:6060/apex/f?p=4550:1
Note2: Due to port forwarding, I am accessing:
http://localhost:7006/apex/f?p=4550:1
Workspace: Internal
User Name: admin
Password: your_password_from_previous_step
Note3: Admin interface link:
http://localhost:7006/apex/apex_admin

Environment variables check:
# check XE listener:
find /u01/app/oracle/product/11.2.0/xe/ | grep listener.ora
cat /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
# env check:
set | grep -i ^oracle

Create Oracle user (prerequisities)
You have to create user in Linux and in Oracle DB to enable successfull authentication.
Oracle user names contain by default specific prefix. You can change this setting or you need to add this prefix to each new user created in Oracle.

Check user prefix in Oracle (optional, recommended)

su oracle
sqlplus / as sysdba
SHOW PARAMETER os_authent_prefix
-- by default, user prefix is set to ops$

Remove username prefix
(optional, recommended)
You can remove prefix and work without "ops$":

su oracle
sqlplus / as sysdba
create pfile='/tmp/pfile.txt' from spfile;
shutdown immediate;
exit
echo "os_authent_prefix=''" >> /tmp/pfile.txt

sqlplus / as sysdba
create spfile from pfile='/tmp/pfile.txt';
startup
show parameter os_authent_prefix

Create user in Linux

sudo su
useradd sqluser1
passwd sqluser1
su oracle
sqlplus / as sysdba

Create user in Oracle
(Linux authentication)
-- User identified by Linux password
CREATE USER sqluser1 IDENTIFIED EXTERNALLY;

Create user in Oracle
(with prefix, not recommended)
-- User with name prefix - not recommended
CREATE USER ops$sqluser1 IDENTIFIED EXTERNALLY;

Create user in Oracle
(Oracle authentication)
-- User identified by Oracle password
CREATE USER sqluser1 IDENTIFIED BY sqluser1_password;
GRANT CREATE SESSION, CREATE TABLE TO sqluser1;

-- alter user sqluser1 quota 100m on system ;
GRANT UNLIMITED TABLESPACE TO sqluser1 ;

Create user in Oracle
(all in one example)
su oracle
sqlplus / as sysdba
-- User identified by Oracle password
CREATE USER sqluser1 IDENTIFIED BY sqluser1_password;
-- ALTER USER sqluser1 QUOTA 100m ON system ;
GRANT UNLIMITED TABLESPACE TO sqluser1 ;

GRANT CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  TO sqluser1 ;

Grant rights to user
GRANT CONNECT TO username;
GRANT resource TO username;
GRANT EXECUTE on schema.procedure TO username;
GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;

grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to sqluser1 ;

Grant role sysdba to user
GRANT SYSDBA to sqluser1;

Error
ORA-01950: no privileges on tablespace 'SYSTEM'
Solution
alter user sqluser1 quota 100m on system ;
alter user sqluser1 quota unlimited on system ;
or
GRANT UNLIMITED TABLESPACE TO sqluser1 ;

Resources

Installation steps inspired by:
http://www.davidghedini.com/pg/entry/install_oracle_11g_xe_on

User name prefix settings:
src: http://www.oracle-base.com/articles/misc/os-authentication.php

Useful commands

-- Show users

SELECT username, account_status FROM dba_users ;

-- Show current user name
show user

-- Change default schema for user
ALTER USER sqluser1 WITH DEFAULT_SCHEMA = sqluser1_schema1 ;

-- Drop all user data in schema
http://stackoverflow.com/questions/1690404/how-to-drop-all-user-tables

-- Show tables for users
SELECT owner, table_name FROM all_tables ;

-- Describe table
DESC table_name ;

-- Drop all tables

select 'drop table '||table_name||' cascade constraints;' from user_tables;

-- Drop sequences

set head off feed off verify off

define owner=sql_user_name

SELECT 'DROP TABLE &owner..' || TABLE_NAME || ' CASCADE CONSTRAINTS;' FROM DBA_TABLES WHERE OWNER=UPPER('&owner');

Useful DB tools

Oracle SQL Developer Download
SQLplus
Squirrel SQL Download