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 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');
